#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0

    Question Multiple Sql commands within a Trasaction.


    Hi guys,

    I'm new and still learning in Delphi. Can anybody help me regarding the correct syntax on executing multiple Sql commands(e.g. Insert) using Transaction? And if any statement fails, it will automatically rollback the transaction.


    Any insights will greatly appreciated.

    Thanks a lot for your time.

    Sincerely,
    Josinvill
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    I usually use the following pattern:

    Code:
      DbConnection.StartTransaction;
      try
        Sql1.Execute;
        Sql2.Execute;
        ...
        ...
        DbConnection.CommitTransaction;
      except
        DbConnection.RollbackTransaction;
        raise;  // re-raise the exception to make it available to interested company
      end;
    This way committing the transaction will only occur if all the sql commands have been executed properly and no exception raised within the block.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I would just add that, while Luthfi's technique is correct, the actual syntax will vary depending upon the database involved and the components used.
    Some do not require an explicit start statement.
    Some just require commit or rollback.
    Firebird also has a CommitRetaining option, so just check your particular syntax.

    Clive
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    Hi Luthfi and Clive,

    So glad to hear from you guys, and I think I'm almost there.

    I use MySql as my DB, and this are my components (TZMySqlDatabase, TZMySqlTransact, TZMySqlQuery).

    Here's my code when I click Save button.

    TZMySqlTransact.Connect;
    TZMySqlTransact.StartTransaction;
    try
    TZMySqlQuery1.Sql.Clear;
    TZMySqlQuery1.Sql.Add('insert into practiceDB.tbl_Header (field1,field2,field3) values (value1,value2,value3) ');
    TZMySqlQuery1.ExecSql;

    TZMySqlQuery2.Sql.Clear;
    TZMySqlQuery2.Sql.Add('insert into practiceDB.tbl_Details (field1,field2,field3) values (value1,value2,value3) ');
    TZMySqlQuery2.ExecSql;

    TZMySqlTransact.Commit;
    except
    TZMySqlTransact.Rollback;
    raise;
    end;

    What's wrong here guys is, when I intentionally put an error on second query, the first query will be successfully saved. What do you think did I miss on this code guys?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    Perhaps TZMySqlQuery1 and TZMySqlQuery2 did not use the same connection? Transaction only applies to a single session/connection.

IMN logo majestic logo threadwatch logo seochat tools logo