Ok in the quest for looking for better ways to write my code I ran into a small
gotcha today that had me banging my head against the wall. Here is the original
code:
<cftry>
<cfquery name="createField" datasource="#DSN#">
INSERT INTO t_field(
structure_id,
source_ref_1,
field_fmt_type_id,
last_chg_user_id,
last_chg_dt
)
VALUES(
<cfqueryparam cfsqltype="cf_sql_integer"
value="#ARGUMENTS.StructureID#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#ARGUMENTS.SourceRef1#" null="#Len(Trim(ARGUMENTS.SourceRef1))#"/>,
<cfqueryparam cfsqltype="cf_sql_integer"
value="#ARGUMENTS.FieldFmtTypeID#"
null="#Len(Trim(ARGUMENTS.FieldFmtTypeID))#"/>,
<cfqueryparam cfsqltype="cf_sql_integer"
value="#ARGUMENTS.LastChangeUserID#"/>,
#CreateODBCDate(Now())#
);
SELECT SCOPE_IDENTITY() AS newid;
</cfquery>
<cfcatch type="database">
<cfreturn false/>
</cfcatch>
</cftry>
I am sure most of you know what I am attempting to do, but for those that don't
I'll explain. Instead of writing a bunch of CFIFs to determine if the value
passed to the query is an empty string and omitting parts of the SQL query and
allowing MSSQL to insert NULLs, I've opted to use the NULL attribute of the
CFQueryparam tag to do that work for me. I've been using
null="#Len(Trim(Arguments.Value))#" with much success and happiness until
today. When ColdFusion attempted to run this code I got a nice fat error:
Invalid data '' for CFSQLTYPE CF_SQL_INTEGER
I was like WTF? Ummm hello McFly if I passed you an empty string that's what
the whole null="#Len(Trim(value))#" was there for... hello. So I did some
digging around and it turns out I was in fact as Fred G Sanford would say "You
Big Dummy!". After reading a blog post by Ben Nadel
http://www.bennadel.com/blog/1092-ColdFusion-CFQueryParam-Binding-vs-SQL-Execution.htm
turns out that if you are using CF_SQL_INTEGER type it's looking for an
integer value period. Pass it an empty string if you wish and it will fail. So
a quick re-write to the code below gave me the results I was expecting in the
first place.
<cftry>
<cfquery name="createField" datasource="#DSN#">
INSERT INTO t_field(
structure_id,
source_ref_1,
field_fmt_type_id,
last_chg_user_id,
last_chg_dt
)
VALUES(
<cfqueryparam cfsqltype="cf_sql_integer"
value="#ARGUMENTS.StructureID#"/>,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#ARGUMENTS.SourceRef1#" null="#Len(Trim(ARGUMENTS.SourceRef1))#"/>,
<cfqueryparam cfsqltype="cf_sql_integer"
value="#ARGUMENTS.FieldFmtTypeID#" null="#NOT
isNumeric(ARGUMENTS.FieldFmtTypeID)#"/>,
<cfqueryparam cfsqltype="cf_sql_integer"
value="#ARGUMENTS.LastChangeUserID#"/>,
#CreateODBCDate(Now())#
);
SELECT SCOPE_IDENTITY() AS newid;
</cfquery>
<cfcatch type="database">
<cfreturn false/>
</cfcatch>
</cftry>
Using the null="#NOT isNumeric(ARGUMENTS.value)# allows for the proper insert
of a NULL when an empty string is passed to the query.
Cheers
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328313
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
