![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » Search Forums » Search Results » View Thread |
Messages 1 to 5 of 5 total |
![]() |
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 ![]() | 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 ![]() | 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. ![]() | << 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 Saturday, June 22, 2024 at 05:51 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |