try

             //////////////////////////////////////////////////////////////////
             if MyQuery1.Active then MyQuery1.Close;
             MyQuery1.SQL.Clear;
             MyQuery1.Params.Clear;
             MyQuery1.SQL.Add('BEGIN START TRANSACTION ON TABLES "PurchaseItems", "Purchases" ;');
             MyQuery1.ExecSQL;

             //////////////////////////////////////////////////////////////////
             if MyQuery1.Active then MyQuery1.Close;
             MyQuery1.SQL.Clear;
             MyQuery1.Params.Clear;
             MyQuery1.SQL.Add('DELETE FROM PurchaseItems WHERE InvoiceID = :NUM ;');
             MyQuery1.ParamByName('NUM').AsInteger:= EditInvoiceID;
             MyQuery1.ExecSQL;

...

...

...

             //////////////////////////////////////////////////////////////////
             if MyQuery1.Active then MyQuery1.Close;
             MyQuery1.SQL.Clear;
             MyQuery1.Params.Clear;
             MyQuery1.SQL.Add('COMMIT ;');
             MyQuery1.ExecSQL;

           except

             //////////////////////////////////////////////////////////////////
             if MyQuery1.Active then MyQuery1.Close;
             MyQuery1.SQL.Clear;
             MyQuery1.Params.Clear;
             MyQuery1.SQL.Add('ROLLBACK ;');
             MyQuery1.ExecSQL;

           end;

Please advise - Thanks In Advance

Andrew


I think your basic problem is you're trying to use several queries and you need to be using a script. Build the script to do the full job then execute the script as a single thing. This is what the script should look like


SCRIPT (IN NUM INTEGER)
BEGIN

START TRANSACTION ON TABLES "PurchaseItems", "Purchases"
BEGIN
DELETE FROM PurchaseItems WHERE InvoiceID = ? USING NUM;
COMMIT;
EXCEPTION
ROLLBACK;
END;

END

Personally since you're building it in code I'd forget the parameter and just code the appropriate InvoiceID when you build the sql.





Roy Lambert

Roy, I appreciate your comment but

I am DELETING TableX WHERE InvoiceID=n

WALKING TableY WHERE InvoiceID=n

COPYING MATCHING TableY.Row INTO TableX

Basically I am deleting old data and replacing it with new data using queries.

Andrew

>Basically I am deleting old data and replacing it with new data using queries.

OK, that wasn't clear from the code you supplied, and I'm still not clear exactly what you want (especially WALKING). The basic principle remains the same though. You either have to use a script or a mixture of Delphi and SQL.

For the script version you can (I think) use EXECUTE IMMEDIATE within a transaction so you'd have something like

SCRIPT (IN NUM INTEGER)
BEGIN

START TRANSACTION ON TABLES "PurchaseItems", "Purchases"
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM PurchaseItems WHERE InvoiceID = ?' USING NUM;
EXECUTE IMMEDIATE 'INSERT (fld names) INTO PurchaseItems (SELECT fld names FROM Purchases WHERE IncoiceID = ?' USING NUM;
COMMIT;
EXCEPTION
ROLLBACK;
END;



END

Roy I tried

Andrew


Try this

CREATE FUNCTION "UpdatePurchaseItems" (IN Num INTEGER)
RETURNS INTEGER
BEGIN
 DECLARE Processed INTEGER;
/* NOTE: all declarations MUST be at the top of the script - they can't be in the body */
 START TRANSACTION ON TABLES 'PurchaseItems', 'Purchases';
/* NOTE: you need single quotes round the table names not double quotes */
 BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO ? FROM WorkSheet WHERE IncoiceID = ?' USING Processed, NUM;
/* NOTE: unless you set up a cursor you can't directly execute SQL - always use EXEXUTE IMMEDIATE */
   EXECUTE IMMEDIATE 'DELETE FROM PurchaseItems WHERE InvoiceID = ?' USING Num;
   EXECUTE IMMEDIATE 'INSERT (fld names) INTO PurchaseItems (SELECT fld names FROM WorkSheet WHERE IncoiceID = ?' USING Num;
   COMMIT;
 EXCEPTION
   SET Processed = 0;
   ROLLBACK;
 END;
 RETURN Processed;
END


A suggestion - when you're stuck like this move into EDBManager and try the code in there. You don't need to run it just keep trying to Prepare it, when it will prepare then's the time to try and run it.

The code above does prepare but I have no idea if it will do what you want since I don't have your database

If this is in a form why not drop a TEDBScript onto the form and plonk the code in there - unless you are building code programmatically its a lot easier.


Roy Lambert

Thanks Roy, I changed the INSERT Line - I trust this is OK - Andy

CREATE FUNCTION "UpdatePurchaseItems" (IN "Num" INTEGER)
RETURNS INTEGER
BEGIN
 DECLARE Processed INTEGER;
 START TRANSACTION ON TABLES 'PurchaseItems', 'Purchases';
 BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO ? FROM WorkSheet WHERE InvoiceID = ?' USING Processed, Num;
   EXECUTE IMMEDIATE 'DELETE FROM PurchaseItems WHERE InvoiceID = ?' USING Num;
   EXECUTE IMMEDIATE 'INSERT INTO PurchaseItems(InvoiceID, Quantity, Description, Amount, BasType, LedgerType) (SELECT InvoiceID, Quantity, Description, Amount, BasType, LedgerType FROM WorkSheet WHERE InvoiceID = ?)' USING Num;
   COMMIT;
 EXCEPTION
   SET Processed = 0;
   ROLLBACK;
 END;
 RETURN Processed; END
VERSION 1.00

Andrew


Its your INSERT line, you have it how you like

Also it wouldn't have worked with my line, but not knowing your tables made it a bit difficult to put the right stuff in

Hope it works now

Roy Lambert