I've written what for me is a fairly adventurous script. Its called in a loop with a transaction. Sometimes it runs along happily for several hundred iterations then jams, other times it stops after 10 - 20 iterations. I'm hoping someone can point out what I'm doing wrong.

procedure TArchiveCompaniesForm.ProcessSelected;
var
CpyID: integer;
Cntr: integer;
TableList: TEDBStringsArray;
begin
SetLength(TableList, 10);
TableList[0] := 'Companies';
TableList[1] := 'Sites';
TableList[2] := 'Contacts';
TableList[3] := 'Career';
TableList[4] := 'ELN';
TableList[5] := 'arcCompanies';
TableList[6] := 'arcSites';
TableList[7] := 'arcContacts';
TableList[8] := 'arcCareer';
TableList[9] := 'arcELN';
DoItAll.Prepare;
Cntr := 0;
arcList.First;
while KeepOn and (not arcList.Eof) do begin
 Application.ProcessMessages;
 inc(Cntr);
 if arcGrid.SelectedRows.CurrentRowSelected then begin
  CpyID := arcList.FieldByName('ID').AsInteger;
  dm.DB.StartTransaction(TableList);
  try
   DoItAll.Close;
   DoItAll.ParamByName('CpyID').AsInteger := CPyID;
   DoItAll.ExecScript;
   dm.DB.Commit;
   arcGrid.SelectedRows.CurrentRowSelected := False; // only remove selection when everything worked
  except
   dm.DB.Rollback;
  end;
 end;
 arcList.Next;
end;
Close;
end;


SCRIPT (IN CpyID INTEGER)
BEGIN

DECLARE OK1 CURSOR FOR OKCheck1;
DECLARE OK2 CURSOR FOR OKCheck2;

DECLARE ContactID INTEGER DEFAULT -1;
DECLARE ContactStatus VARCHAR;
DECLARE Peoples CURSOR FOR PeopleList;
DECLARE Contacts CURSOR FOR ContactDelete;

DECLARE Insert1 CURSOR FOR CompanyInsert;
DECLARE Insert2 CURSOR FOR SiteInsert;
DECLARE Insert3 CURSOR FOR ContactInsert;
DECLARE Insert4 CURSOR FOR CareerInsert;
DECLARE Insert5 CURSOR FOR ELNInsert;

DECLARE Delete1 CURSOR FOR CompanyDelete;
DECLARE Delete2 CURSOR FOR SiteDelete;
DECLARE Delete3 CURSOR FOR CareerDelete;
DECLARE Delete4 CURSOR FOR ELNDelete;
DECLARE Delete5 CURSOR FOR CallsDelete;
DECLARE Delete6 CURSOR FOR CallStatsDelete;
DECLARE Delete7 CURSOR FOR LetterQueueDelete;
DECLARE Delete8 CURSOR FOR DialControlDelete;
DECLARE Delete9 CURSOR FOR DiaryLinksDelete;

DECLARE Alter1 CURSOR FOR EMailsAlter;
DECLARE Alter2 CURSOR FOR InvoicesAlter;
DECLARE Alter3 CURSOR FOR IntroductionsAlterCompany;
DECLARE Alter4 CURSOR FOR IntroductionsAlterContact;
DECLARE Alter5 CURSOR FOR OrderBookAlterCompany;
DECLARE Alter6 CURSOR FOR OrderBookAlterContact;

PREPARE OKCheck1 FROM 'SELECT _ID FROM Companies WHERE _ID = ?';
PREPARE OKCheck2 FROM 'SELECT _ID FROM arcCompanies WHERE _ID = ?';

PREPARE PeopleList FROM 'SELECT _fkContacts FROM Career WHERE _fkCompanies = ?';

PREPARE CompanyInsert FROM 'INSERT INTO arcCompanies SELECT * FROM Companies WHERE Companies._ID = ?';
PREPARE SiteInsert FROM 'INSERT INTO arcSites SELECT * FROM Sites WHERE Sites._fkCompanies = ?';
PREPARE ContactInsert FROM 'INSERT INTO arcContacts SELECT Contacts.* FROM Contacts JOIN Career ON Career._fkContacts = Contacts._ID WHERE Career._Left IS NULL AND Career._fkCompanies = ?';
PREPARE CareerInsert FROM 'INSERT INTO arcCareer SELECT * FROM Career WHERE Career._fkCompanies = ?';
PREPARE ELNInsert FROM 'INSERT INTO arcELN SELECT * FROM ELN WHERE ELN._fkCompanies = ?';

PREPARE CompanyDelete FROM 'DELETE FROM Companies WHERE Companies._ID = ?';
PREPARE SiteDelete FROM 'DELETE FROM Sites WHERE Sites._fkCompanies = ?';
PREPARE ContactDelete FROM 'SELECT * FROM Contacts WHERE _ID = ?';
PREPARE CareerDelete FROM 'DELETE FROM Career WHERE Career._fkCompanies = ?';
PREPARE ELNDelete FROM 'DELETE FROM ELN WHERE ELN._fkCompanies = ?';
PREPARE CallsDelete FROM 'DELETE FROM Calls WHERE Calls._fkCompanies = ?';
PREPARE CallStatsDelete FROM 'DELETE FROM CallStats WHERE CallStats._fkCompanies = ?';
PREPARE LetterQueueDelete FROM 'DELETE FROM LetterQueue WHERE LetterQueue._fkCompanies = ?';
PREPARE DialControlDelete FROM 'DELETE FROM DialControl WHERE DialControl._fkCompanies = ?';
PREPARE DiaryLinksDelete FROM 'DELETE FROM DiaryLinks WHERE DiaryLinks._fkCompanies = ?';

PREPARE EMailsAlter FROM 'UPDATE EMails SET _fkContacts = NULL WHERE _fkContacts = ?';
PREPARE InvoicesAlter FROM 'UPDATE Invoices SET _fkCompanies = NULL WHERE _fkCompanies = ?';
PREPARE IntroductionsAlterCompany FROM 'UPDATE Introductions SET _fkCompanies = NULL WHERE _fkCompanies = ?';
PREPARE IntroductionsAlterContact FROM 'UPDATE Introductions SET _fkContacts = NULL WHERE _fkContacts = ?';
PREPARE OrderBookAlterCompany FROM 'UPDATE OrderBook SET _fkCompanies = NULL WHERE _fkCompanies = ?';
PREPARE OrderBookAlterContact FROM 'UPDATE OrderBook SET _fkContacts = NULL WHERE _fkContacts = ?';

OPEN OK1 USING CpyID;

IF ROWCOUNT(OK1) = 1 THEN
 OPEN OK2 USING CpyID;
 IF ROWCOUNT(OK2) = 0 THEN
  OPEN Insert1 USING CpyID;
  OPEN Insert2 USING CpyID;
  OPEN Insert3 USING CpyID;
  OPEN Insert4 USING CpyID;
  OPEN Insert5 USING CpyID;

  OPEN Delete1 USING CpyID;
  OPEN Delete2 USING CpyID;
  OPEN Delete4 USING CpyID;
  OPEN Delete5 USING CpyID;
  OPEN Delete6 USING CpyID;
  OPEN Delete7 USING CpyID;
  OPEN Delete8 USING CpyID;
  OPEN Delete9 USING CpyID;

  OPEN Alter2 USING CpyID;
  OPEN Alter3 USING CpyID;
  OPEN Alter5 USING CpyID;

   OPEN Peoples USING CpyID;
   FETCH FIRST FROM Peoples('_fkContacts') INTO ContactID;
   WHILE NOT EOF(Peoples) DO
    OPEN Contacts USING ContactID;
    IF NOT EOF(Contacts) THEN
     FETCH FIRST FROM Contacts('_Status') INTO ContactStatus;
     IF (ContactStatus <> 'CV on file') AND (ContactStatus <> 'Hold') AND (ContactStatus <> 'Placed') THEN
      DELETE FROM Contacts;
      OPEN Alter1 USING ContactID;
      OPEN Alter4 USING ContactID;
      OPEN Alter6 USING ContactID;
     END IF;
     FETCH NEXT FROM Peoples('_fkContacts') INTO ContactID;
    END IF;
   END WHILE;

  OPEN Delete3 USING CpyID;
 END IF;
END IF;

END



Roy Lambert

Roy,

while I am not able to study your script in detail, I'd suggest just to
debug it.

In similar cases I found out the culprit quite quickly by inserting "SET
LOG MESSAGE To xy" in important (or less important) lines of the script.

You can then use the OnLogMessage event of the script component in your
Delphi applciation to see where the script stops.

Uli

Uli


I think I've sorted it - a distinct case of mind not tuned to SQL. Wrote what I thought the loop should do in Delphi and the problem was glaringly obvious

Roy Lambert