MSSQL CFUUID Function
Michael Sharman over at the Chapter 31 blog has a nice post on working with ColdFusion UUID's and MSSQL. One of his functions to create a uuid uses the newid() function twice to generate a ColdFusion compatible UUID. While it may work, each call to the newid() generates a new ms uuid. I had created a similar function for Oracle awhile back so I whipped up a quick MSSQL user defined function to provide the same result.
The below custom function, which uses the same string concat function from Michael's blog entry is a little cleaner in that it only creates one ms uuid and then creates the cf uuid from that.
The Function
CREATE FUNCTION dbo.cfuuid(@msID varchar(36))
RETURNS varchar(35)
AS
BEGIN
DECLARE @myCFID varchar(35)
SET @myCFID = left(@msID, 23) + right(@msID,12)
RETURN @MyCFID
END
To call it;
select dbo.cfuuid(newid()) as myid
I find this useful. How can a similar thing like this be done in MySQL?
Not that familiar with MySQL. Anybody?
I believe MySQL uses the UUID() function. But I don't think MySQL has a uniqueidentifier data type so you might have to use char(36).\n\nNote that UUID() is in the MSSQL format (36 characters with 4 hyphens)
Creating a function like this has been on my mind for some time. I just never got around to creating it. Thanks for doing it! I am now using it in association with a trigger that adds the UUID as a default value in a column:\n\nCREATE TRIGGER [createUuid] ON [dbo].[Subscribers] \nFOR INSERT \nAS\nDECLARE @currentID int\nSELECT @currentID = (SELECT subscriberID FROM Inserted)\n\nUPDATE subscribers SET emailToken = (SELECT dbo.cfuuid(newid())) WHERE subscriberID = @currentID
I just posted a more detailed entry on CF Vs. SQL UUID datatypes, showing how to create defaults and constraints, to maintain data integrity.\n\nhttp://www.robgonda.com/blog/index.cfm/2007/2/4/Co...
