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]
or
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.
WHERE
<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.
2 Likes
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
@Zackster 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
1 Like
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
1 Like
Hi all,
I ran into this issue recently and would like to revive this conversation, because in my opinion this behaviour needs to change. I spent a lot of time debugging this issue.
I have never tried running this in ACF as we use Lucee, and I totally didnât expect that <cfqueryparam> will silently turn empty value into NULL!
I am working with a codebase not written by me, and I agree there are approaches for checking the value before using it more thoroughly, but this behaviour is not documented and is counterintuitive. I would expect the tag to either throw an error or convert ââ into a 0 in case of numeric cfsqltype.
<cfquery result="test">
INSERT INTO AdminUser (active)
VALUES (<cfqueryparam cfsqltype="cf_sql_bit" value="">);
</cfquery>
<cfdump var="#test#">
This code inserts NULL into the DB. I donât think this is the correct behaviour.
Should I file a bug?
Marianna
I think you should file a bug Marianna. An empty string is not a valid value for a bit, integer so it should throw an exception. You should also note in the bug report that it is incompatible with ACF, even though that doesnât matter in your case, it should give the issue a higher priority.
3 Likes
Which database are you working with?
I am using Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Zac, I found a bug that you submitted in 2017 thatâs I think is the same issue, which is still in the backlog. I added my comment.
https://luceeserver.atlassian.net/browse/LDEV-1532?jql=text%20~%20"cfqueryparam"%20order%20by%20created%20DESC
Weâve backed out the fix as it caused other problems in 6.0
https://luceeserver.atlassian.net/browse/LDEV-1532
As we are looking at 6.0, Iâm open to the idea of matching ACF behaviour, i.e. throwing an error
CFâs behaviour is correct IMO. As @MariannaAtPlay & @pfreitag allude to: an empty string is not a valid value for a numeric type. And an empty string is also not any approximation / equivalent of null.
If you give a string (even an empty one) when a numeric value is required, one should get a runtime error, and enable the dev to fix their code. Not silently second-guess what they actually mean and apply magic special behaviour instead, which has the sole effect of hiding a problem situation.
7 Likes
For what itâs worth, I agree with @AdamCameron, @MariannaAtPlay & @pfreitag.
ok then , I have created a new ticket to throw an exception instead of casting to null
this will be a breaking change (for the better) for Lucee 6.0
4 Likes
Cheers for taking the time to ask about this, and then listening, @Zackster.
4 Likes
ok, played around a few approaches, this one seems the best, should actually be faster for everyone as it removes a try catch around most query parameter handling
2 Likes
this change has been implemented in 6.0.0.383