ColdFusion 参数化查询

我运行一个查询来填充我试图参数化的 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。我的屏幕上没有出现任何 CFErrors，但我的 CFChart 是空白的。

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

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

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

#dateFormat(theMonth,"yyyy")#

有用。

有人能解释一下吗？

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

忽略正确的做法 https://stackoverflow.com/a/27062906/104223暂时，发生这种情况的原因是您使用了不正确的cfsqltype对于参数。因此，您实际上向数据库发送了与您想象的不同的值（并因此执行了不同的比较）。结果，查询无法找到任何匹配的记录。这就是为什么你的图表是空白的。

通过使用cf_sql_timestamp您正在将“值”转换为完整的日期/时间对象。然而，YEAR() http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_year只返回一个四位数。所以你正在比较苹果和橙子。从概念上讲，您的查询实际上是这样做的：

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

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

 WHERE 2014 = 1234567890

由于 cfsqltype 是可选的，很多人认为它不是很重要 - 但它确实很重要。

• 验证：除了其他好处之外，cfqueryparam 还根据以下内容验证提供的“值”cfsqltype（日期、日期和时间、数字等）。出现这种情况beforesql 被发送到数据库。因此，如果输入无效，您就不会浪费数据库调用。如果您省略 cfsqltype，或者仅使用默认的 ie 字符串，那么您将失去额外的验证。

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

  The cfsqltype还确保以明确的格式将值提交到数据库，数据库将按照您期望的方式进行解释。从技术上讲，您可以将所有内容以字符串形式发送到数据库。但是，这会强制数据库执行隐式转换 https://dev.mysql.com/doc/refman/5.5/en/type-conversion.html（通常是不需要的）。

  通过隐式转换，字符串的解释完全取决于数据库 - 并且它可能并不总是能给出您期望的答案。将日期作为字符串而不是日期对象提交就是一个很好的例子。当前数据库将如何解释像“05/04/2014”这样的日期字符串？ 4月5日还是5月4日？这取决于。更改数据库或数据库设置，结果可能完全不同。

确保结果一致的唯一方法是指定适当的 cfsqltype。它应该与比较列/函数的数据类型匹配，或者至少是等效类型。如果是YEAR()，它返回一个四位数。所以你应该使用cf_sql_integer, as 阿德里安提到了评论 https://stackoverflow.com/questions/27049918/coldfusion-parameterizing-a-querie#comment42618043_27050120。这同样适用于您的MONTH() https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html比较。

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

说了这么多，丹的建议 https://stackoverflow.com/a/27062906/104223是执行日期比较的更好方法。那个范式 https://stackoverflow.com/questions/13666883/date-comparison-in-ms-sql-2005/13687006#13687006索引更加友好，无论您的目标列是否包含日期（仅）或日期和时间，都可以工作。注意使用cf_sql_date在他的例子中。

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