Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
<cfqueryparam> question ("null" attribute)
Author: DURETTE, STEVEN J (ATTASIAIT)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#335117
Why not define form.myDate in the first place.
<cfparam name="form.myDate" default="" />
Then...
<cfqueryparam value="#form.myDate#" null="#not len(trim(form.myDate))#"
/>
Steve
Hello Everyone (again):
Another form of cfqueryparam has come up and I was curious how your
outcomes might pan out.
The following cfqueryparam passes, BUT when NULL it passes the default
date in SQL Server (01/01/1900) Also, I had to take out the cfsqltype,
it would error out if I did.
<cfqueryparam value="#isDefined('form.myDate')?form.myDate:''#"
null="#!isDefined('form.myDate')#" />
So, if I leave the date field blank it enters in this date (01/01/1900)
into the db, if I put in a date then its fine. This code works (and I'm
currently using):
<cfif isDefined("form.myDate") AND #form.myDate# NEQ "">
<cfqueryparam value="#form.myDate#" cfsqltype="cf_sql_timestamp">
<cfelse>
<cfqueryparam null="yes">
</cfif>
I can keep it this way of course, but I really do like this new format
and would like to see if it's possible to use this.
Here's my theory, a space is inserted when the field is left blank when
I should be passing a NULL value for dates... It's another shot in the
dark, so I tried this format:
<cfqueryparam value="#isDefined('form.myDate')?form.myDate:'NULL'#"
null="#!isDefined('form.myDate')#" />
Simply added in NULL for the else, but that was a no go. Again, it
passed through the submitting of the form, but the date still showed up
as the default 01/01/1900 date. So far I've been able to figure out
integers and vars but here's a new challenge of the date/time. Thanks
everyone in advance!
Author: Matthew Lowrey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#335115
Hello Everyone (again):
Another form of cfqueryparam has come up and I was curious how your outcomes
might pan out.
The following cfqueryparam passes, BUT when NULL it passes the default date in
SQL Server (01/01/1900) Also, I had to take out the cfsqltype, it would error out
if I did.
<cfqueryparam value="#isDefined('form.myDate')?form.myDate:''#"
null="#!isDefined('form.myDate')#" />
So, if I leave the date field blank it enters in this date (01/01/1900) into the
db, if I put in a date then its fine. This code works (and I'm currently using):
<cfif isDefined("form.myDate") AND #form.myDate# NEQ "">
<cfqueryparam value="#form.myDate#" cfsqltype="cf_sql_timestamp">
<cfelse>
<cfqueryparam null="yes">
</cfif>
I can keep it this way of course, but I really do like this new format and would
like to see if it's possible to use this.
Here's my theory, a space is inserted when the field is left blank when I should
be passing a NULL value for dates... It's another shot in the dark, so I tried
this format:
<cfqueryparam value="#isDefined('form.myDate')?form.myDate:'NULL'#"
null="#!isDefined('form.myDate')#" />
Simply added in NULL for the else, but that was a no go. Again, it passed
through the submitting of the form, but the date still showed up as the default
01/01/1900 date. So far I've been able to figure out integers and vars but
here's a new challenge of the date/time. Thanks everyone in advance!
Author: Sean Corfield
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333339
> Sean - Not to get off this subject, but I noticed your signature and
realized my boss and myself were on your site (getrailo.com) reviewing it because
we have been looking into trying to get some kind of internal wiki for mainly
having search-able documentation to share/keep track of for our IT dept,
developers, administration, etc. Just thought it was pretty ironic I had to
laugh. It looks like something we'll want to try out and use.
The main sites (.com and .org) are powered by Mura. The wiki is
CodexWiki. All running on Railo (of course). Let me know if you need
more information.
--
Sean A Corfield -- (904) 302-SEAN
Railo Technologies, Inc. -- http://getrailo.com/
An Architect's View -- http://corfield.org/
"If you're not annoying somebody, you're not really alive."
-- Margaret
Atwoo
Author: Matthew Lowrey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333338
Thanks again everyone for your feedback.
Sean - Not to get off this subject, but I noticed your signature and realized my
boss and myself were on your site (getrailo.com) reviewing it because we have
been looking into trying to get some kind of internal wiki for mainly having
search-able documentation to share/keep track of for our IT dept, developers,
administration, etc. Just thought it was pretty ironic I had to laugh. It looks
like something we'll want to try out and use.
Matt...
----- Excess quoted text cut - see Original Post for more -----
Author: Sean Corfield
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333330
> You can nest ternary operators, I believe, but it is going to start
> getting pretty unreadable quickly. If you have more logic to run, I'd
> suggest pulling it up out of the sql statement, figuring out the final
> result, then using that result in the conditional in your cfparam.
I agree. Embedding that much logic in the middle of your
<cfqueryparam> tags is going to create a maintenance nightmare.
Consider preprocessing your form scope to clean up the fields so that
the ?: condition can be nice and simple.
--
Sean A Corfield -- (904) 302-SEAN
Railo Technologies, Inc. -- http://getrailo.com/
An Architect's View -- http://corfield.org/
"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood
Author: William Seiter
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333329
You can do nested iif, but not a full cfif/cfelseif/cfelse
Sean, I'm pretty sure I already know this answer, but now that you've brought
this new way of thinking to my attention. I'm going to place my cards on the
table here and ask the million dollar question. Is it possible to have a
cfelseif in this param? :-D I know... I have a pair of 4's... not likely to beat
your cards haha
Matt...
----- Excess quoted text cut - see Original Post for more -----
null="yes"></c
Author: Judah McAuley
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333328
You can nest ternary operators, I believe, but it is going to start
getting pretty unreadable quickly. If you have more logic to run, I'd
suggest pulling it up out of the sql statement, figuring out the final
result, then using that result in the conditional in your cfparam.
Cheers,
Judah
----- Excess quoted text cut - see Original Post for more -----
Author: Matthew Lowrey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333326
Sean, I'm pretty sure I already know this answer, but now that you've brought
this new way of thinking to my attention. I'm going to place my cards on the
table here and ask the million dollar question. Is it possible to have a
cfelseif in this param? :-D I know... I have a pair of 4's... not likely to beat
your cards haha
Matt...
----- Excess quoted text cut - see Original Post for more -----
null="yes"></c
Author: Matthew Lowrey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333306
YA!!!!!!!!!!!!!!!!!!!!!!!! SEAN WINS THE PRIZE!! Thank you Mr. Corfield. This
worked beautifully and better yet I didn't have to set anything or cfif anything,
just one tag with everything all inside of it. Thanks a million x yourVal
Matt...
----- Excess quoted text cut - see Original Post for more -----
null="yes"></c
Author: Sean Corfield
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333301
Since you're on CF9:
<cfqueryparam value="#isDefined('form.myVar')?form.myVar:''#"
null="#!isDefined('form.myVar')#" />
The (new in CF9) ?: operator only evaluates the true-expr if the
condition is true.
> This is getting to be quite an interesting challenge. All the examples I've
seen logically make sense but none of them have worked so far. I will say I'm
learning quite a bit from everyone's knowledge/wisdom. I will try and list here
all the different methods I've tried out and have failed. First, the method I've
used (and still using):
>
> <cfif isDefined("form.myVal")><cfqueryparam
cfsqltype="cf_sql_varchar" value="#form.myVal#"
maxlength="50"><cfelse><cfqueryparam
null="yes"></c
Author: Jason Fisher
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333296
CFParam, unless you want to go the multiline route:
<cfif structKeyExists(form, "myVar")>
<cfqueryparam type="cf_sql_integer" value="#form.myVar#" />
<cfelse>
<cfqueryparam type="cf_sql_integer" value="" isNull="Yes" />
</cfif>
But you can't go <cfqueryparam type="cf_sql_integer" value="#form.myVar#"
isNull="#NOT
isNumeric(form.myVar)#" />
because value="#form.myVar#" will fail to compile if form.myVar doesn't
exist.
> I think the point is that if you're going to use any of those
techniques,
> you DO have to param the variable, or use some other mechanism to make
sure
> the variable exists. Otherwise the code in your "value" attribute will
> always fail when the variable doesn't exist. Make sense?
Yes. This is the part that, in my initial reading, I didn't even see.
This doesn't have anything to do with CFQUERYPARAM per se, it's just
that you can't reference a variable that doesn't exist at all.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/
Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite.
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333295
> I think the point is that if you're going to use any of those techniques,
> you DO have to param the variable, or use some other mechanism to make sure
> the variable exists. Otherwise the code in your "value" attribute will
> always fail when the variable doesn't exist. Make sense?
Yes. This is the part that, in my initial reading, I didn't even see.
This doesn't have anything to do with CFQUERYPARAM per se, it's just
that you can't reference a variable that doesn't exist at all.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/
Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite.
Author: Brian Kotek
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333294
I think the point is that if you're going to use any of those techniques,
you DO have to param the variable, or use some other mechanism to make sure
the variable exists. Otherwise the code in your "value" attribute will
always fail when the variable doesn't exist. Make sense?
----- Excess quoted text cut - see Original Post for more -----
Author: Matthew Lowrey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333293
This is getting to be quite an interesting challenge. All the examples I've seen
logically make sense but none of them have worked so far. I will say I'm
learning quite a bit from everyone's knowledge/wisdom. I will try and list here
all the different methods I've tried out and have failed. First, the method I've
used (and still using):
<cfif isDefined("form.myVal")><cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.myVal#" maxlength="50"><cfelse><cfqueryparam
null="yes"></cfif>
Examples that have not worked (unless I cfparam the variable):
<cfqueryparam value="#form.myVar#" null='#not IsDefined("form.myVar")#'>
<cfqueryparam value="#form.myVar#" null="#not IsDefined('form.myVar')#">
<cfqueryparam value="#form.myVar#" null="#not IsDefined('form.myVar')#">
<cfqueryparam value="#form.myVar#" null="#not Len(Trim(form.myVar))#" />
<cfqueryparam type="cf_sql_varchar" value="#form.myVar#" isNull="#NOT
isDefined(form.myVar)#" /> (actually got a different error on this one and
couldn't figure it out for some reason Attribute validation error for tag
CFQUERYPARAM. Usually it's because I'm passing the wrong cf_sql_type, but I took
that out and it still gave me the error)
<cfqueryparam type="cf_sql_varchar" value="#form.myVar#"
isNull="#yesNoFormat(NOT isDefined(form.myVar)#" />
<cfqueryparam type="cf_sql_varchar" value="#form.myVar#"
isNull="#YesNoFormat(NOT Len(Trim(form.myVar)))#" />
<cfqueryparam type="cf_sql_varchar" value="#form.myVar#"
isNull="#yesNoFormat(not structKeyExists(form, 'myVar'))#" />
<cfqueryparam type="cf_sql_varchar" value="#form.myVar#"
isNull="#yesNoFormat(not len(trim(form.myVar)))#" />
I went ahead and re-tried all these examples above. Most (if not all) these
examples should work logically. Also, for the record I'm currently using:
Coldfusion 9
SQL Server 2005
Thank again for everybody's help and wisdom!
Best Regards
Matt..
----- Excess quoted text cut - see Original Post for more -----
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333290
> what Dominic meant is that value="#somevalue#" in cfqueryparam tag
> cause the problem when somevalue var does not exist, not the
> isDefined("somevalue") part.
Oh, well, yes - that would cause a problem! I didn't even think about that part.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/
Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333289
what Dominic meant is that value="#somevalue#" in cfqueryparam tag
cause the problem when somevalue var does not exist, not the
isDefined("somevalue") part.
Azadi
On 02/05/2010 06:12, Dave Watts wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333288
> I believe the trouble to be this:
>
> <cfqueryparam value="#someValue#" null="#not IsDefined('someValue')#"
/>
>
> Works fine when the value is defined. However, if the value is not defined,
> value="#someValue#" will throw an error - ColdFusion evaluating it
> before/regardless of the value of the null attribute.
But that's not how isDefined works. If you pass isDefined a string
literal, it tests to see if there is a variable whose name matches the
string literal. So, this will work:
isDefined("thisVariableDoesNotExist")
and (assuming the variable doesn't exist) it will return false. But,
if you did this:
isDefined(thisVariableDoesNotExist)
it would first evaluate the expression thisVariableDoesNotExist, and
that would cause an error (again, assuming the variable doesn't
exist).
In the posted code sample, there are single quotes around someValue,
making it a string literal.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/
Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or
onsite.
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333285
I believe the trouble to be this:
<cfqueryparam value="#someValue#" null="#not IsDefined('someValue')#" />
Works fine when the value is defined. However, if the value is not defined,
value="#someValue#" will throw an error - ColdFusion evaluating it
before/regardless of the value of the null attribute.
Personally, I find cfparam tidier than checking existence of variables
throughout a script. Variables can all be defaulted at the top of the script
with a single clear line per variable. Not always appropriate of course, but
still - just my preference.
Upshot, if you want a tidy cfqueryparam, you will need to guarantee the
existance of 'someValue' and then do something like:
<cfqueryparam value="#someValue#" null="#not Len(Trim(someValue))#" />
HTH
Dominic
On 30 April 2010 23:00, Ian Skinner <hof@ilsweb.com> wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Ian Skinner
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333284
All ColdFusion expressions and functions will throw an undefined error,
if you reference a variable that is, you know, undefined even
expressions and functions used inside of the "null" property of the
<cfqueryparam...> tag.
If you expect that the variable is going to be undefined, as some form
variables may very well be, you need to account for this in your logic.
You can either use the <cfparam...> tag to make sure they are never
undefiend, or you can use the structKeyExists() or the less prefered
isDefined() functions to test if they exist.
For the latter options you may need to write a more complex expression
for the null property that makes uses of some type of AND|OR operators.
Author: Matthew Lowrey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333283
Thanks Dave & Tony. I must be doing something wrong, I don't know why it's not
working. The only way these tags work is if I do a cfparam with default="". I
keep getting the same undefined error. Both of your ideas logically make sense
and even the Not IsDefined() should work, that's what I use currently outside of
the qparams. It's weird.
I did read somewhere about the db having to be set up a curtain way to accept
values I think, but if my old method is working just fine, then wouldn't that
mean your code should work as well? Crazy code. It's acting like JS or
something.. *shivers*
><cfqueryparam null="#len(form.field)eq 0#" value="#form.field#">
Author: Tony Bentley
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333282
<cfqueryparam null="#len(form.field)eq 0#" value="#form.field#">
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333281
----- Excess quoted text cut - see Original Post for more -----
The syntax you quoted in the email you found is incorrect. Simply use
a Boolean expression to evaluate the value of the NULL attribute of
CFQUERYPARAM. For example, if a field is present but may contain an
empty value:
<cfqueryparam ... null="#yesNoFormat(not len(trim(form.someField)))#">
Or, if a field may simply not be present:
<cfqueryparam ... null="#yesNoFormat(not structKeyExists(form,
'someField'))#">
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/
Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or
o
Author: Matthew Lowrey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#333280
Hello everyone (if you're still alive) haha!
I notice the posting was dated back in 2004, now it's 2010 and I've come across
this article looking for a better way of choosing when an NULL value is entered
into my SQL Server.
I'm running CF9 and SQL Server I think is up to date as well (doesn't really
matter in this case I think) Here's what I'm doing currently:
< cfif isDefined( " form.myVal " ) >< cfqueryparam ...
value="form.myVal" >< cfelse >< cfqueryparam null="yes >< /cfif
>
Until today, I was content doing it, but, I started at this new company who has
not used cfqueryparams so we're having to go through all our websites and add
this in for security purposes. I wanted to look around to the updated way of
posting things and I wanted to check and see what NULL was. I found this article
and was happy to see a shorter way of doing things. I tried all the options
mentioned and none of them worked.
Anybody know why things aren't working? If I cfparam the variables to default=""
then it will go through, but.. again... more work. I might as well just keep to
the cfif isDefined method.
----- Excess quoted text cut - see Original Post for more -----
Author: Barney Boisvert
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153720
It'll do it in CFMX, but not in CF5 and less. Prior to CFMX, you could only
have single values inside hashes, but with CFMX you can put arbitrary
expressions between them.
Cheers,
barneyb
----- Excess quoted text cut - see Original Post for more -----
Author: Matthew Walker
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153713
It will handle it in CFMX but not CF5.
><cfqueryparam type="cf_sql_integer" value="#form.myVar#" isNull="#NOT
>isNumeric(form.myVar)#" />
>
>That will insert the value provided, unless it's not numeric, in which case
>it'll insert NULL.
Thanks Barney,
One small correction though: I don't think CF can evaluate:
#NOT isNumeric(form.myVar)#
It seems you have to wrap "NOT {expression}" with a function, like:
#yesNoFormat(NOT isNumeric(form.myVar))#
Thanks,
Jamie
_____
Author: Jamie Jackson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153703
><cfqueryparam type="cf_sql_integer" value="#form.myVar#" isNull="#NOT
>isNumeric(form.myVar)#" />
>
>That will insert the value provided, unless it's not numeric, in which case
>it'll insert NULL.
Thanks Barney,
One small correction though: I don't think CF can evaluate:
#NOT isNumeric(form.myVar)#
It seems you have to wrap "NOT {expression}" with a function, like:
#yesNoFormat(NOT isNumeric(form.myVar))#
Thanks,
Jamie
Author: Jamie Jackson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153700
>Since CF and HTML don't have an idea of NULL, you may want to convert an
>empty string from an HTML form into a NULL value within the database. You
>could do it like this, I think:
>
><cfqueryparam ... null="#YesNoFormat(NOT Len(Trim(Form.fieldname)))#">
My situation exactly, thanks fellas.
Thanks,
Jamie
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153698
----- Excess quoted text cut - see Original Post for more -----
Since CF and HTML don't have an idea of NULL, you may want to convert an
empty string from an HTML form into a NULL value within the database. You
could do it like this, I think:
<cfqueryparam ... null="#YesNoFormat(NOT Len(Trim(Form.fieldname)))#">
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
Author: Barney Boisvert
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153697
The null attribute says that the value passed to the DB should be null,
regardless of what the value attribute says. For instance, if you have a
select box with a blank first option and the actual options have a numeric
value, you might use a CFQUERYPARAM like this:
<cfqueryparam type="cf_sql_integer" value="#form.myVar#" isNull="#NOT
isNumeric(form.myVar)#" />
That will insert the value provided, unless it's not numeric, in which case
it'll insert NULL.
Cheers,
barneyb
----- Excess quoted text cut - see Original Post for more -----
Author: Tangorre, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153695
If it is set to YES, the value attribute is ignored and a NULL is used. If
it is sent to NO, the value is used.
_____
Sent: Thursday, February 19, 2004 2:36 PM
To: CF-Talk
Subject: <cfqueryparam> question ("null" attribute)
I'm not quite sure how the null attribute works in <cfqueryparam>
===========================
From the docs:
null [Optional] [Default="No"]
Description: Whether parameter is passed as a null value.
*Yes: tag ignores the value attribute
*No
===========================
Can anyone give me an example of the "null" attribute put to good use?
Thanks,
Jamie
_____
Author: Jamie Jackson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658#153694
I'm not quite sure how the null attribute works in <cfqueryparam>
===========================
From the docs:
null [Optional] [Default="No"]
Description: Whether parameter is passed as a null value.
*Yes: tag ignores the value attribute
*No
===========================
Can anyone give me an example of the "null" attribute put to good use?
Thanks,
Jamie
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
