ColdFusion 参数化查询

2021-11-20 00:00:00 mysql coldfusion

我运行一个查询来填充我试图参数化的 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")#

它有效.

有人能解释一下吗?

推荐答案

我没有在屏幕上看到任何 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 被发送到数据库.因此，如果输入无效，则不会浪费一次数据库调用.如果您省略 cfsqltype，或者只使用默认的 ie 字符串，那么您将失去额外的验证.

  • 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

相关文章