Selecting “uniqueidentifier” type in cfquery

问题:

I need to select a row where identifier matches my variable. Let's say, my variable value is "myvariable" (in uniqueidentifier form, of course).

<cfset current_user_id = "myvariable"> 

Here's my "cfquery" content:

<cfquery name="findUser" datasource="#SOURCE#">
    SELECT
        db.[User].UserID
    FROM
        db.[User]
    WHERE
        db.[User].UserID = "#current_user_id#";
</cfquery>

It returns the following error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'myvariable'.

The data and query is fine. A cfdump of SELECT * returns all of the rows, but the addition of the WHERE clause causes an error. The query works fine, outside ColdFusion, even with WHERE clause.

I tried adding this, but it doesn't work either:

<cfqueryparam value = "#current_user_id#" cfsqltype="cf_sql_idstamp">  

Is there something I should know about selecting the "uniqueidentifier" type with conditions? Maybe I need to set the type of my variable to "uniqueidentifier" somehow?

CF version 10,0

Thank you in advance!

回答1:

eg:

<cfqueryparam cfsqltype="cf_sql_varchar" value="1890958b-dec9-0dfc-c372-306a897693ea" />

Note this is using a MSSQL compatible version of a UUID from the CFLIB.ORG CreateGUID() function.

回答2:

Tried adding [cfqueryparam] but it doesn't work either:

Does not work how? Essentially, a uniqueidentifier will be handled as a fixed length string. As long as you supply the correct value, any of the base types used for characters is fine:

• CF_SQL_CHAR
• CF_SQL_VARCHAR
• CF_SQL_IDSTAMP (just a synonym for CF_SQL_CHAR)

All of those types work fine with ColdFusion 10/11 and SQL Server 2008. So the problem is likely due to a difference in your data or code.

DDL:

CREATE TABLE TestTable ( UserID UNIQUEIDENTIFIER, UserName VARCHAR(100) );

CODE:

<cfset current_user_id = "6F9619FF-8B86-D011-B42D-00C04FC964FF">

<!--- 1:   CF_SQL_IDSTAMP --->
<cfquery name="qTest">
    SELECT  UserID
    FROM    TestTable 
    WHERE   UserID = <cfqueryparam value = "#current_user_id#" cfsqltype="cf_sql_idstamp"> 
</cfquery>

<!--- 2:   CF_SQL_CHAR --->
<cfquery name="qTest">
    SELECT  UserID, UserName
    FROM    TestTable 
    WHERE   UserID = <cfqueryparam value = "#current_user_id#" cfsqltype="cf_sql_char"> 
</cfquery>

<!--- 3:   CF_SQL_VARCHAR --->
<cfquery name="qTest">
    SELECT  UserID, UserName
    FROM    TestTable 
    WHERE   UserID = <cfqueryparam value = "#current_user_id#" cfsqltype="cf_sql_varchar"> 
</cfquery>

changed double quotes to single quotes and it worked

Though technically you can use quotes - don't. CFQueryparam provides a lot more benefits. For example, you no longer have to worry about quoting strings ;-) Not to mention things like - sql injection protection, data type checking, improved query performance, etcetera.