Convert MySQLGuid (from Devart dotConnect for MySQL) to String in SQL
I am using Devart DotConnect product to connect .net application to MySQL database. Everything works very well, but I have a problem when working with SQL on a database.
The app uses Guides for the row id in the database (originally from a SQL Server application), which are stored in BINARY (16) in the database (and use the DevArt MySQLGuid class when accessed through a .net app)
The problem is that when I want to query the database for a specific string, I can't just insert the string representation of the GUID into the database, so I'm trying to work out a function to convert the binary GUID representation to a string.
In the beginning, although it would be fairly straightforward to call HEX (id) in the request, then if I wanted friendly readable output, all I would need to do is add the appropriate place.
A select statement might look like this (in practice I would wrap it like 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 the correct GUID. For example, if I store d1dfd973-fa3d-4b90-a1eb-47217162cd40
, then the above select statement returns73d9dfd1-3dfa-904b-a1eb-47217162cd40
It looks like the first 8 bytes have changed the order in the group (taking the first part of the string ID, we have 73d9dfd1
, which in terms of byte ordering (i.e. handles groups of two characters as one byte) d1dfd973
, which is the correct output.
My question is, is there an operation I can do in the SQL SQL Dialogs that will allow me to change the byte order of the respective sections? I could have done a more complex LEFT / MID / RIGHT statement, but that's not very good.
Suggestions would be much appreciated.
source to share
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 Binary Encoding section on the wiki page for GUIDs: http://en.wikipedia.org/wiki/Globally_unique_identifier
GUID and UUID are 16 byte values, split 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
GUID and UUID are usually written as hexadecimal strings, using hyphens to separate data components. Here is the kicker, UUID and GUID strings are written with data 1-3 in large order . The same strings can be represented by different byte patterns depending on whether they are stored in a UUID or a GUID on a platform with few sides.
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 appear in memory in the following order:
guid[0] = 0x73 // LSB first
guid[1] = 0xd9
guid[2] = 0xdf
guid[3] = 0xd1 // MSB last
I think this is most likely the byte order that Devart wrote the database. Then your code tries to extract this with
LEFT(HEX(theme_id), 8)
which is going to create string 73d9dfd1 because it just uses the data in the order it is stored.
There is a hint that I am on the right track: your code reads Data4 in the correct order (a1eb-47217162cd40 in your example GUID). Data4 is stored in the same byte order regardless of platform and we are talking about UUID or GUID.
You have a couple of options
-
Let's assume everything is stored in the database as small GUIDs. Use a conversion routine such as Andrzej to convert back to string representation.
-
The mandate that everything is stored is big UUIDs. Run a one-time conversion to reverse the byte order of existing strings. You can use Andrzej routines to convert, but after that you can convert from binary to lowercase directly.
source to share
step 1)
I am creating a table in Mysql
CREATE TABLE `table_code` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(45) DEFAULT NULL,
`guid` binary(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
step 2)
I insert one line:
INSERT INTO table_code (code,guid)
VALUES ('d1dfd973fa3d4b90a1eb47217162cd40',UNHEX('d1dfd973fa3d4b90a1eb47217162cd40'));
step 3)
I launched
SELECT
id,
code,
LOWER(CONCAT(LEFT(HEX(guid), 8),'-', MID(HEX(guid), 9,4), '-',MID(HEX(guid), 13,4), '-', MID(HEX(guid), 17,4), '-', RIGHT(HEX(guid), 12))),
LOWER(HEX(guid))
FROM test.table_code;
step 4) Result:
1 d1dfd973fa3d4b90a1eb47217162cd40 d1dfd973-fa3d-4b90-a1eb-47217162cd40 d1dfd973fa3d4b90a1eb47217162cd40
Everything looks fine - on the Mysql side.
So you are sure you are inserting 'd1dfd973fa3d4b90a1eb47217162cd40' (this must be done with UNHEX ('d1dfd973fa3d4b90a1eb47217162cd40')) or some other (reverse) value.
Anyway, to rotate a line like this: "73d9dfd13dfa904ba1eb47217162cd40" into this "d1dfd973-fa3d-4b90-a1eb-47217162cd40"
You can use code like this:
LOWER(CONCAT(
MID(HEX(guid), 7,2),
MID(HEX(guid), 5,2),
MID(HEX(guid), 3,2),
LEFT(HEX(guid), 2),'-',
MID(HEX(guid), 11,2),
MID(HEX(guid), 9,2),'-',
MID(HEX(guid), 15,2),
MID(HEX(guid), 13,2),'-',
MID(HEX(guid), 17,4), '-',
RIGHT(HEX(guid), 12)))
I think Mysql does not give us an easier way since this function works on binary data.
To test this:
Step 5)
INSERT INTO table_code (code,guid)
VALUES ('73d9dfd13dfa904ba1eb47217162cd40',UNHEX('73d9dfd13dfa904ba1eb47217162cd40'));
step 6)
SELECT
id,
code as `raw code`,
LOWER(CONCAT(LEFT(HEX(guid), 8),'-', MID(HEX(guid), 9,4), '-',MID(HEX(guid), 13,4), '-', MID(HEX(guid), 17,4), '-', RIGHT(HEX(guid), 12))),
LOWER(HEX(guid)) `decoded`,
LOWER(CONCAT(
MID(HEX(guid), 7,2),
MID(HEX(guid), 5,2),
MID(HEX(guid), 3,2),
LEFT(HEX(guid), 2),'-',
MID(HEX(guid), 11,2),
MID(HEX(guid), 9,2),'-',
MID(HEX(guid), 15,2),
MID(HEX(guid), 13,2),'-',
MID(HEX(guid), 17,4), '-',
RIGHT(HEX(guid), 12))) as switched
FROM test.table_code;
step 7) Everything seems to be in order.
source to share