cfquery Syntax Error

1
votes

I'm receiving the following error and can not figure out why:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'. The error occurred on line 141.

<cfquery datasource="mySource" name="getTravel">
    select traveler, customernumber, destination, tripdate, purpose, empid, pm_reportid, company
    from mySource.dbo.PM_Travel
    where pm_reportid in 
    (
        <cfloop from="1" to="#getRecentReports.recordcount#"  index="i">
            <cfif i lt getRecentReports.recordcount>
                 #getRecentReports.pm_reportid[i]#,         
            <cfelse>
                 #getRecentReports.pm_reportid[i]#
            </cfif>
         </cfloop>
    )
    order by customernumber
</cfquery>

Line 141 is:

where pm_reportid in (<cfloop from="1" to="#getRecentReports.recordcount#"  index="i"><cfif i lt getRecentReports.recordcount>#getRecentReports.pm_reportid[i]#, <cfelse>#getRecentReports.pm_reportid[i]#</cfif></cfloop>)

This was not written by me, and I'm new to CF. Any help is appreciated.

2
sql-servercoldfusion
Tip: If full debugging is enabled, the error message should also include the generated SQL. Examining the actual sql sent to the db should help you spot the problem. Without seeing the actual sql, I am guessing the getRecentReports query is empty OR one or more of the pm_reportid's is null. So the cfloop produces and invalid SQL statement. ie For example an empty clause WHERE Col IN ( {nothing here} ) or missing id's WHERE Col (11,22,33,{no value here},44). - Leigh
What is the source of the data in getRecentReports? It is possible you could simplify the query by using a JOIN instead of a cfloop. - Leigh
getRecentReports gets pm_reportid from a table called pm_reports based on matching form inputs (#form.empid# and #form.weekid#.) - brianh
Then you could probably simplify it by using a JOIN instead. I will post the general idea as an answer in a minute. - Leigh

2 Answers

7
votes

Try this:

where pm_reportid in ( <cfqueryparam value="#valueList(getrecentReports.pm_report_id)#" list="true" cfSqlType="CF_SQL_NUMERIC" /> )

You may need to change the value of cfSqlType to match the type of data that is contained in the 'pm_report_id column'. If it is text, try using CF_SQL_VARCHAR

To explain what is going on:

valueList() takes a query column, in the syntax of 'queryName.columnName', and converts it to a comma delimited list.

cfqueryparam parameterizes the query which will give you a performance boost on the SQL side and offers a bit of security as the data is passed as a certain data type.

4
votes

(Follow up from the comments)

As I mentioned, the cfloop code is probably generating an invalid sql statement. Either due to the query containing zero (0) records, which would produce an empty clause:

 WHERE Col IN ( {nothing here} )

... or one of the id's is null, which would create an invalid clause with extra commas, like: 

 WHERE Col (11,22,33,{no value here},44)

Scott's answer shows one way to handle this. Another option is to use a JOIN instead of looping through the query results. Simply join the two tables on the matching column(s) and use the appropriate WHERE filters. The exact SQL depends on the relationship between the two tables, but below is the general idea. I do not know the exact names or data types of the columns involved, so modify as needed. 

<cfquery ....>
    SELECT t.traveler, t.customernumber, t.destination, ...more columns ...
    FROM  PM_Travel t INNER JOIN pm_reports r
              ON t.pm_reportid = r.pm_reportid
    WHERE  r.empID = <cfqueryparam value="#form.empid#" cfsqltype="cf_sql_integer">
    AND    r.weekID = <cfqueryparam value="#form.weekid#" cfsqltype="cf_sql_integer">
</cfquery>

Side note, I know this is an inherited application, but be sure to use cfqueryparam on all user supplied values. Both for enhancing performance, and to provide an extra layer of protection against a common form of sql injection, as Scott mentioned. You can find a mapping of the cfsqltypes by dbms in the CF documentation.