Answered
cfqueryparam ignores null="yes" when list="yes"
I get an unexpected result when using cfqueryparam with
list="yes" and null="yes" using CF 7.0.1 and MSSQL 2000.
When i run this code:
<cfset arguments.userIds = ""/>
<cfquery datasource="dsn">
select id
from users
where id in (<cfqueryparam value="#arguments.userIds#" cfsqltype="cf_sql_integer" list="true" null="#not listLen(arguments.userIds)#"/>)
</cfquery>
It throws an error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near ')'.
SQL: select id from users where id in ()
If I remove list="yes" from the cfqueryparam tag, it works fine and sends "select id from users where id in (null)" to SQL server (which is a valid statement) and the page loads successfully. It appears that with list="yes" and null="yes", it does not send the "null" as I would expect like it does when list="no". I cannot find any documentation on using null="yes" with list="yes", so I can't tell if this is intended functionality or a bug. I am getting a SQL error, not a CF error, so CF sees it as syntactically correct.
Thanks.
When i run this code:
<cfset arguments.userIds = ""/>
<cfquery datasource="dsn">
select id
from users
where id in (<cfqueryparam value="#arguments.userIds#" cfsqltype="cf_sql_integer" list="true" null="#not listLen(arguments.userIds)#"/>)
</cfquery>
It throws an error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near ')'.
SQL: select id from users where id in ()
If I remove list="yes" from the cfqueryparam tag, it works fine and sends "select id from users where id in (null)" to SQL server (which is a valid statement) and the page loads successfully. It appears that with list="yes" and null="yes", it does not send the "null" as I would expect like it does when list="no". I cannot find any documentation on using null="yes" with list="yes", so I can't tell if this is intended functionality or a bug. I am getting a SQL error, not a CF error, so CF sees it as syntactically correct.
Thanks.
AdChoices