List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 13 2004 1:41am
Subject:Re: GUID storage
View as plain text  
In the last episode (May 12), Larry Lowry said:
> A uniqueidentifier in MS SQL is basically a guid.  I am generating
> them via System.Guid.NewGuid().ToString(N") in the Dot Net
> framework which now returns me a string of 32 characters (hex). 
> Internally I understand it is a 128-bit integer.  As an option I could
> store that in MySql.  What data type would that be?
> 
> How would one convert to binary char(16)?  In what documentation
> would I find this?   I do not know enough yet to write a UDF.  

You'll need to build your own INSERT statement, since I don't know of
any way to convert an arbitrary hex string to a binary with mysql
functions (although you can use the HEX() function to go the other
way).  What you can do is insert a hex value directly into a binary
field.  Say your generated uuid was "cb0217f9-a47b-11d8-89f3-0080ad08fb4f":

UPDATE mytable SET myfield=CAST(0xcb0217f9a47b11d889f30080ad08fb4f as INARY);

It must be exactly like this, not within quotes.  You also might want
to try just setting your uniqueid field directly using bind variables/
placeholders.  If ODBC is smart enough, it will realize that both
source and destination are 16 bytes and just pass the raw binary value
without any bin->hex->bin conversion.  Another option is to convert the
guid to a raw string yourself with .net, and pass that to mysql.

-- 
	Dan Nelson
	dnelson@stripped
Thread
GUID storageLarry Lowry12 May
  • Re: GUID storageMike Hillyer12 May
  • Re: GUID storageJeremy Zawodny12 May
    • Re: GUID storageUnknown Sender29 Jun
      • Re: GUID storageDan Nelson29 Jun
  • Re: GUID storageDan Nelson12 May
  • Re: GUID storageLarry Lowry12 May
    • Re: GUID storageDan Nelson13 May
    • Re: GUID storageSergei Golubchik13 May