Obtaining triggered UUID() key value after insert

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
sdaberle
Posts: 8
Joined: Tue 12 Sep 2017 16:34

Obtaining triggered UUID() key value after insert

Post by sdaberle » Tue 12 Sep 2017 17:20

I've recently started using MyDac components and I'm experiencing some confusion. Apologies for my inexperience.

I have a database whose primary key is a GUID. They key value is set by the database in a BEFORE INSERT trigger: SET new.GUIDField = UUID(). I need to obtain the key field value after inserting and posting a record to the database. In the past (using Advantage Database TDataSet descendant components) I would have used code like this:

TheTable.Insert;
TheTable.FieldByName('SomeField').AsString := 'Some value';
TheTable.Post;
sGUIDValue := TheTable.FieldByName('GUIDField').AsString;

When I try to do this with MyDac using MyQuery or MyTable components, however, the string value returned for the key field is empty, because the record values have not been updated after posting. When I try to call the RefreshRecord method, I get an error message that 0 records were returned.

I've read several forum postings about complications around obtaining the last inserted AutoInc ID value. Responses have involved setting Options.ReturnParams to True and using a ptInputOutput parameter. This is not exactly my problem, however, since I'm using a trigger-set GUID key, not an AutoInc ID. I also don't understand how to add and set the value of a ptInputOutput parameter in this situation. Where would I create the parameter? I see no way to add a new parameter (that is not specified in the SQL code) in the Parameters dialogue for my query.

This brings me to a deeper source of confusion: I don't understand the function and use of the SQLInsert, SQLDelete, SQLUpdate, SQLRefresh etc. properties. I get that they can be populated by using the SQL Generator, and presumably that they can be edited to change default behaviours, but I don't understand exactly how (and why) to go about doing this. Any suggestions regarding tutorials, code examples etc. would be most welcome. (RTFM will not help me in this situation. I have read the documentation and don't find it informative without being able to see an example.)

I also don't understand the use of the UpdateObject property, although I have looked at the MyDacDemo UpdateSQL form. I have found no documentation in the help file about UpdateObject and am curious as to its use.

My workaround for my particular situation has been to remove the BEFORE INSERT trigger and set the GUID value using code, but I would prefer to have this taken care of at the database engine level. Any suggestions? Many thanks in advance.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Obtaining triggered UUID() key value after insert

Post by ViktorV » Wed 13 Sep 2017 10:18

sdaberle wrote:I've recently started using MyDac components and I'm experiencing some confusion. Apologies for my inexperience.

I have a database whose primary key is a GUID. They key value is set by the database in a BEFORE INSERT trigger: SET new.GUIDField = UUID(). I need to obtain the key field value after inserting and posting a record to the database. In the past (using Advantage Database TDataSet descendant components) I would have used code like this:

TheTable.Insert;
TheTable.FieldByName('SomeField').AsString := 'Some value';
TheTable.Post;
sGUIDValue := TheTable.FieldByName('GUIDField').AsString;

When I try to do this with MyDac using MyQuery or MyTable components, however, the string value returned for the key field is empty, because the record values have not been updated after posting. When I try to call the RefreshRecord method, I get an error message that 0 records were returned.

I've read several forum postings about complications around obtaining the last inserted AutoInc ID value. Responses have involved setting Options.ReturnParams to True and using a ptInputOutput parameter. This is not exactly my problem, however, since I'm using a trigger-set GUID key, not an AutoInc ID. I also don't understand how to add and set the value of a ptInputOutput parameter in this situation. Where would I create the parameter? I see no way to add a new parameter (that is not specified in the SQL code) in the Parameters dialogue for my query.

This brings me to a deeper source of confusion: I don't understand the function and use of the SQLInsert, SQLDelete, SQLUpdate, SQLRefresh etc. properties. I get that they can be populated by using the SQL Generator, and presumably that they can be edited to change default behaviours, but I don't understand exactly how (and why) to go about doing this. Any suggestions regarding tutorials, code examples etc. would be most welcome. (RTFM will not help me in this situation. I have read the documentation and don't find it informative without being able to see an example.)

I also don't understand the use of the UpdateObject property, although I have looked at the MyDacDemo UpdateSQL form. I have found no documentation in the help file about UpdateObject and am curious as to its use.

My workaround for my particular situation has been to remove the BEFORE INSERT trigger and set the GUID value using code, but I would prefer to have this taken care of at the database engine level. Any suggestions? Many thanks in advance.
MySQL server does not return the information about fields filled by the trigger during adding (modifying) data, therefore MyDAC does not get this information either.
To solve this issue, you can add a key field to the SELECT query. You can use autoincrement field or unique key with the value set on the client.
For example:
1. Add an increment field to the table:

Code: Select all

ALTER TABLE tablename ADD id_fieldname INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
2. Set the TMyQuery.RefreshOptions property to roAfterInsert.
This brings me to a deeper source of confusion: I don't understand the function and use of the SQLInsert, SQLDelete, SQLUpdate, SQLRefresh etc. properties. I get that they can be populated by using the SQL Generator, and presumably that they can be edited to change default behaviours, but I don't understand exactly how (and why) to go about doing this. Any suggestions regarding tutorials, code examples etc. would be most welcome. (RTFM will not help me in this situation. I have read the documentation and don't find it informative without being able to see an example.)

I also don't understand the use of the UpdateObject property, although I have looked at the MyDacDemo UpdateSQL form. I have found no documentation in the help file about UpdateObject and am curious as to its use.

My workaround for my particular situation has been to remove the BEFORE INSERT trigger and set the GUID value using code, but I would prefer to have this taken care of at the database engine level. Any suggestions? Many thanks in advance.
If the SQLInsert, SQLUpdate, SQLDelete, SQLRefresh properties are not empty, for example generated in design-time or set manually, MyDAC will use SQL query specified in this property. If the property is set to an empty string, MyDAC will automatically generate necessary SQL queries when executing the corresponding operations.
In most cases you do not have to specify SQLInsert, SQLUpdate, SQLDelete, SQLRefresh properties.
You should set these properties manually only in specific situations, when an automatically generated SQL query returns an incorrect result (for example, using complex queries)
The TMyUpdateSQL component, which contains SQL queries for updating data or objects for executing data updates, is specified in the UpdateObject property.

sdaberle
Posts: 8
Joined: Tue 12 Sep 2017 16:34

Re: Obtaining triggered UUID() key value after insert

Post by sdaberle » Wed 11 Oct 2017 23:21

Excellent. Thank you for this thorough and clear answer.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Obtaining triggered UUID() key value after insert

Post by ViktorV » Fri 13 Oct 2017 10:14

Thank you for the interest in our products.
Please contact us if you have any questions about our products.

Post Reply