Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Been a while since I did this - forget - I want to DELETE/UPDATE/INSERT on several TABLES |
Sun, Jan 7 2024 11:32 PM | Permanent Link |
Andrew Hill | 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 |
Mon, Jan 8 2024 3:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Jan 9 2024 12:29 AM | Permanent Link |
Andrew Hill | 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. |
Tue, Jan 9 2024 3:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Jan 9 2024 3:29 PM | Permanent Link |
Andrew Hill | Roy I tried
Attachments: Function-Error.zip |
Wed, Jan 10 2024 3:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jan 10 2024 5:26 AM | Permanent Link |
Andrew Hill | 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 |
Wed, Jan 10 2024 7:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |