null=#Len(Trim(ARGUMENTS.SourceRef1))#
that will evaluate to null=true when ARGUMENTS.SourceRef1 IS an empty string.
as Leigh said, you should have used null=#NOT Len(Trim(ARGUMENTS.SourceRef1))#
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
On 13/11/2009 01:58, Brett Davis wrote:
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:328334
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
