Thanks Jeff,
However, I don't really see that it's going to make a huge deal of
difference if it's done outside the query or not.... it's essentially
the same code.
I'm really surprised that the cfqueryparam does not just allow you to
specify null="yes" and then convert any zero-length strings to null
values for the insert.
That way the value would be populated to the DB or if an empty string is
encountered it would just replace it with null. If you did not specify
the null paramater, it would default to inserting the empty string.
I had hoped that there might be a better way / something I was missing.
Cheers,
Callum
Jeff Anderson wrote:
Perhaps move that conditional outside of the query and set a variable that
you use in place of yes/no that has a Boolean value...
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Callum McGillivray
Sent: Wed, July 13, 2005 23:24
To: [email protected]
Subject: [CFCDev] Handling NULL's in a DAO
Hi all,
Just a very quick question.
I'm writing a DAO for a customer table using a SQL server database.
In the Update() method of the DAO, I have something like this;
<cffunction name="update" displayname="Update(CustomerBean
CustomerBean)" returnType="CustomerBean" output="false" hint="I update a
customer record in the Database.">
<cfargument name="CustomerBean" type="CustomerBean" required="true">
<cfset var ItemUpdate = "" />
<cfquery datasource="#dsn#" name="ItemUpdate" dbtype="odbc">
UPDATE customer
SET name = <cfqueryparam
cfsqltype="cf_sql_varchar" value="#arguments.CustomerBean.getName()#"
maxlength="40" null="no" />,
guarantor = <cfqueryparam
cfsqltype="cf_sql_integer"
value="#arguments.CustomerBean.getGuarantor()#" null="yes" />,
etc.. etc...
In the table that I am working with, there are a lot of fields that can
contain a NULL value, and when this is read in using the Read() method,
they are represented in CF as a zero length string.
When I am doing an update, do I reall have to use the following line of
code to set null values ?
UPDATE customer
SET name = <cfif
arguments.CustomerBean.getName() is ''><cfqueryparam
cfsqltype="cf_sql_varchar" value="#arguments.CustomerBean.getName()#"
maxlength="40" null="yes" /><cfelse><cfqueryparam
cfsqltype="cf_sql_varchar" value="#arguments.CustomerBean.getName()#"
maxlength="40" null="no" /></cfif>,
It seems a bit cumbersome and I was wondering if there is a better way.
Any thoughts would be appreciated.
Cheers,
Callum
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
CFCDev is supported by New Atlanta, makers of BlueDragon
http://www.newatlanta.com/products/bluedragon/index.cfm
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
CFCDev is supported by New Atlanta, makers of BlueDragon
http://www.newatlanta.com/products/bluedragon/index.cfm
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
CFCDev is supported by New Atlanta, makers of BlueDragon
http://www.newatlanta.com/products/bluedragon/index.cfm
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
