Photo from Chile

CF9 ORM - Experimenting with type vs ormtype

I've been playing around with the attributes that are available, as of ColdFusion 9, to the cfproperty tag that are meant to describe the datatype of the property for persistent objects. Currently there are four options:

  • type - this is the standard type attribute that was available to the cfproperty tag pre-CF9. You are only supposed to use valid CF types for this.
  • ormtype - this is used to tell Hibernate what the data type of the property should be. It is a database agnostic value. Hibernate will translate it into a valid datatype for the dbms in question.
  • sqltype - this is a dbms-specific datatype. You would use this when you want to direct Hibernate to create a column with a specific datatype.
  • none - if you are pointing your persistent object at an existing table, you can choose not to specify any of the above three attributes, and Hibernate will look at the database to determine the datatype.

It may already be obvious that this is a bit confusing. I have been trying to figure out how everything works, and what the ramifications of different combinations of attributes and values are. I plan a more in-depth post on that topic when I've got it clearer in my mind. For now I'm posting the results of some testing that I did, as I find it interesting and it might help me and others understand this stuff better.

type vs ormtype and Setters

One thing that I do know is that, if you use the type attribute CF will generate typed setters for your properties, whereas if you use ormtype instead it will generate typeless setters. Consider the following example:

If you have an object like this:

view plain print about
1<cfcomponent persistent="true">
2    <cfproperty name="myDate" type="date" />

and try to do:

view plain print about
1<cfset myObject.setMyDate("abc") />

CF will throw an error because "abc" isn't a valid date. But if you have an object like this:

view plain print about
1<cfcomponent persistent="true">
2    <cfproperty name="myDate" ormtype="date" />

and try to do:

view plain print about
1<cfset myObject.setMyDate("abc") />

CF will happily set the value into the myDate property for you, and will only throw an error when you try to save the object.

Testing type with ormtype values

As part of the testing that I've been doing to figure this stuff out, I decided to try creating a cfc using all of the available ormtypes, but placing those values in the type attribute, rather than the ormtype attribute. Here's what that cfc looks like:

view plain print about
1<cfcomponent persistent="true" output="false">
2    <cfproperty name="ID" fieldtype="id" generator="native" />
3    <cfproperty name="string" type="string" />
4    <cfproperty name="character" type="character" />
5    <cfproperty name="char" type="char" />
6    <cfproperty name="short" type="short" />
7    <cfproperty name="integer" type="integer" />
8    <cfproperty name="int" type="int" />
9    <cfproperty name="long" type="long" />
10    <cfproperty name="big_decimal" type="big_decimal" />
11    <cfproperty name="float" type="float" />
12    <cfproperty name="double" type="double" />
13    <cfproperty name="Boolean" type="Boolean" />
14    <cfproperty name="yes_no" type="yes_no" />
15    <cfproperty name="true_false" type="true_false" />
16    <cfproperty name="text" type="text" />
17    <cfproperty name="date" type="date" />
18    <cfproperty name="timestamp" type="timestamp" />
19    <cfproperty name="binary" type="binary" />
20    <cfproperty name="serializable" type="serializable" />
21    <cfproperty name="blob" type="blob" />
22    <cfproperty name="clob" type="clob" />

When I reloaded the ORM, it didn't throw any errors, and happily created a table for me with one column for each of those properties. Here's what the generated MySQL table looked like:

view plain print about
1CREATE TABLE `ormBlog_Intro`.`ORMTypeTest` (
4 `character` CHAR(1) NULL DEFAULT NULL ,
7 `integer` INT(11) NULL DEFAULT NULL ,
10 `big_decimal` DECIMAL(19,2) NULL DEFAULT NULL ,
13 `Boolean` BIT(1) NULL DEFAULT NULL ,
14 `yes_no` CHAR(1) NULL DEFAULT NULL ,
15 `true_false` CHAR(1) NULL DEFAULT NULL ,
20 `serializable` TINYBLOB NULL DEFAULT NULL ,

That looks like a pretty close approximation of my types. I assume at this point that if no ormtype is specified that CF simply takes whatever is in type and uses it as an ormtype. So, what happens when I try to call the setters for these properties?

I set up a test template with the following code:

view plain print about
1<cfset ORMTest = EntityNew("ORMTypeTest") />
2<cfset valArray = [1,"a",Now(),1.1] />
3<cfloop array="#valArray#" index="theVal">
4    <cfloop array="#getMetaData(ORMTest).properties#" index="prop">
5        <cfif NEQ "ID">
6            <cftry>
7            <cfinvoke component="#ORMTest#" method="">
8                <cfinvokeargument name="" value="#theVal#" />
9            </cfinvoke>
10            <cfoutput>Successfully set #theVal# into<br /><br /></cfoutput>
11            <cfcatch type="any"><cfoutput>Error trying to set #theVal# into<br>#cfcatch.detail#<br />#cfcatch.message#<br />#cfcatch.type#<br /></cfoutput></cfcatch>
12            </cftry>    
13        </cfif>
14    </cfloop>
15    <cftry>
16        <cfset EntitySave(ORMTest) />
17        <cfset ormFlush() />
18        <cfoutput>Successfully saved the Entity.<br /><br /></cfoutput>
19        <cfcatch type="any"><cfoutput>Error saving<br>#cfcatch.detail#<br />#cfcatch.message#<br />#cfcatch.type#<br /></cfoutput></cfcatch>
20    </cftry>

This code will attempt to set an integer, a string, a date, and a double into each property. If the setter works, I'll see a message on the screen to that extent. If a setter fails I'll see a message, followed by some of the detail from the cfcatch. After each property is set with one of those values, I attempt to save the entity. If the save fails I display the details of the error for that as well. So, what did I find out by running this?

Invalid Values for Type

I found that the following values:

  • character
  • big_decimal
  • yes_no
  • true_false
  • text
  • timestamp
  • serializable
  • blob
  • clob

all failed to work at all. No matter what value I passed into the setters for those properties, I received the same error: "The XXX argument passed to the setXXX function is not of type XXX."

Where XXX is the datatype/property name. I am assuming in this case that CF is treating these as custom types that should correspond to a cfc, and is therefore rejecting any simple values that I pass in. To continue with the experiment, I removed those properties from my object and ran the test code again.

Results of Calling Setters

When I passed the value 1 into each of my properties, they all accepted it, except for the binary property, which threw an error. This makes sense as all of those other datatypes should accept an integer as a valid value. My attempt to save the object succeeded. Hibernate ended up putting a date of 1899-12-31 into my date property, which I assume is the correct translation of the number 1 into a date. I'm not sure if CF, Hibernate or MySQL did that translation.

When I passed the value a into each of my properties, the following setters failed: integer, float, boolean, date and binary, which is expected. What was not expected, however, is that short, int, long and double each accepted the value. When I tried to save the object, it failed, not surprisingly. The error message is: "Root cause :org.hibernate.HibernateException: coldfusion.runtime.Cast$NumberConversionException: The value a cannot be converted to a number." I guess Hibernate wasn't too happy about being asked to put the value a into one of those numeric columns.

When I passed the value Now() into each of my properties, the following setters failed: integer, boolean and binary, which is expected. Again, as above, short, int, long and double each accepted the value, as did float, which had previously failed with the value a. Again, when I tried to save the object, it failed. The error message is: "Root cause :org.hibernate.HibernateException: coldfusion.runtime.Cast$OutOfBoundsException: Cannot convert the value 40071.54907407407 to short because it cannot fit inside a short." Makes sense, kinda.

When I passed the value 1.1 into each of my properties, they all accepted it, except for binary and integer. This seems to make sense. When I tried to save the object, if failed. The error message is: "Root cause :org.hibernate.HibernateException: coldfusion.runtime.Cast$CharCastException: Unable to cast object 1.1 to char.". The column char in the database is defined with a length of 1, so it makes sense that this would fail.

What Does It All Mean?

That's a tough one. I can make the following general observations:

  1. Strictly for the purpose of table creation, you can use any valid ormtype value as a type, and a reasonable table will be created for you by Hibernate.
  2. You cannot use any of the following ormtype values if you want to be able to call a setter on the property:character, big_decimal, yes_no, true_false, text, timestamp, serializable, blob or clob.
  3. You can use any of the following ormtype values, but they will not create typed setters: char, short, int, long and double.
  4. The following ormtype values will create valid typed setters: integer, boolean, date and binary. The interesting item here is integer. As far as I understand integer is not a native CF type. Perhaps that's been added under the hood somewhere.
  5. The ormtype value float appears to create a typed setter, but it allows a date to be passed.

I guess the bottom line is that you can use ormtype values to specify the datatypes of your properties using the type attribute, which will sometimes result in typed setters, but that perhaps it's not such a good idea.

Bob, great post and exploration. I was surprised that using the Type attribute worked as well as it did (even if not all over the place.

This is a bit off topic, but on topic for data types; one thing that I was very curious about was data-type translations as the insert/select level. For example, let's say (for funzies) that I wanted to represent a date/time stamp as a standard ColdFusion date object in the CF world... but, when I went to save it, could I convert it to a decimal value.

I think I tried using type="date" and ormtype="timestamp" and then sqltype="float". None of these would seem to work (i tried playing with various combinations). Not sure why I would do something like that, per say, but I can see places were database-level storage might be optimized by not perfectly representing the data type it gets translated into.
# Posted By Ben Nadel | 9/16/09 9:35 AM
Wow! Thanks, Bob! As I'm quickly diving deeper into ORM in CFML, I'm digging around for various bits and keep finding my way back to your blog :) So thanks for sharing!
# Posted By Jamie Krug | 5/19/10 10:09 PM
No problem, Jamie. That's exactly why I post this stuff.
# Posted By Bob Silverberg | 5/19/10 10:11 PM
Bob - I found that 'text' does work but 'clob' does not.

Interestingly if I dump the object it shows the getter/setter methods.

I am using a slightly different syntax for my properties with the ormtype attribute:

property name="info" type="string" ormtype="text";
# Posted By Johan | 6/28/10 1:34 AM
Great post Bob! As far as i understand your researches, the best type declaration i could do is to specify the type AND the ormType Attributes with their correct values. Which is exactly what i end up on my tests.
# Posted By Marco Betschart | 8/19/10 2:47 AM
@Marco: I actually prefer to just use ormType and not use type at all, so I don't end up with typed setters. That way I can catch datatype errors in my validations, rather than have CF throw an error when calling a setter.
# Posted By Bob Silverberg | 8/20/10 10:04 AM
@Bob: Sounds interesting - How do you do your validations? Do you use the validation attributes of the cfproperty - Tag? At the moment i've got no idea how do you then catch the database errors?

I've just started to dig into this ORM stuff and i'm excited of learning how others deal with it :)
# Posted By Marco Betschart | 8/23/10 3:30 AM
@Marco: I use my own validation framework for doing all of my validations. I released it as open source a couple of years ago. It's called ValidateThis and you can find more info at
# Posted By Bob Silverberg | 8/23/10 9:18 AM