QuackFuzed [ Ramblings of a Con..ColdFuzed Mind ]

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.

1. <cffunction name="configure"
2. hint="Configures the object prior to population"
3. returntype="void" output="false" access="public">
4. <cfscript>
5. setSomeDateNull();
6. </cfscript>
7. </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".

1. <property name="SomeDate"
2. column="some_date"
3. type="date"
4. 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!

Comments

(Comment Moderation is enabled. Your comment will not appear until approved.)

On 7/9/09 at 8:52 PM, Dan Wilson said:

Yeah, that hit me a few times. I'm sure you'll help someone out with this post.

DW

On 8/6/09 at 5:12 AM, Robert Rawlins said:

Ah! This is an excellent post! I had this exact same problem myself recently with NULL dates, I had them configured as nullable in the transfer config and couldn't figure out why it was saving a date when committing the record. I've been adding logic into my controllers to setIsNull() before posting them to be committed.

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:

@Dan - Looks like you were right. :-)

@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:

Hey Matt,

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:

@Rob - It all boils down to personal preference. Do you want your business logic encapsulated within the business object, or are you okay with having portions of it (e.g. setSomeDateNull()) in another layer? Neither one is necessarily more right or more wrong than the other. For me personally, I prefer it to be in the BO. :-)

On 8/7/09 at 1:51 PM, Matt Quackenbush said:

@Rob - Heh. I just realized that I misunderstood your comment. LMAO

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.

[Add Comment]