选择“唯一识别符”键入cfquery(Selecting "uniqueidentifier" type in cfquery)

高性能WEB开发 IT屋
问 题

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!

解决方案

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.

本文地址:IT屋 » Selecting &quot;uniqueidentifier&quot; type in cfquery

问 题

我需要选择一个标识符与我的变量匹配的行。让我们说,我的变量值是“myvariable”(以uniqueidentifier形式,当然)。



 < cfset current_user_id =“myvariable”> 


这里是我的“cfquery”内容:



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


它返回以下错误:




[Macromedia] [SQLServer JDBC Driver] [SQLServer]列名称无效
'myvariable'。




数据和查询都很好。 SELECT * 的cfdump返回所有行,但添加 WHERE 子句会导致错误。查询工作正常,在ColdFusion外,即使与 WHERE 子句。



我试图添加这个, t工作:



 < cfqueryparam value =“#current_user_id#”cfsqltype =“cf_sql_idstamp”> 


有条件我应该知道选择“uniqueidentifier”类型吗?也许我需要将变量的类型设置为“uniqueidentifier”某种方式?



CF版本10,0



提前谢谢!


解决方案

尝试添加[cfqueryparam],但无法:




无法使用如何?本质上,唯一标识符将作为固定长度字符串。只要提供正确的值,用于字符的任何基本类型都是正确的:




  • CF_SQL_CHAR

  • CF_SQL_VARCHAR

  • CF_SQL_IDSTAMP (只是 CF_SQL_CHAR 的同义词)



这些类型的工作正常与ColdFusion 10/11和SQL Server 2008.所以这个问题可能是由于您的数据或代码的差异。



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>



将双引号更改为单引号,并且工作




虽然在技术上你可以使用引号 - 不要。 CFQueryparam 提供了更多的好处。例如,你不再需要担心引用字符串;-)更不用说像sql注入保护,数据类型检查,改进的查询性能等。


本文地址:IT屋 » 选择“唯一识别符”键入cfquery