Different cfqueryparam behavior between acf and lucee


lucee allows <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="">
acf crashes <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="">


But only in QoQ?


It happens with sql server as well

and in the sql debug, it shows up somewhat confusingly as

where id = [CF_SQL_INTEGER]


where id = [CF_SQL_NUMERIC]

when I’m assuming it’s actually passing in null?

and if i change the datatype to VARCHAR,

<cfqueryparam cfsqltype="varchar" value="">

it throws Error converting data type nvarchar to numeric.


Lucee handles null a whole lot better than ACF and, to my mind, Lucee has this right. I’d suggest filing a bug for ACF at https://tracker.adobe.com/


That’s JDBC semantics (to have “typed” nulls) in a parameter replacement, as it usually would get turned into

DECLARE @p0 int=null
SELECT * from table where id=@p0

(Slight oversimplification to remove the sp_prepare and execute and unprepare bits)

Proper way to do this in ACF IMHO would be

<cfqueryparam cfsqltype="cf_sql_integer" value="#Trim(v)#" null="#Not Len(Trim(v))#" />

where v is your incoming variable. That’s what we do all over our codebase for inserts, or updates where you’re adding or changing a value to a null.

With an update we’d wrap that in a <cfif StructKeyExists(arguments,"v")> for conditional updating semantics.

But I think in your case you need to revisit your strategy. SQL would error if you gave it the query SELECT * FROM sometable WHERE id='', that CF does also doesn’t seem to be a CF problem.

And what are you trying to accomplish? WHERE id='' is not valid, nor is WHERE ID=NULL - you’d need to change your query to do IS NULL if that’s truly what you want.

   <cfif not Len(Trim(v))> id IS NULL <cfelse><cfqueryparam cfsqltype="cf_sql_integer" value="#Trim(v)#" /></cfif>

IMHO that’s the programmers responsibility, just as it would be to make sure v is actually a number, instead of something like “J”. Realizing that CF believes something like “1e1” is a number, while SQL doesn’t (in MSSQL you’d have to pass it as a float to parse, not an integer or numeric()) may mean regex or Len(spanincluding(trim(v),"0123456789")) EQ len(trim(v)) validation.

My examples are based on MSSQL semantics - for QoQ you’d have to use H2 semantics.


thanks @joe.gooch, I’ve been doing that approach for many years!
@ddspringle adobe aren’t going to change the behaviour.

It was more documenting the different between the two engines.

For Lucee, the sql debug needs updating to show nulls instead of the datatype
and the documentation needs to be updated

Is this the only official differences guide between acf and lucee at the moment?

there is a much more substantial list here


@Zac_Spitzer I think based on what you’re saying Lucee is the less preferred approach.

I agree that if you’re using a cfqueryparam to populate a value you’re inserting or updating, it might be slightly more convenient for “” to be the same as NULL. But we have the null attribute for that purpose and it’s relatively easy to calculate, or to specify separately.

BUT if you’re using cfqueryparam in a WHERE clause, like you are, and it silently transforms to null, the query results will be invalid and there won’t be a clear indication why. In that case the ACF behavior of throwing an exception is exactly what I would want to happen.

My preference is for the ACF behavior - because it gives you a sane default with the options to do what you need it to do. In Lucee, the default could cause invalid query results, silently, and you’d have no way to tell queryparam to throw an exception instead, so now you NEED to do length checks on all your queryparams before running the query, and as you indicated, the debugging output doesn’t even help.

The appearance of things like IsNull() in CFML reinforces that in todays programming world, Null != “”, and I think this behavior is a step backwards.

Just my $.02


I agree , the acf behaviour is better, that said having <cfqueryparam value=“v” type=“integer” null=“auto”>,
which would be the same as null="#Not Len(Trim(v))#" would be nice