选择“唯一识别符”键入cfquery [英] Selecting "uniqueidentifier" type in cfquery

问题描述

我需要选择一个标识符与我的变量匹配的行。让我们说，我的变量值是myvariable（以uniqueidentifier形式，当然）。

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"> 

这里是我的cfquery内容：

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>

它返回以下错误：

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

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

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

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.

我试图添加这个， t工作：

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

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

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

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版本10,0

提前谢谢！

推荐答案

尝试添加[cfqueryparam]，但无法：

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 （只是 CF_SQL_CHAR 的同义词）

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

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

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

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

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.

这篇关于选择“唯一识别符”键入cfquery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！