tracker issue : CF-3684235

select a category, or use search below
(searches all categories and all time range)
Title:

Derby datasource doesn't work with CFQUERYPARAM null values

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/ThirdParty

Reporter/Name(from Bugbase): Nathan Dintenfass / Nathan Dintenfass (nathandintenfas s)

Created: 12/13/2013

Components: Database

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: Final /

Priority/Frequency: Normal / Some users will encounter

Locale/System: English / Platforms All

Vote Count: 0

Problem Description:

When passing NULL="TRUE" in CFQUERYPARAM using an Apache Derby datasource the query does not run, throwing the exception: "Syntax error: Encountered "?" at line [X], column [Y]."

Steps to Reproduce:

The following is a simple example of the error (the same query approach works in mySQL datasources):

<cfquery name="foo" datasource="cfartgallery">
  select * from artists where fax IS <cfqueryparam null="true" cfsqltype="cf_sql_varchar" value="">
</cfquery>

Note that this DOES work as expected:

<cfquery name="foo" datasource="cfartgallery">
  select * from artists where fax IS NULL
</cfquery>

Also note, that while this does not throw an exception it does NOT produce the desired results:

<cfquery name="foo" datasource="cfartgallery">
  select * from artists where fax IS <cfqueryparam null="true" cfsqltype="cf_sql_varchar" value="">
</cfquery>

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3684235

Deployment Phase:	Release Candidate

External Customer Info:
External Company:  
External Customer Name: nathandintenfas s
External Customer Email:  
External Test Config: Vanilla install of CF10 on OSX 10.8.5 was the initial environment that produced this error. The cfartgallery dsn referenced is the one installed by CF.

Attachments:

Comments:

Although one could theoretically work around this by not using CFQUERYPARAM that violates Adobe best practices and opens up potential security issues.
Comment by External U.
13834 | December 13, 2013 10:57:59 AM GMT
NOTE: that last example that doesn't throw an error should have been: <cfquery name="foo" datasource="cfartgallery"> select * from artists where fax = cast(<cfqueryparam null="true" cfsqltype="cf_sql_varchar" value=""> as varchar(12)) </cfquery>
Comment by External U.
13835 | December 13, 2013 10:59:58 AM GMT
Looks like a known derby issue. https://issues.apache.org/jira/browse/DERBY-5629 IS NULL will work but = NULL will not work. Basically with NULL value you cannot use "=". You have to use "IS" and "IS" does not allow parametererized values. So the moment it sees "?" it throws the error. This is even before we set the parameters.
Comment by Himavanth R.
13836 | December 23, 2013 06:46:26 AM GMT