Problem Using Database Defaults with ColdFusion ORM

{ Posted By : Eric Cobb on October 4, 2012 }
Related Categories: ORM, CFML, Databases

Today I was trying to convert a simple insert query over to ORM, and for the life of me I could not get the insert to work. Everything ran perfectly, there were no errors being thrown (that I could find), but the new record would not get inserted into the database. It took me a while to figure out what was going on....well, to be honest I still don't know what was going on, but I figured out how to fix it. Here is an example of what I was running in to:

The Setup:
I have a small SQL Server table that I'm using to log searches from a form:

CREATE TABLE [dbo].[logFormSearch](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [searchstring] [varchar](3500) NULL,
    [stamp] [datetime] NULL DEFAULT (getdate()),
    [ip] [varchar](15) NULL)

And here is the insert query that I want to convert over to ORM:

<cfquery datasource="#dsn#">
insert into logFormSearch(searchstring,ip)
values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.searchString#">,
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#cgi.REMOTE_ADDR#">)

Pretty basic, right? Since my ID and STAMP columns in the database are auto-generated, I only have to specify the other two fields in my query and everything works nicely.

Using ORM:
In order to do away with my insert query and use ORM for my logging, I first created my logFormSearch.cfc with my properties:

<cfcomponent displayname="logformsearch" output="false" persistent="true">
    <cfproperty name="id" displayname="id" type="numeric" generator="identity" generated="insert" insert="false" update="false">
    <cfproperty name="stamp" displayname="stamp" type="date" required="false">
    <cfproperty name="searchstring" displayname="searchstring" type="string" required="false">
    <cfproperty name="ip" displayname="ip" type="string" required="false">

And replaced my above query with this:

<cfset log = EntityNew("logFormSearch")>
<cfset log.setsearchstring(arguments.searchString)>
<cfset log.setip(cgi.REMOTE_ADDR)>
<cfset EntitySave(log)>

It was at this point that my insert stopped working. I didn't get an error, but CF just breezed right over my ORM code without inserting a record. It seems that for whatever reason CF's ORM had a problem with my STAMP column. To be honest I'm not sure why. I'm not setting a value for it in my entity (because I wanted to use the DB default), and the CFC has 'required="false"' for that value so I shouldn't have to set a value for it.

I would think that if I didn't specify a value in my ORM calls, that CF would just omit that value when trying to insert into the DB. Even if CF tried to pass in NULL that would work because my DB column is set to allow NULLS. Maybe it's because the DB column has a [datetime] datatype and CF is trying to pass an empty string? Like I said, I don't know the reasoning, I'm just guessing.

One easy fix for it is to just call the setter for my STAMP column, set the value to #Now()#, and be done with it:

<cfset log.setstamp(now())>

But I didn't want to do that. The fact that I should even have to do that is just strange. Having to set a value in your application code because your programming language can't use the defaults specified in the database is just not a good implementation in my opinion.

The Solution:
Eventually, I found a way to get CF's ORM to allow the DB value to be used. I added the 'insert="false"' attribute to my STAMP column's cfproperty tag and that took care of it.

<cfproperty name="stamp" displayname="stamp" type="date" required="false" insert="false">

To be clear, this will only work if you plan on always using the DB default value. If you want to be able to insert your own value sometimes and use the DB value sometimes, this won't work for that. In my situation it's fine, as I'm always going to be using the DB default on insert, but this may not be ideal for everyone.

I'm really surprised by this functionality. I don't know if this is a limitation of Hibernate, or CF's implementation of it, but this just doesn't seem right. Anything that works this closely with a database should be able to honor column default values.

What do you think? Is this a flaw in CF's ORM, or am I just overlooking something obvious?

Devin's Gravatar You need to turn on ORM logging when debugging this. It'll output the actual queries that are being generated.
# Posted By Devin | 10/4/12 9:49 AM
Eric Cobb's Gravatar Thanks Devin! I didn't realize that feature was available. I'll definitely have to look into it.
# Posted By Eric Cobb | 10/4/12 9:56 AM
gt's Gravatar Did you try dbdefault=null / dbdefault="null" ...
# Posted By gt | 4/23/13 9:51 AM