CFQUERYPARAM在ColdFusion 10中不工作 [英] CFQUERYPARAM not working in ColdFusion 10

问题描述

我将三个整数传递到CFC中的函数，如下所示：

 < cfscript& 
 Q = TOPBIKES.GetTopBikes（127，10，11）; 
 writeDump（Q）; 
< / cfscript> 
  

CFC使用这些整数运行这样的查询：

 <！--- GET TOP BIKES ---> 
< cffunction name =GetTopBikes> 
< cfargument name =FeatureIDrequired =true> 
< cfargument name =MinWinsrequired =true> 
< cfargument name =RecordsToReturnrequired =true> 
< cfscript> 
 LOCAL.FeatureID = ARGUMENTS.FeatureID; 
 LOCAL.MinWins = ARGUMENTS.MinWins; 
 LOCAL.RecordsToReturn = ARGUMENTS.RecordsToReturn; 
< / cfscript> 
<！--- RUN QUERY ---> 
< cfquery name =Q> 
 SELECT TOP＃LOCAL.RecordsToReturn＃
 B.BikeID，
 B.BikeName，
 BS.PCTWins 
 FROM Bikes B 
 LEFT JOIN BikeScores BS 
 ON B.BikeID = BS.BikeID 
 WHERE BS.Wins> < cfqueryparam cfsqltype =cf_sql_integervalue =＃LOCAL.MinWins＃> 
 AND B.BikeID IN（SELECT BikeID 
 FROM Bikes_Features 
 WHERE FeatureID =< cfqueryparam cfsqltype =cf_sql_integervalue =＃LOCAL.FeatureID＃> 
）
 ORDER BY BS.PCTWins desc 
< / cfquery> 
< cfreturn Q> 
< / cffunction> 
  

问题是我不能得到cfqueryparam在SQL语句的TOP部分工作。 p>

这些工作：

  SELECT TOP 11 
 SELECT TOP＃ LOCAL.RecordsToReturn＃
  

这不起作用：

  SELECT TOP< cfqueryparam 
 cfsqltype =cf_sql_integer
 value =＃LOCAL.RecordsToReturn＃> 
  

我可以使用查询中的其他地方。

任何关于为什么CFQUERYPARAM不能在TOP工作的线索？

解决方案

选择顶部#val（LOCAL.RecordsToReturn）＃

SQL语句的某些部分不能使用cfqueryparam，例如From之后的Top或表名。

I am passing three integers into a function in a CFC, like this:

<cfscript>
Q = TOPBIKES.GetTopBikes(127, 10, 11);
writeDump(Q);
</cfscript>

The CFC uses these integers to run a query like this:

<!--- GET TOP BIKES --->
<cffunction name="GetTopBikes">
    <cfargument name="FeatureID" required="true">
    <cfargument name="MinWins" required="true">
    <cfargument name="RecordsToReturn" required="true">
    <cfscript>
        LOCAL.FeatureID = ARGUMENTS.FeatureID;
        LOCAL.MinWins = ARGUMENTS.MinWins;
        LOCAL.RecordsToReturn = ARGUMENTS.RecordsToReturn;
    </cfscript>
    <!--- RUN QUERY --->        
    <cfquery name="Q">
        SELECT      TOP #LOCAL.RecordsToReturn#
                    B.BikeID, 
                    B.BikeName,
                    BS.PCTWins
        FROM        Bikes B
                    LEFT JOIN BikeScores BS
                        ON B.BikeID = BS.BikeID
        WHERE       BS.Wins > <cfqueryparam cfsqltype="cf_sql_integer" value="#LOCAL.MinWins#">
                    AND B.BikeID IN (   SELECT  BikeID
                                        FROM    Bikes_Features
                                        WHERE   FeatureID = <cfqueryparam cfsqltype="cf_sql_integer" value="#LOCAL.FeatureID#">
                                    )   
        ORDER BY    BS.PCTWins desc
    </cfquery>
    <cfreturn Q>
</cffunction>

The problem is that I cannot get cfqueryparam to work in the TOP part of the SQL statement.

These work:

SELECT      TOP 11
SELECT      TOP #LOCAL.RecordsToReturn#

This does not work:

SELECT      TOP <cfqueryparam 
                  cfsqltype="cf_sql_integer" 
                  value="#LOCAL.RecordsToReturn#">

I can, however use anywhere else in the query. I know it's an integer and works when used elsewhere such in replacement of the FeatureID.

Any clue as to why CFQUERYPARAM is not working in TOP?

解决方案

SELECT TOP #val(LOCAL.RecordsToReturn)#

Some parts of a SQL statement cannot use cfqueryparam, such as Top or table name after From.

这篇关于CFQUERYPARAM在ColdFusion 10中不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！