CFQueryparam Oh now I <3/ hate you o_O

[Brett Davis-2]

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=17837.14401.4

[cfSearching]

>             <cfqueryparam
> cfsqltype="cf_sql_integer"
> value="#ARGUMENTS.FieldFmtTypeID#"
> null="#Len(Trim(ARGUMENTS.FieldFmtTypeID))#"/>,

Do you not mean:  

.... null="#NOT Len(Trim(ARGUMENTS.FieldFmtTypeID))#"/>

ie Insert null when the value IS an empty string

-Leigh







~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:328318
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Fisher-3]

I do a similar check with #not isDate(arguments.startDate)# with
cf_sql_date types as well.  Handy little trick being able to drop the
NOT in there like that.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:328328
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[cfSearching]

> Handy little trick being
> able to drop the  NOT in there like that.

.... though if you forget it, cfqueryparam does NOT work as expected ;)


     


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:328333
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Azadi Saryev]

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=17837.14401.4