cfqueryparam and its null attribute 1

[GUJUm0deL]

Hey all, when I INSERT INTO or UPDATE a sql statement, I use <cfqueryparam> tag. If the field allows null value (like field is optional), I use to do this:

<cfif isdefined("FORM.Address2")>
	<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.Address2#" maxlength="50">
<cfelse>
	NULL	
</cfif>

That is until I looked at the cf doc on Adobe (

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm#wp1102474)

and saw that <cfqueryparam> tag has a null attribute. So I tried using that last night, and the result wasn't what I had hoped. I thought that null="yes" meant if the field would be null if no value is passed, but if some value is passed then that value would be inserted into the dB. That is not what I encountered. Consistantly whereever I used:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.Address2#" maxlength="50" null="yes">

the value entered in the dB was null. I am using MS SQL 2000.

Any ideas on how to do this?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[cfStarlight]

Here's a sql server example that produces the following results:

query
ADDRESS1 ADDRESS2 ID
1 86 Sea Street [empty string] 1
2 291 Sea Street Suite 214 2

Notice the "Address2" value is NOT null in the second row, because something was entered in form.address2.

<cfquery name="addTable" datasource="#dsn#">
	CREATE TABLE YourTable (
	ID int identity(1,1),
	Address1 varchar(255),
	Address2 varchar(255)
	)
</cfquery>

<cfset form.address1 = "86 Sea Street">
<cfset form.address2 = "     ">
<cfquery name="insertAddress" datasource="#dsn#">
	INSERT INTO YourTable (Address1, Address2)
	VALUES 
	(
		<cfqueryparam value="#form.address1#" cfsqltype="cf_sql_varchar" null="#NOT len(trim(form.address1))#">,
		<cfqueryparam value="#form.address2#" cfsqltype="cf_sql_varchar" null="#NOT len(trim(form.address2))#">
	)
</cfquery>

<cfset form.address1 = "291 Sea Street">
<cfset form.address2 = "Suite 214">
<cfquery name="insertAddress" datasource="#dsn#">
	INSERT INTO YourTable (Address1, Address2)
	VALUES 
	(
		<cfqueryparam value="#form.address1#" cfsqltype="cf_sql_varchar" null="#NOT len(trim(form.address1))#">,
		<cfqueryparam value="#form.address2#" cfsqltype="cf_sql_varchar" null="#NOT len(trim(form.address2))#">
	)
</cfquery>

<cfquery name="getAddresses" datasource="#dsn#">
	SELECT 	* FROM YourTable
</cfquery>

<cfdump var="#getAddresses#">

 

[GUJUm0deL]

<cfqueryparam value="#form.address1#" cfsqltype="cf_sql_varchar" null="#NOT len(trim(form.address1))#">

Would be a lot better if we could do this for an optinal field:

<cfqueryparam value="#form.address1#" cfsqltype="cf_sql_varchar" null="optional">

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[cfStarlight]

Personally, I think something like NULLIF might be a more descriptive or closer to the mark ;-)

But I don't think its that simple, because you have to define what optional means. For cf_sql_integer values, does "optional" mean when the value is an empty string , when its 0, -1, or some other condition?

I suppose they could define some constants for NULLIF_EMPTY_STRING, NULLIF_ZERO, ... but that seems a bit limiting. This code acceptible to me because I can define exactly what "optional" means.

<cfqueryparam value="#form.address1#" cfsqltype="cf_sql_varchar" null="#NOT len(trim(form.address1))#">

 

[cfStarlight]

#IIF(FORM.Address2 EQ "", true, false)#

Btw, whichever method you choose you should always TRIM() the values so you're not counting all whitespace as a valid entry.