Re: Guid (UUID) and binary(19) just works

[Elmer Bulthuis]

Hi there

The MySql BINARY datatype seems to have some problems with the following byte values:

128,130,131,132,133,134,135,136,137,138,139,140,142,145,146,147,148,149,150,151,152,153,154,155,156,158,159

Fortunately it does nog have any problems with byte values smaller than 128. So we van safely use the first 7 bits of every byte.

To store a GUID we need 16 bytes, if we only use the first 7 bits of every byte, we need 16*8/7 = 18,29 = 19 bytes. Use BINARY(19) as the datatype to store your GUID.

Now all we need is some functions for converting our MySql BINARY(19) field to a C# GUID.

The C# code below shows how it can be done.

static string MySqlFromGuid(Guid guid)
{
StringBuilder sb;
byte[] bytes, safeBytes;

bytes = guid.ToByteArray();
safeBytes = new byte[19];

safeBytes[0] = (byte)(bytes[0] & 127);
safeBytes[1] = (byte)(((bytes[0] & 128) >> 7) | ((bytes[1] & 63) << 1));
safeBytes[2] = (byte)(((bytes[1] & 192) >> 6) | ((bytes[2] & 31) << 2));
safeBytes[3] = (byte)(((bytes[2] & 224) >> 5) | ((bytes[3] & 15) << 3));
safeBytes[4] = (byte)(((bytes[3] & 240) >> 4) | ((bytes[4] & 7) << 4));
safeBytes[5] = (byte)(((bytes[4] & 248) >> 3) | ((bytes[5] & 3) << 5));
safeBytes[6] = (byte)(((bytes[5] & 252) >> 2) | ((bytes[6] & 1) << 6));
safeBytes[7] = (byte)((bytes[6] & 254) >> 1);

safeBytes[8] = (byte)(bytes[7] & 127);
safeBytes[9] = (byte)(((bytes[7] & 128) >> 7) | ((bytes[8] & 63) << 1));
safeBytes[10] = (byte)(((bytes[8] & 192) >> 6) | ((bytes[9] & 31) << 2));
safeBytes[11] = (byte)(((bytes[9] & 224) >> 5) | ((bytes[10] & 15) << 3));
safeBytes[12] = (byte)(((bytes[10] & 240) >> 4) | ((bytes[11] & 7) << 4));
safeBytes[13] = (byte)(((bytes[11] & 248) >> 3) | ((bytes[12] & 3) << 5));
safeBytes[14] = (byte)(((bytes[12] & 252) >> 2) | ((bytes[13] & 1) << 6));
safeBytes[15] = (byte)((bytes[13] & 254) >> 1);

safeBytes[16] = (byte)(bytes[14] & 127);
safeBytes[17] = (byte)(((bytes[14] & 128) >> 7) | ((bytes[15] & 63) << 1));
safeBytes[18] = (byte)(((bytes[15] & 192) >> 6));

sb = new StringBuilder();

for (int i = 0; i < 19; i++) sb.Append((char)safeBytes);

return sb.ToString(); ;
}
static Guid MySqlToGuid(string s)
{
char[] chars;
byte[] bytes, safeBytes;

bytes = new byte[16];
safeBytes = new byte[19];

chars = s.ToCharArray();

for (int i = 0; i < 19; i++) safeBytes = (byte)chars;

bytes[0] = (byte)(safeBytes[0] | ((safeBytes[1] & 1) << 7));
bytes[1] = (byte)(((safeBytes[1] & 254) >> 1) | ((safeBytes[2] & 3) << 6));
bytes[2] = (byte)(((safeBytes[2] & 252) >> 2) | ((safeBytes[3] & 7) << 5));
bytes[3] = (byte)(((safeBytes[3] & 248) >> 3) | ((safeBytes[4] & 15) << 4));
bytes[4] = (byte)(((safeBytes[4] & 240) >> 4) | ((safeBytes[5] & 31) << 3));
bytes[5] = (byte)(((safeBytes[5] & 224) >> 5) | ((safeBytes[6] & 63) << 2));
bytes[6] = (byte)(((safeBytes[6] & 192) >> 6) | ((safeBytes[7] & 127) << 1));

bytes[7] = (byte)(safeBytes[8] | ((safeBytes[9] & 1) << 7));
bytes[8] = (byte)(((safeBytes[9] & 254) >> 1) | ((safeBytes[10] & 3) << 6));
bytes[9] = (byte)(((safeBytes[10] & 252) >> 2) | ((safeBytes[11] & 7) << 5));
bytes[10] = (byte)(((safeBytes[11] & 248) >> 3) | ((safeBytes[12] & 15) << 4));
bytes[11] = (byte)(((safeBytes[12] & 240) >> 4) | ((safeBytes[13] & 31) << 3));
bytes[12] = (byte)(((safeBytes[13] & 224) >> 5) | ((safeBytes[14] & 63) << 2));
bytes[13] = (byte)(((safeBytes[14] & 192) >> 6) | ((safeBytes[15] & 127) << 1));

bytes[14] = (byte)(safeBytes[16] | ((safeBytes[17] & 1) << 7));
bytes[15] = (byte)(((safeBytes[17] & 254) >> 1) | ((safeBytes[18] & 3) << 6));

return new Guid(bytes);
}


now we can read our GUID like this:

id = MySqlToGuid(reader.GetString(0));


and we can add a GUID as a MySqlParameter like this:

cmd.Parameters.Add("?ID", MySqlFromGuid(id));


greets, Elmer



Edited 3 time(s). Last edit at 10/26/2006 01:20PM by Elmer Bulthuis.