coldfusion - 使用 cfquery cfqueryparam 出现奇怪的 ColdFusion 10 错误

Question

我正在使用 ColdFusion 10 Update 23 和 MySQL 数据库。当我对使用 cfqueryparm 的脚本进行更改时，该脚本会导致此错误消息:“无法确定标签 queryparam 属性值的类型。”

这个脚本完美运行:

<cfquery name="i" datasource="tasktrack">
UPDATE qa_commitments
SET 
    commit_division = <cfqueryparam cfsqltype='cf_sql_integer' value='#trim(commit_division)#'/>,
    commit_source = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_source))#'/>,
    commit_title = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_title))#'/>,
    commit_responsibility = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_responsibility))#'/>,
    <cfif Len(commit_cause)>commit_cause = <cfqueryparam cfsqltype='cf_sql_varchar' value='#commit_cause#'/>
    <cfelse>commit_cause = NULL</cfif>,
    commit_comments = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(PreserveSingleQuotes(commit_comments)))#'/>,
    commit_issue_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_issue_date#'/>,
    commit_response_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_due_date#'/>,
    commit_response_compl_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_compl_date#'/>,
    commit_action_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_action_due_date#'/>
    <cfif IsDefined('commit_closeing_date')> ,commit_closeing_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_closeing_date#'/></cfif>
WHERE ID = #id# 

如果我打开脚本并添加任何内容(即返回、选项卡、注释)，然后保存脚本，我会收到上述错误。如果我从旧版本恢复脚本，它会再次正常工作。我打开旧的，用输入键插入换行符，保存它，然后它再次中断。真正奇怪的部分是，如果我完全删除 cfqueryparam 标签，脚本会再次工作。这种情况发生在我使用此标签的每个脚本上。CF 的最后一次更新是 2017 年 4 月，有几个较新的脚本可以工作，除非我编辑它们。我尝试过不同的编辑器，但结果相同。我用谷歌搜索了我的屁股没有结果。有人可以指出我正确的方向吗？

Answer 1

我无法告诉你它为什么坏了，但我可以告诉你如何修复它。

推杆<cfif>查询内部的 s 使得数据库引擎无法缓存查询。因此，我会将它们移出，并移出查询中的条件逻辑。

<cfquery name="i" datasource="tasktrack">
DECLARE @commit_clause varchar(40) = <cfqueryparam cfsqltype='cf_sql_varchar' value='#commit_cause#' null="#IIF(len(commit_clause)1, 0)#"/>
DECLARE @commit_closeing_date date = cfqueryparam cfsqltype='cf_sql_date' value='#commit_closeing_date#' null="#IIF(isDefined(Commit_closeing_date), 0, 1)#"/>.

UPDATE qa_commitments
SET 
    commit_division = <cfqueryparam cfsqltype='cf_sql_integer' value='#trim(commit_division)#'/>,
    commit_source = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_source))#'/>,
    commit_title = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_title))#'/>,
    commit_responsibility = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(commit_responsibility))#'/>,
    commit_cause = @commit_clause,
    commit_comments = <cfqueryparam cfsqltype='cf_sql_varchar' value='#ucase(trim(PreserveSingleQuotes(commit_comments)))#'/>,
    commit_issue_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_issue_date#'/>,
    commit_response_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_due_date#'/>,
    commit_response_compl_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_response_compl_date#'/>,
    commit_action_due_date = <cfqueryparam cfsqltype='cf_sql_date' value='#commit_action_due_date#'/>,
    commit_closeing_date = @commit_closeing_date
WHERE ID = #id# /* I would fix this too */
</cfquery>

偏离主题

我会考虑使用实体。我真的不喜欢一遍又一遍地写这些东西。