How to create a Coldfusion UUID from MySQL
Posted by john at 2:34 PM
0 comments - Categories: Coldfusion | Adobe | MySQL
I was moving digital roar's project tracking software off of a php based project management on to Project Tracker version 2.4.5, developed by Joe Danziger. I ran into an issue converting primary key id's, for primary key ID's he uses createuuid() function to generate them and cfqueryparams the ids to expect uuid's. so After my integration I was getting a lot errors and unexpected behavior. I ran across this blog and it discussion of the MS SQL newID(). which describes the following:
the createUUID() function in ColdFusion produces a 35 character string in the format of:
xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx (8-4-4-16).
the uuid() function in MySQL(MSSQL's newid() too) producesa 36 character string in the format:
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (8-4-4-4-12)
so created a little script to convert uuid() into an accepteable coldfusion uuid.
set @myuid = uuid()
GO
SELECT
CONCAT(
CONCAT(
left (@myuid, 18),"-"), replace(right(@myuid,instr(reverse(@myuid),"-") + 4),"-",""))
I hope someone else finds this as useful as I did.