mysql.netguiduuiddotconnect

Convert a MySQLGuid (from Devart dotConnect for MySQL) to String in SQL

I'm using Devart's DotConnect product to connect an .net application to a MySQL Database. Everything is working pretty well, but I have an issue when working with SQL in the database.

The application uses Guids for row id's in the database (having come originally from a SQL Server application) which are stored as BINARY(16) in the database (and use DevArt's MySQLGuid class when being accessed through the .net application)

The problem is that when I want to query the database for a specific row I can't just paste in a string representation of a GUID in the database, so I'm trying to work out a function to convert the binary representation of the GUID to a string.

At first, I though it would be a fairly simple matter of calling HEX(id) in the query and then if I wanted a friendly readable output all I would need to do is add -'s in the appropriate place.

The select statement could look like this (in practice, I'd wrap this as a function):

 LOWER(CONCAT(LEFT(HEX(theme_id), 8), '-', MID(HEX(theme_id), 9,4), '-', MID(HEX(theme_id), 13,4), '-', MID(HEX(theme_id), 17,4), '-', RIGHT(HEX(theme_id), 12)))

does not return quite the right GUID. For example, if I store d1dfd973-fa3d-4b90-a1eb-47217162cd40 then the above select statement returns 73d9dfd1-3dfa-904b-a1eb-47217162cd40

It looks like the first 8 bytes have had their order reversed in the group (taking the first part of the string GUID, we have 73d9dfd1 which from a byte order perspective (i.e. treating groups of two characters as one byte) is d1dfd973, which is the right output.

My question is this - is there an operation that I can do within MySQL's dialect of SQL that will allow me to reverse the byte order of the relevant sections? I could make a more complex LEFT/MID/RIGHT statement but that doesn't feel like a good way of doing it.

An suggestions would be very much appreciated.

Solution

  • For example, if I store d1dfd973-fa3d-4b90-a1eb-47217162cd40 then the above select statement returns 73d9dfd1-3dfa-904b-a1eb-47217162cd40

    I think you are suffering from little endian/big endian issues with your GUIDs. See the section titled "Binary coding" on the wiki page for GUIDs : http://en.wikipedia.org/wiki/Globally_unique_identifier

    GUIDs and UUIDs are 16 byte values broken into 4 blocks:

    Data1 : 4 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data2 : 2 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data3 : 2 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data4 : 16 bytes   : stored in the same order for both UUIDs and GUIDs

    GUIDs and UUIDs are typically writen as hexadecimal strings using hyphens to separate the data components. Here is the kicker, both UUIDs and GUIDs strings are written with Data 1-3 in big endian order. The same strings could be represented with different byte patterns depending on whether they are stored in a UUID or a GUID on a little endian platform.

    Let's break down the four byte Data1 block of your sample GUID: d1dfd973. If this string represents a GUID on a microsoft/intel platform, then the bytes would appear in memory in this order:

    guid[0] = 0x73 // LSB first
guid[1] = 0xd9
guid[2] = 0xdf
guid[3] = 0xd1 // MSB last

    I think this is likely the byte order that Devart wrote the database. Your code then tries to extract this with 

    LEFT(HEX(theme_id), 8)

    which is going to produce the string 73d9dfd1 because it simply uses the data in the order it is stored.

    There is a clue that I am on the right track: your code reads Data4 in correct order (a1eb-47217162cd40 in your example GUID). Data4 is stored in the same byte order regardless of platform and whether we are talking about UUIDs or GUIDs.

    You have a couple of options