yeah, I see what you mean, just using the "is null" attribute will make the code a lot neater.
<cfqueryparam ... null="#YesNoFormat(NOT Len(args.theValue))#" /> I like this idea <cfset NULL = "$NULL$"> ... else if ( trim(varValue) eq NULL ) bNull = true; return bNull; ... where you've got a string to simulate a NULL. I can use that straight away. For us, the trim() + server side checking is done is a layer before the queries. I can set any empty string value to hold this "$NULL$" and pass it on to the query. good tip Elliot cheers barry.b -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Elliot Russo Sent: Friday, 20 August 2004 9:10 AM To: CFAussie Mailing List Subject: [cfaussie] Re: inserting nulls - or - empty strings? why not this barry SET thisField = <cfqueryparam type="CF_SQL_CHAR" value="#args.theValue#" null="#isNull(args.theValue)#"/> ,thatField = <cfqueryparam type="CF_SQL_CHAR" value="#args.otherValue#" null="#isNull(args.theValue, true)#" /> where is null is a function returning a boolean, if you create a string (constant) you can also simulate a null too <cfset NULL = "$NULL$"> <cfset ALLOWEMPTYSTRINGS = false> <cffunction name="isNull"> <cfargument name="varValue" type="string" required="yes"> <cfargument name="bAllowEmptyString" type="boolean" default="#ALLOWEMPTYSTRINGS#"> <cfscript> var bNull = false; writeOutput(len(trim(varValue))); if ( NOT bAllowEmptyString and NOT len(trim(varValue)) ) bNull = true; else if ( trim(varValue) eq NULL ) bNull = true; return bNull; </cfscript> </cffunction> <cfoutput>#isNull("x")#</cfoutput> false <cfoutput>#isNull("$NULL$")#</cfoutput> true <cfoutput>#isNull(" ")#</cfoutput> true <cfoutput>#isNull(" ", true)#</cfoutput> false the null attribute takes precedence, and I *never* have empty strings in the database (its a personal thing), whereas some don't like nulls Elliot "barry.b" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > > >> inserting nulls - or - empty strings? > > drunken dba's debate this for hours. > > but since CF and java don't do nulls, you'd have to ask why bother? > > > I mean, not writing code like this > > SET > thisField = <cfif len(args.theValue) EQ 0 > NULL <cfelse> > <cfqueryparam type="CF_SQL_CHAR" value="#args.theValue#" /> > </cfif> > , thatField = <cfif len(args.otherValue) EQ 0 > NULL <cfelse> > <cfqueryparam type="CF_SQL_CHAR" value="#args.otherValue#" /> > </cfif> > > > has got to be a saving of 20% on data connection code compared to > > SET > thisField = <cfqueryparam type="CF_SQL_CHAR" value="#args.theValue#" /> > , thatField = <cfqueryparam type="CF_SQL_CHAR" value="#args.otherValue#" /> > > I just thought of another twist: you could tell the difference between > uninitialised values (null) vs any field that's been processed at least > once (empty string). "Empty String" could now mean "not known at this > time" > and "NULL" becomes "yet to be processed" > > any thoughts? > > cheers > barry.b > > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
