Wednesday, July 7, 2010

Using database default values in ColdFusion ORM

I stumbled upon a problem when I was using ORM on a SQL table which has a default value (column not null, default value 0). The problem is that ORM does not accept a value which isn't set because then it will insert it as NULL.

Example:
/**
* @persistent true
* @entityName UserError
* @table UserErrors
* @schema dbo
*/
component output="false"
{
property name="user_id" column="user_id" type="numeric" ormtype="int" fieldtype="id" generator="identity";
property name="error_count" column="error_count" type="numeric" ormtype="int"; //column with default value
}

The step for me was to force that you can set this value as NULL, which caused an error:
property name="error_count" column="error_count" type="numeric" ormtype="int" notnull="false" ;

Instead of an ORM error you get a database error. So the next try is to tell ORM not to use this:
property name="error_count" column="error_count" type="numeric" ormtype="int" persistent="false" ;

This worked for the insert because the value was set correctly in the database. The problem is that you cannot retrieve the value after it is inserted.
So the next step is to modify the data before it is inserted. In the component I added the folowing function:
public void function preInsert() {
if(isNull(this.getError_count()))
this.setError_count(0);
}
Now I get an error converting char to int. This gives me a clue that the preInsert function is executed. So now I have to force ColdFusion to use int for the Error_count default value:
public void function preInsert() {
if(isNull(this.getError_count()))
this.setError_count(javacast("int",0));
}

Now it is working as a charm.
The only problem is that you have two places where your database defaults exists. This is something to keep in mind if you are changing databse default values. It is working for me, but it is not ideal.
If I find a better solution I will post it here. Suggestions are welcome in the comments.

1 comment:

  1. I definitely enjoying every little bit of it. It is a great website and nice share. I want to thank you. Good job! You guys do a great blog, and have some great contents. Keep up the good work.
    ORM

    ReplyDelete