Greetings. I have a MySQL database, some of whose tables have primary keys derived from GUIDs. The GUIDs are stored in BINARY(16) fields as I've seen recommended in various fora. These fields are populated using a BEFORE INSERT trigger that sets the value to UNHEX(REPLACE(UUID(), '-', '')). This works fine when I work with the tables using MySQL through Heidi. I can add, edit, and delete records, either directly in the Data view or using SQL.
My problem is that when I try to work with these tables using MyDAC MyTable or MyQuery components, in some cases updating or deleting a record leads to an "Update failed. Found 0 records." error. This doesn't happen with all the tables, but it happens consistently with some. It happens both when I run the app and when I work with the data using the Data Editor dialog that's available from the SQL dialog I get by double-clicking the MyDAC component.
Why do these MyDAC components fail to work successfully with these Binary key fields? Is there some setting somewhere that I can use to get around this issue? I'm not really looking forward to the idea of restructuring my database to use some other primary key type. Thanks for any suggestions.
Binary(16) GUID leads to "found 0 records" errors
Re: Binary(16) GUID leads to "found 0 records" errors
Update: I've found a workaround. All the tables with BINARY(16) primary keys also have a virtual column that represents the key value in standard CHAR(36) GUID format [using the expression insert(insert(insert(insert(hex(<FieldName>),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')]. If I set the MyDAC components KeyFields property to this virtual column (instead of leaving it blank or setting it to the BINARY(16) column), updates work.
I'd still be interested to know why the MyDAC components are apparently not able to work directly with BINARY(16) primary keys.
I'd still be interested to know why the MyDAC components are apparently not able to work directly with BINARY(16) primary keys.
Re: Binary(16) GUID leads to "found 0 records" errors
MyDAC supports mapping from binary to guid. For example:
If this does not help in solving the problem, please compose and send us using the contact form https://devart.com/company/contactform.html a small sample demonstrating the behavior, including scripts for creating and populating database objects.
Code: Select all
MyQuery.DataTypeMap.AddDBTypeRule(myBinary, ftGuid);
MyQuery.Options.BinaryAsString := False;
MyQuery.Open;