cfqueryparam with uniqueidentifier type in table

Question

I have a query where I am attempting to insert values into a table and one of these values (ImportID) is of type uniqueidentifier in the database. I have looked in the adobe CF documentation and saw that both cf_sql_char and cf_sql_idstamp should work for the cfquery param for my uniqueidentifier type. I also read this thread which says the same thing: What is the most appropriate Coldfusion cfsqltype to use for MS SQL's uniqueidentifier field type?

I have found a few other places that use the "maxlength" parameter for the cfqueryparam tag and cf_sql_char as the type but have not been able to make my code work. I keep getting an error along the lines of "Conversion failed when converting from a character string to uniqueidentifier." I'm including my code where I generate the UID and attempt the insert below (I replaced some of the variable names and took out all of the other columns for the import):

<cfscript>
    ImportID = createUUID();
</cfscript>

<cfquery name="INSERTTableName" datasource="#Application.Datasource#">
    INSERT INTO TableNameImport(
        ImportID
    )
    VALUES(
        <cfqueryparam value="#variables.ImportID#" cfsqltype="CF_SQL_CHAR">
    )
</cfquery>

Any help would be greatly appreciated. Thanks in advance.

*********************************EDIT********************************

Found the answer:

<cfquery name="INSERTTableName" datasource="#Application.Datasource#">
    DECLARE @ImportID uniqueidentifier
    SET @ImportID = NEWID()

    INSERT INTO TableNameImport(
        ImportID
    )
    VALUES(
        @ImportID
    )
</cfquery>

The problem was that the UUID created from the coldfusion call is a different format from the one created in Microsoft SQL Server.

score 0 · Answer 1 · answered Jul 03 '12 at 15:16

0

You are correct. SQL uses a GUID and createUUID manks a UUID. Both are 32hex value but use a differnt text format (location of dashes). And the use a different calculation to generate the random value. You can utilize UUID's if you change the text formating to have the GUID dash locations or send the binary value. For your example above that works you might also want return the GUID if cf needs a reference to that value.

answered Jul 03 '12 at 15:16

DanielD

345
3
7

yes, I did return the GUID back to coldfusion as I was selecting the records for that insert later down the line. thanks for the response! – David Lance Jul 06 '12 at 18:27

cfqueryparam with uniqueidentifier type in table

1 Answers1