ColdFusion参数化查询 [英] ColdFusion Parameterizing a Query

问题描述

我有一个查询，我运行填充CFChart，我试图参数：

I have a query that I run to populate a CFChart that I am trying to parametrize:

<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是空白的。

When I change my query to this it breaks my CFChart somehow. I don't get any CFErrors on the screen but my CFChart is blank.

我已经缩小到与我的查询相关：

I have narrowed it down to being related to this in my query:

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

当我删除这个参数化的部分查询，只是把

When I remove this parametrized part of the query and just put

#dateFormat(theMonth,"yyyy")#

它可以工作。

任何人都可以了解这个吗？

Can anyone shed some light on this?

推荐答案

屏幕上的任何CFErrors，但我的CFChart为空。

I don't get any CFErrors on the screen but my CFChart is blank.

忽略正确方法一会儿，发生的原因是您对参数使用了不正确的 cfsqltype 。所以你实际上发送不同的值到数据库（并因此执行不同的比较）比你想。因此，查询无法找到任何匹配的记录。这是为什么你的图表是空白的。

Ignoring the correct approach for a moment, the reason that happens is that you are using the incorrect cfsqltype for the parameters. So you are actually sending different values to the database (and consequently performing a different comparison) than you are thinking. As a result, the query fails to find any matching records. That is why your chart is blank.

通过使用 cf_sql_timestamp /时间对象。但是， YEAR（）只返回一个四位数字。所以你比较苹果和橘子。从概念上讲，您的查询实际上是这样做的：

By using cf_sql_timestamp you are converting the "value" into a full date/time object. However, YEAR() only returns a four digit number. So you are comparing apples and oranges. Conceptually, your query is actually doing this:

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

不会引发错误的原因是日期/时间值在内部存储为数字。所以你实际上是比较一个小数字（即年）到一个真正的大数字（即日期/时间）。显然日期值会大得多，所以它几乎永远不匹配年数。此外， 您的查询执行此操作：

The reason it does not throw an error is that date/time values are stored as numbers internally. So you are actually comparing a small number (ie year) to a really big number (ie date/time). Obviously the date value will be much bigger, so it will almost never match the year number. Again, conceptually your query is doing this:

 WHERE 2014 = 1234567890

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

Since cfsqltype is optional, a lot of people think it is not very important - but it is.

• 验证：除了其他优点之外，cfqueryparam根据 cfsqltype （日期，日期和时间，数字等）。这发生在 之前，sql被发送到数据库。因此，如果输入无效，您不会浪费数据库调用。

• Validation: In addition to its other benefits, cfqueryparam validates the supplied "value", based on the cfsqltype (date, date and time, number, etcetera). This occurs before the sql is ever sent to the database. So if the input is invalid, you do not waste a database call. If you omit the cfsqltype, or just use the default ie string, then you lose that extra validation.

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

Accuracy Selecting the proper cfsqltype ensures you send the correct value to the database. As demonstrated above, using the wrong type can cause CF to send the wrong value to the database.

cfsqltype 也确保以非模糊格式将值提交到数据库，数据库将解释你期望。技术上你可以发送一切到数据库一个字符串。但是，这会强制数据库执行隐式转换（通常不合需要）。

The cfsqltype also ensures values are submitted to the database in a non-ambiguous format the database will interpret the way you expect. Technically you could send everything to the database a string. However, that forces the database to perform implicit conversion (usually undesirable).

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

With implicit conversion, the interpretation of the strings is left entirely up to the database - and it might not always come up with the answer you would expect. Submitting dates as strings, rather than date objects, is a prime example of that. How will the current database interpret a date string like "05/04/2014"? As April 5th or a May 4th? It depends. Change the database or the database settings and the result may be completely different.

确保结果一致的唯一方法是以指定适当的cfsqltype。它应该匹配比较列/函数的数据类型，或至少一个等效类型。在 YEAR（）的情况下，它返回一个四位数字。因此，您应该使用 cf_sql_integer 作为 Adrian提及评论。这同样适用于您的 MONTH（）比较。

The only way to ensure consistent results is to specify the appropriate cfsqltype. It should match the data type of the comparison column/function, or at least an equivalent type. In the case of YEAR(), it returns a four digit number. So you should use cf_sql_integer, as Adrian mentioned the comments. The same applies to your MONTH() comparison.

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

现在已经说过了， Dan的建议是执行日期比较的更好方法。 该范例更具索引性，无论您的目标列包含日期（仅）或日期和时间。请注意在他的示例中使用 cf_sql_date 。

Now having said all that, Dan's suggestion is the better way to perform date comparisons. That paradigm is more index friendly and works regardless of whether your target column contains a date (only) or a date and time. Note the use of cf_sql_date in his example.

• cf_sql_timestamp - 发送日期和时间


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

• cf_sql_timestamp - sends both a date and time
• cf_sql_date - sends a date only. the time value is truncated

这篇关于ColdFusion参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！