Transfer: Using NULL Dates
Posted on July 9, 2009 at 7:28 PM in ColdFusion, Transfer
The purpose of this post is primarily to assist me in remembering this little tidbit, but hopefully it will be of use to someone else as well.
To date (like the pun?) I have not had to use NULL dates with Transfer, so I had never encountered this issue. However, today I needed to handle them, and so I wrote a couple of lines in my decorator thinking that would properly take care of everything.
Transfer initializes any date properties with #now()#, but uses 1/01/0100 to represent a NULL date, since that is the earliest date that ColdFusion will recognize. So, in my decorator, I simply used the configure() method to make sure that the date is initialized as the recognized NULL value.
- <cffunction name="configure"
- hint="Configures the object prior to population"
- returntype="void" output="false" access="public">
- <cfscript>
- setSomeDateNull();
- </cfscript>
- </cffunction>
Since configure() runs prior to the object being populated, if the date from the database is NULL, it will be set to the recognized NULL value. That's all there is to it, right?
Wrong. I ran a quick little insert test and KABOOM! SQL Server screamed back at me that it did not like the fact that I had used such an archaic date.
"WTF? I set it to the recognized NULL value! Why did Transfer try to insert the date?" I muttered as I angrily slammed my fist on my desk.
Off to gmail I went, searching my archived mail from the Transfer Dev mailing list. And there I found the rest of my answer. The configure() method in the decorator got me half-way there, but I needed to slightly tweak my transfer.xml to get me the rest of the way.
In the property declaration for the object in question, just add nullable="true".
- <property name="SomeDate"
- column="some_date"
- type="date"
- nullable="true" />
And now my NULL dates magically work beautimously. :-)
Thanks again to Mark and everyone that has ever contributed to Transfer for your time and generosity!
Latest Articles
- No recent entries.
Categories
- ColdBox (21) [RSS]
- ColdFusion (92) [RSS]
- Fusebox (3) [RSS]
- General (22) [RSS]
- jQuery (15) [RSS]
- Kalendar (1) [RSS]
- Linux (1) [RSS]
- Mura CMS (1) [RSS]
- Railo (1) [RSS]
- Rants (5) [RSS]
- Transfer (8) [RSS]
- Uni-Form Tag Library (36) [RSS]
On 7/9/09 at 8:52 PM, Dan Wilson said:
DW
On 8/6/09 at 5:12 AM, Robert Rawlins said:
This solves that issue perfectly! One does ponder as to whether Transfer should be setting the default of nullable fields to their null value rather than now()? I mean, you wouldn't default populate a nullable string field with "empty string" would you?
Perhaps something which needs to be raised for discussion with Mark.
Cheers for the tip anyway Matt, I appreciate it.
Rob
On 8/6/09 at 2:36 PM, Matt Quackenbush said:
@Robert - There has been discussion with Mark about it. This behavior was in place long before the NULL settings were added to Transfer. It has just been one of those things that has never been high enough on the priority list to address specifically. Or something like that. I'm paraphrasing, of course. ;-)
On 8/7/09 at 12:24 PM, Robert Rawlins said:
Ah I see well I guess we can live with the workaround for now, not long before lovely CF9 ormyness is ready for us to use.
Just as a quick note regarding your script for the configure() method, a more succinct way of doing this would be to simply call setSomeDateNull() which will null the field so you don't have to define the full date/time string.
Rob
On 8/7/09 at 1:47 PM, Matt Quackenbush said:
On 8/7/09 at 1:51 PM, Matt Quackenbush said:
I had totally forgotten about the whole setSomeDateNull() method until you mentioned it. I was thinking you were saying to call that from somewhere else, but I realize now that you meant calling it within the configure() method instead of creating the string. DUH!
Thanks for the tip. I will update the post to reflect the change for future readers.