A (more) correct way of using transactions ?

[Pete B]

Hi
An apology if my question seems elementary but I'm relatively new to both SQL and MyDAC (currently using 5.9.00.56).
I have been employing START TRANSACTION, COMMIT and ROLLBACK by wrapping them around queries directly within a TMyQuery.SQL.Text string which I presume is OK ... it appears to work just fine ... unless you know better

Is it more correct to perhaps use the StartTransaction / Commit / Rollback methods of TMyConnection instead ? ... better peformance maybe ? and if I use these, where is the best place to call from ... for StartTransaction maybe TMyQuery.BeforePost ?. Then where is best for calling the Commit(), Rollback methods ? ... the MyDAC Transaction demo does this from buttons which is not really an option in this case.

thanks and best regards

Pete B

[Dimon]

The TMyConnection.StartTransaction method executes the START TRANSACTION SQL statement, therefore the StartTransaction/Commit/Rollback methods are equal to appropriate SQL statements.

Note:
If you don't use transactions obviously by calling the StartTransaction method, then MyDAC doesn't start a transaction obviously either. In this case MySQL server uses an implicit transaction and data are commited automatically. If you change data (e.g., insert, update, delete), it is saved in a database automaticly on calling the Post method, unless you use transactions.

[Pete B]

Thank you for your help.
I suspect that I will use the TMyConnection StartTransaction / Commit and Rollback methods in preference to simply using their equivalent in MySQL DML as I have been.
The best way to do this appears to be in a try / except block with the rollback on exception, simplistically along the lines of ...

try
MyConnection.StartTransaction;

MyDataset.Post;

MyConnection.Commit;
except

MyConnection.Rollback;

end;

... I guess that's probably 'of course' to the initiated ? ... but I'm a relative novice here, so advice is always appreciated.

thanks Pete B.

[Dimon]

You have selected the correct way to use transactions.

[Pete B]

Been away for a while working on a PHP project ... now I can return to MyDAC at last ... but I have encountered the need to extend this thread.
There are several posts in this forum that refer to using transactions and others that refer to updating multiple tables, it is employing the TMyQuery SQLUpdate property, using transactions AND updating multiple tables that is currently causing a problem. If, for example, I set the SQLUpdate property to (at runtime) ...

'SET @update_id = ''' + lbdbID.Text +''';' +
'UPDATE sfs1.customers AS c SET c.email = ''' + eddbEMAIL.Text +''', c.tel_mobile = ''' + eddbTELMOBILE.Text +''' WHERE c.client_id = @update_id LIMIT 1;' +
'UPDATE sfs1.farm_usage AS fu SET fu.notes = ''' + medbNOTES.Text + ''' WHERE fu.client_id = @update_id LIMIT 1;'

... then I find my second update statement does not appear to be executed. Looking harder, it seems that in fact ANY edit for ANY field in the customers table is updated which to me implies that in actual fact none of the above statements are actually being executed and all that is happening is as if TMyQuery is behaving in its 'default' mode and automatically generating the SQL update ... of course that is for one table only.

How do I correctly resolve this please

Using Delphi 2009 / MyDAC 5.90.0.56

many thanks

Pete B

[Dimon]

It seems that you should use the SQLUpdate query with parameters, like this:

Code: Select all

'SET @update_id = :id;' + 
'UPDATE sfs1.customers AS c SET c.email = :email, c.tel_mobile = tel_mobile WHERE c.client_id = @update_id LIMIT 1;' + 
'UPDATE sfs1.farm_usage AS fu SET fu.notes = ''' + medbNOTES.Text + ''' WHERE fu.client_id = @update_id LIMIT 1;' 

[Dimon]

You can set the TMyQuery.Debug property to True to display executing statement and all its parameters' values.
Also you can use the TMySQLMonitor component to monitor dynamic SQL execution in MyDAC based applications.
You can find more detailed information about these components in the MyDAC help.

[Pete B]

You can set the TMyQuery.Debug property to True to display executing statement and all its parameters' values.
Also you can use the TMySQLMonitor component to monitor dynamic SQL execution in MyDAC based applications.

... yes, I do this and it's a fantastic facility of MyDAC.

With regard to multiple table update / insert. Thanks for the advice on using parameters. I actually discovered that I can use either parameters or ... as in the case above, by way of example ... the control text property, both work. However I think parameters are better , more elegant, make use of MyDAC and are shorter !
The reason it was not really working seems to be my choice of method, I was setting my SQLUpdate string in various places eg. TMyQuery.BeforeExecute. It was only when I tried BeforePost that it all worked ... I suppose that should have been obvious but hey ho ...

cheers

Pete B.