ColdFusion参数化查询

Question

我有一个查询，我试图填充我要参数化的CFChart：

<cfquery name="total" datasource="#datasource#">
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = #month# 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")# 
    AND    technician_id = #techID#
 </cfquery>

这是我尝试过的：

<!---Open tickets from chosen year where technician is active --->
<cfquery name="total" datasource="#datasource#">          
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = <CFQUERYPARAM Value="#month#"> 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"> 
    AND    technician_id = <CFQUERYPARAM Value="#techID#">
</cfquery>

当我将查询更改为此时，它将以某种方式破坏CFChart。屏幕上没有出现任何CFError，但是我的CFChart为空白。

我在查询中将其范围缩小到与此相关：

#dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"

当我删除查询的此参数化部分并放入

#dateFormat(theMonth,"yyyy")#

有用。

谁能对此有所启发？

Answer 1

屏幕上没有出现任何CFError，但是我的CFChart为空白。

暂时忽略正确的方法，发生的原因是您cfsqltype对参数使用了错误的参数。因此，实际上您正在向数据库发送不同的值（并因此执行不同的比较）。结果，查询无法找到任何匹配的记录。这就是为什么图表空白的原因。

通过使用，cf_sql_timestamp您正在将“值”转换为完整的日期/时间对象。但是，YEAR（）仅返回一个四位数的数字。因此，您正在比较苹果和橙子。从概念上讲，您的查询实际上是在这样做：

  WHERE  2014 = {ts '2009-02-13 23:31:30'}

它不会引发错误的原因是日期/时间值在内部存储为数字。因此，您实际上是在将一个较小的数字（即年份）与一个很大的数字（即日期/时间）进行比较。显然，日期值会大得多，因此它几乎永远不会与年份匹配。同样，
从概念上讲， 您的查询正在执行此操作：

 WHERE 2014 = 1234567890

由于cfsqltype是可选的，所以很多人认为它不是很重要-而是。

• 验证： 除了其其他好处之外，cfqueryparam还基于cfsqltype（日期，日期和时间，数字等）验证提供的“值” 。这是 在 将sql发送到数据库 之前 发生的。因此，如果输入无效，则不会浪费数据库调用。如果您省略cfsqltype，或者仅使用默认值ie字符串，那么您将失去该额外的验证。

• 准确性 选择正确的cfsqltype可确保您将正确的值发送到数据库。如上所示，使用错误的类型可能导致CF向数据库发送错误的值。

该cfsqltype还确保值在无歧义的格式提交给数据库的数据库将解释你期望的方式。从技术上讲，您可以将所有内容发送到数据库。但是，这迫使数据库执行隐式转换（通常是不希望的）。

使用隐式转换时，字符串的解释完全由数据库决定-
并不一定总能得到您期望的答案。以日期而不是日期对象的形式提交日期是一个很好的例子。当前数据库将如何解释日期字符串，例如“
05/04/2014”？是4月5日还是5月4日？这取决于。更改数据库或数据库设置，结果可能会完全不同。

确保结果一致的唯一方法是指定适当的cfsqltype。它应与比较列/函数的数据类型匹配，或至少与等效类型匹配。对于YEAR()，它将返回一个四位数的数字。所以，你应该使用cf_sql_integer，因为阿德里安提到的意见。您的MONTH（）比较也是如此。

 WHERE Year(ColumnName) = <cfqueryparam value="2014" cfsqltye="CF_SQL_INTEGER">
 AND   Month(ColumnName) = <cfqueryparam value="11" cfsqltye="CF_SQL_INTEGER">

说完这些，Dan的建议是执行日期比较的更好方法。该范例更加索引友好，并且不管您的目标列包含日期（仅）还是日期和时间，该范例都可以工作。请注意cf_sql_date在他的示例中的使用。

• cf_sql_timestamp -发送日期和时间
• cf_sql_date-仅发送日期。时间值被截断