4

I am using the following: 

begin
dbmodule.comenziQuery.SQL.Clear;
dbmodule.comenziQuery.SQL.Add('INSERT INTO `r33758pi_tipotask`.`arhiva` SELECT id, data, stare, client, telefon, email, detalii, observatii, pret, livrare, user, status FROM comenzi WHERE `id`='''+inttostr(dbmodule.comenziDataSetid.Value)+''';');
dbmodule.comenziQuery.ExecSQL(true);

dbmodule.comenziQuery.SQL.Clear;
dbmodule.comenziQuery.SQL.Add('DELETE FROM `r33758pi_tipotask`.`comenzi` WHERE `id`='''+inttostr(dbmodule.comenziDataSetid.Value)+''';');
dbmodule.comenziQuery.ExecSQL(true);
end;

Can anyone guide me on how to wrap this in a transaction? I've never used one before and I'm having difficulty understanding how to modify the code to add it.

I'm trying to add it in a transaction as I've read it is good practice, since if 1 fails it won't execute at all.

I've tried following this guide Some Guide and subsequently using the begin transaction and begin try that are mentioned there but I'm guessing that's not intented for delphi code, or something..so no luck.

Hope the code is ok, please feel free to point out anything wrong I might have done so far.

Delphi, using Rad Studio 10 Seattle, TSQLQuery - Server is mysql - let me know in a comment if I left anything out and I will edit.

Ken White
  • 123,280
  • 14
  • 225
  • 444
t1f
  • 3,021
  • 3
  • 31
  • 61
  • so the dblayer is what? The dblibrary? – Drew Nov 26 '16 at 02:22
  • @Drew - Database client version: libmysql - 5.1.73 - is this what you mean? Sorry, fairly new to this so I'm having trouble understanding your question :) – t1f Nov 26 '16 at 02:26
  • http://docwiki.embarcadero.com/Libraries/Seattle/en/Data.SqlExpr.TSQLConnection.StartTransaction – Drew Nov 26 '16 at 02:29
  • Oh, thanks for the link. I am using TSQLConnection actually, so what, I just add comenziConnection.BeginTransaction; before the 1st sql statement and go from there? – t1f Nov 26 '16 at 02:34
  • That's my guess. Just keep all superfluous cmds away from the Start / Commit / Rollback. Keep it lean and mean. Also note that in different environments such commands can barf out the Trans – Drew Nov 26 '16 at 02:35
  • @Drew Thanks a lot! :D – t1f Nov 26 '16 at 02:38
  • @Sami - Mentioned in the initial post. Rad Studio 10 Seattle, Embarcadero® RAD Studio 10 Seattle Version 23.0.21418.4207 - so that makes it Delphi 10 Seattle – t1f Nov 26 '16 at 06:17
  • You should use `If SQLConnection1.TransactionsSupported Then` – Ilyes Nov 26 '16 at 06:26
  • @Sami care to make it an answer which I can accept, based on my code above? I'd hate to mess something up since I haven't use it before and can't find a full code example so far which I can modify. Either way, thanks! – t1f Nov 26 '16 at 06:28
  • I am not sure if mysql can do this, but in sql server you can put both statements in the same call in stead of 2 seperate calls – GuidoG Nov 28 '16 at 08:21
  • @GuidoG Thanks! I'll keep looking – t1f Nov 29 '16 at 01:05

1 Answers1

1

Transactions are usually used within SQL Server stored procedures where you have a block of sql statements between the BEGIN TRANSACTION and COMMIT TRANSACTION or ROLLBACK TRANSACTION statements.

Not sure if it works the same for MySQL.

Transactions are usually handled on the sql server, as opposed to doing it from code.

OnoMrBill
  • 56
  • 4