Error passing in NULL on cfparam list="yes"

dev support
1

If a null value is passed into Params on queryExecute( ) an error is generated.
Error message: Index: 0, Size: 0

Example code - sanitized due to a NDA on the project. The null should override the list but does not.

public void function testSQL() {
	var qry = {};
	qry['Options'] = { 'datasource':variables.DSN, 'result':'out' };
	qry['Params']  = {
		'types': { cfsqltype="CF_SQL_VARCHAR", list="yes", value="", null=true }
	};
	qry['SQL'] = "
		SELECT
			  type
			, count(type) AS counts
		FROM sales
		WHERE 1 = 1
			AND ( :types IS NOT NULL AND type IN (:types) )
		GROUP BY type
	";

	var qData = queryExecute( qry.SQL, qry.Params, qry.Options );
}
2

Wow some things are just disappointing. It seems this bug has existed since 2014. Here is the railo bug report.

https://issues.jboss.org/browse/RAILO-3195?_sscc=t

Was this bug moved over to the lucee tracking system? Is there another way of handling this input that I don’t know about?

I believe my syntax works in ACF.

3

I also find these in the lucee bug tracking system. Oh and I am running Lucee: 5.2.1.9 on windows 10 with Jetty 9.4.3

https://luceeserver.atlassian.net/browse/LDEV-225
https://luceeserver.atlassian.net/browse/LDEV-224

4

Well it appears that my syntax was wrong after all. I forgot the separator=“,”. The returned error message could be much better but at least it works.

I did notice that it is impossible to pass in a NULL value when using list=true. Or at least I was never able to make it happen.

5

A possible workaround:

var params = {
	"types": { sqltype="VARCHAR", list=!IsEmpty( value ), value="#value#", null=IsEmpty( value ) }
};
3 Likes
6

Thanks for your workaround @Julian_Halliwell. I created an issue for this:

https://luceeserver.atlassian.net/browse/LDEV-1699