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

 

[r937]

any ideas on how to do what? insert a null?

i might just use the cfqueryparam null="yes" option

r937.com | rudy.ca

 

[ecobb]

null="yes" basically tells cfqueryparam to insert a NULL instead of a value, regardless if one was passed or not. The way that you originally had your query coded is probably the best way to do what you're asking.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[GUJUm0deL]

Rudy, what I wanted to was to insert a NULL only if no value was passed. If a value was passed, then insert that value.

ECAR, does that logic make sense? Logically a "null" field means the form field is optional. Either it can have a value or it cannot have a value. What scenario would always want a NULL value to be passed? If your always passing a NULL value, why even have that field on the page? Why even have that columnname defined in the dB table?

I really wanted to avoid doing:

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

I guess that's the only solution...


____________________________________
Just Imagine.

http://www.imajinarts.com

 

[ecobb]

ECAR, does that logic make sense?

No, not really. When I first discovered it I had the same thought process on it that you do. But, like you, every time I tried it I got a NULL regardless. I never really understood why it acted like that, but the only think I could figure was that it was an implicit instruction to insert a NULL instead of a value.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[r937]

... it was an implicit instruction to insert a NULL instead of a value

actually it was pretty explicit on that livedocs page in the first post

r937.com | rudy.ca

 

[GUJUm0deL]

The livedocs say this about the NULL attribute:

Whether parameter is passed as a null value.
  Yes: tag ignores the value attribute
  No

When I saw that I looked odd, but then again i've seen the livedocs make many errors in their documentation, and thought this might be one of them. Didn't thik much until I tried it, :-(

Any idea if CF8 will fix this? Or, the very least, allow passing of an optional value?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

So I dug a little deeper into this issue. I figured if we noticed that the null="yes" logic is seriously flawed, so must have others. And, maybe someone else found a clever way of passing the value of an optional field.

Lo and behold:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.Address2#" maxlength="100" null="#IIF(FORM.Address2 EQ "", true, false)#">

I implemented this and it works, with desired results.

Does anyone think using method is a bad idea? Will using #IIF(FORM.Address2 EQ "", true, false)# cause any performance issues down the line?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[ecobb]

actually it was pretty explicit on that livedocs page in the first post

Instructions???? We don't need no stinking instructions!! That's like having to stop and ask for directions!

Good tip, GUJUm0deL! Thanks for sharing.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[cfStarlight]

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

I've seen a lot of frameworks/cfc tools use similar logic. Except they use something like #len(trim(FORM.Address2))# instead of IIF.

 

[GUJUm0deL]

i'm not sure that would work. The whole reason we need to use the IIF function is becuase the inception of the null attribute wasn't well thought out.

I don't know if doing something like

<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.Address2#" maxlength="100" null="#len(trim(FORM.Address2))#">

Would let CF know to insert #FORM.Address2# if there is a value and insert NULL if there is no value.

My only concern was that there was one 'best practices' guide somewhere (I think it was easycfm.com) that said to use <cfif></cfif> over IIF cause IIF was slower. I just wanted to know if other CF guru's thought the same.



____________________________________
Just Imagine.

http://www.imajinarts.com

 

[cfStarlight]

Sure it works. (If it didn't I can think of a lot of applications that would be blowing up right about now ;-)

A length of 0 evaluates to a boolean false. A length greater than zero evaluates to true.

<cfset form.address2 = " ">
<cfif len(trim(FORM.Address2))>
TRUE
<cfelse>
FALSE
</cfif>

 

[GUJUm0deL]

cfStarlight, that approach seems similar to what I had been doing all along (see my 1st post). I wanted to avoid doing that becuase if I have, say, 25 nullable fields, then that's a lot of extra coding i'd have to do.

The conception of the null attribute was a great idea, just the implementation was flawed.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

i don't understand what's flawed about a yes/no variable

if it says yes, pass in a null

if is says no, pass in the value

maybe it's just me?



or maybe it's because i can visualize what types of queries (e.g. INSERT) would benefit from having a NULL substituted whenever no value exists to be passed in

for example, don't try to pass in a zero-length string if the column is numeric

and where customizing the query column by column to omit any mention of a column for which no value has been passed is needlessly complex

go on, admit it, you guys have all written SQL where you decided column by column which columns to include... optional form fields, anyone?



r937.com | rudy.ca

 

[ecobb]

Um...am I missing something? cfStarlight, I think your logic is backwards. If it's false it equals "No"...so every time a null value is passed, it would have a Len(Trim()) of "NO", meaning that you would get the exact opposite effect on cfqueryparam than what is desired.

To clarify, if the value is blank, this would tell cfqueryparam NOT to use the null, but if there was a value passed it would tell cfqueryparam that it IS NULL. In this particular case, we need the value to equal YES if it's false, and NO if it's true.

This is what Len(Trim()) would give us, which is backwards:

<cfset form.address2 = "   ">
<cfif len(trim(FORM.Address2))>
    TRUE (<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.Address2#" maxlength="100" [red]Null="YES"[/red]>)
<cfelse>
    FALSE (<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.Address2#" maxlength="100" [red]Null="NO"[/red]>)
</cfif>

Run this and it should show you what I'm talking about:

<cfset form.address2 = "   ">
<cfoutput>
	<cfif len(trim(FORM.Address2))>
	    TRUE value passed, NULL is: #YesNoFormat(len(trim(FORM.Address2)))#
	<cfelse>
	    FALSE (blank) value passed, NULL is: #YesNoFormat(len(trim(FORM.Address2)))#
	</cfif>
</cfoutput>

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[cfStarlight]

GUJUm0deL,

I agree with r937. I don't see anything flawed about a boolean attribute.

I'm not suggesting you use CFIF. The CFIF example was just to demonstrate that LEN() can be evaluated to a boolean. See the examples below. CF is able to evaluate the numeric values as booleans (0 - false and 2 becomes true). So the YesNoFormat() isn't necessary and is only included for readability.

<cfset form.address2 = "   ">
Is Null: <cfoutput>#len(trim(FORM.Address2))#</cfoutput>
<cfset form.address2 = "   ab   ">
Is NOT Null: <cfoutput>#len(trim(FORM.Address2))#</cfoutput>
<hr>
<cfset form.address2 = "   ">
Is Null: <cfoutput>#YesNoFormat(len(trim(FORM.Address2)))#</cfoutput>
<cfset form.address2 = "   ab   ">
Is NOT Null: <cfoutput>#YesNoFormat(len(trim(FORM.Address2)))#</cfoutput>

ECAR,

You're absolutely correct. I didn't have an example handy. That's why I said "something like" in my original response ;-) The correct code is:

null="#NOT len(trim(form.address2))#"

 

[GUJUm0deL]

if it says yes, pass in a null

if is says no, pass in the value

What about instances where the field is nullable in the dB, the user does not enter a value and in the INSERT statement konks out because it is expecting a value?

What many have noted is this:
null="yes" - CF will pass null regarldess if a value was submitted or not.

null="no" - CF is expecting a value (regardless if the field is required or not).

How does one get around this scenario?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

get around? i don't understand the question

if the field is nullable, and the user doesn't enter a value in the form field, pass a null

that would seem to be desired behaviour, not something to "get around"

how can the INSERT "konk out" because it is expecting a value? why would it be expecting a value for a nullable field?


r937.com | rudy.ca

 

[GUJUm0deL]

if the field is nullable, and the user doesn't enter a value in the form field, pass a null

that would seem to be desired behaviour, not something to "get around"

The problem is when the user DOES enter a value for a optional field, CF still inserts a NULL value and not the vlaue being passed (as in the users' form selection).

As ECAR put it very simply a few posts above:

null="yes" basically tells cfqueryparam to insert a NULL instead of a value, regardless if one was passed or not.

Also,

why would it be expecting a value for a nullable field?

It would be expecting a value if the form field was optional and the user had a choice in submitting an anser or not.

If I'm on form, and was asked my age (and the age was an optional field), if I chose to answer it and made a selection then that value would be passed. If I chose to not answer then the NULL value would be passed.

Maybe i'm not explaining this right...

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[cfStarlight]

What about instances where the field is nullable in the dB, the user does not enter a value and in the INSERT statement konks out because it is expecting a value?

The statement won't throw an error because the db will recieve a value: NULL.

The problem is when the user DOES enter a value for a optional field, CF still inserts a NULL value and not the vlaue being passed (as in the users' form selection).

No. I think you may be misunderstanding how the logic works. If the user does enter a value for an optional field, then that value is passed to the datbase because the null attribute will = "no". If they do NOT enter a value (ie the field is "") then NULL is passed to the database (because the null attribute will = "yes".