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

Linda_web


Can you clarify what you're asking please I just can't understand.

Roy Lambert

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

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]

<< 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