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.