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.
Obtaining triggered UUID() key value after insert
Re: Obtaining triggered UUID() key value after insert
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.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.
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;
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.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.
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.
Re: Obtaining triggered UUID() key value after insert
Excellent. Thank you for this thorough and clear answer.
Re: Obtaining triggered UUID() key value after insert
Thank you for the interest in our products.
Please contact us if you have any questions about our products.
Please contact us if you have any questions about our products.