Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
TEDBQuery and START TRANSACTION & COMMIT |
Mon, Dec 10 2012 6:01 PM | Permanent Link |
Linda_web | I have TEDBQuery and trying to use
START TRANSACTION; Do delete If error Rollback Also I do alter a table same way BTW: I don’t want to use SP Thank you |
Tue, Dec 11 2012 4:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Linda_web
Can you clarify what you're asking please I just can't understand. Roy Lambert |
Thu, Dec 13 2012 12:06 PM | Permanent Link |
Linda_web | I have TEDBQuery in my Delphi form, and connected to a database
I’m trying to execute delete statement on multi tables I like to have begin transaction , if any delete fails roll back, START TRANSACTION; Delete from table1 where id=1 Delete from table2 where id=2 So on Commit If error <>0 then Rollback TRANSACTION End; As I said, I like to do it from Delphi component side not EDB manager or any Stored procedure Thanks Roy Lambert wrote: Linda_web Can you clarify what you're asking please I just can't understand. Roy Lambert |
Thu, Dec 13 2012 1:40 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Linda_web
That's fairly easy. The first step is to decide which tables are involved. If its the whole database you don't need to bother with a table list if not you may want to use a list to reduce the tables that are locked. You'll also need to use a TEDBScript not a query (either drop one on the form or create dynamically in the procedure). procedure DeleteSelectedRecords; var TableList: TEDBStringsArray; DelScript:TEDBScript; begin SetLength(TableList, 2); TableList[0] := table1; TableList[1] := table2; DelScript := TEDBScript.Create(nil); try DelScript.SessionName := xx; DelScript.DatabaseName := yy; DelScript.SQL.Add('EXECUTE IMMEDIATE '+QuotedStr('DELETE FROM table1 WHERE ID = 1')); DelScript.SQL.Add('EXECUTE IMMEDIATE '+QuotedStr('DELETE FROM table2 WHERE ID = 2')); database.StartTransaction(TableList); try DelScript.ExecScript; database.Commit; except database.rollback; end; finally DelScript.Close; DelScript.Free; end; end; Totally untested. I hope it helps Roy Lambert [Team Elevate] |
Wed, Jan 2 2013 6:25 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << I have TEDBQuery and trying to use
START TRANSACTION; Do delete If error Rollback >> You can use a script to do so - you need a script because of the error trapping/handling: SCRIPT BEGIN START TRANSACTION; BEGIN EXECUTE IMMEDIATE 'DELETE FROM ....'; EXECUTE IMMEDIATE 'DELETE FROM ....'; COMMIT; EXCEPTION ROLLBACK; RAISE; END; END << Also I do alter a table same way >> Table alterations cannot be done inside of a transaction. In fact, you can't use any DDL statements inside of a transaction. If you have any other questions, please let me know. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |