Being aware of ORM sessions and gotchas

So recently at work I was trying to troubleshoot an odd issue I had never seen before. I was getting an odd error from a relatively simple HQL query:

Root cause :org.hibernate.HibernateException: coldfusion.runtime.Cast$NumberConversionException: The value '' cannot be converted to a number.

Informative, right?

The query being ran was a simple fetch query along the lines of:

hql = "FROM artists WHERE id = :id";
artist = ormExecuteQuery( hql, {id = 1}, true); 

Basically, there was nothing to indicate that there was a numeric value that was being populated with a null string.

So where did the error come from?



I threw together a quick code example to demonstrate how the error occurred.


It's dependent on two entities for Art and Artist:

component persistent="true" table="art" {

   property name="id" column="artID" generator="increment" ormType="int";
   property name="artName" column="artName" ormType="string";
   property name="price" column="price" ormtype="int";
   property name="largeImage" column="largeImage" ormType="string";
   property name="mediaID" column="mediaID" ormType="int";
   property name="isSold" column="isSold" ormType="boolean";
   property name="artist" fieldtype="many-to-one" fkcolumn="artistID" cfc="artists" ;

}

component persistent="true" table="artists"  {

 property name="id" column="artistID" generator="increment" ormType="int";
 property name="firstname" column="firstName" ormType="string";
 property name="lastname" column="lastName" ormtype="string";  
 property name="address" column="address" ormtype="string";
 property name="city" column="city" ormtype="string";
 property name="postalcode" column="postalCode" ormtype="string";
 property name="email" column="email" ormtype="string";
 property name="phone" column="phone" ormtype="string";        
 property name="fax" column="fax" ormtype="string";
 property name="thepassword" column="thePassword" ormtype="string";
 property name="arts" fieldtype="one-to-many" fkcolumn="artistID" cfc="art" cascade="all" inverse="true";

}

and flushAtRequestEnd = false (which is generally a good idea).

	/*
	We create a function that will update the price of our
	art and save it. This is not an ideal setup and an
	entirely useless function, but it will demonstrate
	the issue.
	*/
	void function updatePrice( required art, required price ) {
	art.setPrice( arguments.price );
	/*
	So, the developer who wrote this line may not have
	known about the save function contained in this component
	or about how ormSessions work, so they just threw an
	entitySave() here.
	*/
	entitySave( art );
	}
	/*
	Again, a useless function but it will demonstrate the purposes.
	Basically, we write an save function that will start an
	orm session and commit at the end so we know the data has been
	persisted.
	*/
	void function save( required art ) {
	trx = ormGetSession().beginTransaction();
	/*
	In case we forgot to assign an artist, let's grab number 1.
	Number 1 means the best, right?
	*/
	if( isNull( arguments.art.getArtist() ) ) {
	hql = "FROM artists WHERE id = :id";
	artist = ormExecuteQuery( hql, {id = 1}, true);
	arguments.art.setArtist( artist );
	}
	entitySave( arguments.art );
	//RIGHT HERE IS WHERE THE DATA PERSISTS!!!!
	trx.commit();
	}
	randomArt = entityLoadByPK( "art", 1 );
	/*
	Here we decide that we want to update the price
	of an existing entity to null because it's going
	to charity. Only, I'm still new to this orm stuff,
	so I assume that I can use "" which isn't actually null
	it's a blank varchar. To cast to null, I would have used
	javacast( "null", 0 ) to get a true java null, but I'm
	sure we all knew that ^__^
	*/
	//WE PASS IN THE BAD DATA HERE, THE ACTUAL ERROR
	updatePrice( randomArt, "" );
	/*
	We do all sorts of exciting and neat things with the art.
	We admire it. We love it. The code that would be here
	instead of this comment block would make other developers weep.
	But, for troubleshooting sake, let's just understand that
	a whole lot of stuff that isn't written happens here...
	*/
	/*
	So now, as part of this inventory process,
	we want to create a new piece of art and then save it.
	Unfortunately, we're going to forget to set an
	artist, so it will attempt to set one in the
	save method. Maybe if I wasn't on a constant stream
	of whiskey I would have remembered....
	*/
	art1 = entityNew( "art",
	{
	artName = "Code Caito1",
	price = "2000",
	largeImage = "codeCaito1.jpg",
	mediaID = "1",
	isSold = false
	}
	);
	//Let's save it!
	save( art1 );

view raw gistfile1.java hosted with ❤ by GitHub

So, If you run this, it will toss an error. The same error that I was getting at work. That is:

Error while executing the Hibernate query.

org.hibernate.HibernateException: coldfusion.runtime.Cast$NumberConversionException: The value '' cannot be converted to a number.
 
The error occurred in C:/CodingProjects/CodeExamples/BlogExamples/ormTroubleshooting.cfm: line 36
Called from C:/CodingProjects/CodeExamples/BlogExamples/ormTroubleshooting.cfm: line 83
Called from C:/CodingProjects/CodeExamples/BlogExamples/ormTroubleshooting.cfm: line 36
Called from C:/CodingProjects/CodeExamples/BlogExamples/ormTroubleshooting.cfm: line 83
34 :     if( isNull( arguments.art.getArtist() ) ) {
35 :       hql = "FROM artists WHERE id = :id";
36 :       artist = ormExecuteQuery( hql, {id = 1}, true);  
37 :       arguments.art.setArtist( artist );
38 :     }

The basics of what is happening is that around the line where this occurs: updatePrice( randomArt, "" );  We are setting an integer value to an empty string. As stated in the code, this is _NOT_ a database null value. If we had used javacast( "null", 0 ) instead, it would not have tossed an error. NULL is a valid value. "" is not. Also, you can't pass a javacast null into a function and have it recognize it. So, if we had written the function this way:

   if( arguments.price == "" ) {
    arguments.art.setPrice( javacast( "null", 0 ) );
   } else {
    arguments.art.setPrice( arguments.price );
   }

it would have worked just fine.

However, because of the way ORM sessions work, nothing is really happening there. Even when it saves in the updatePrice() method, nothing is really happening because it won't get committed until you explicitly commit it using a transaction somewhere or calling ormFlush(). Then, and only then will it try to persist that data to the database.

So, when we start our orm transaction in the save method, it goes to commit and persist the bad data. However, it can't. It's expecting an integer and it's getting an empty string. So it throws an error. However, because the error didn't _technically_ occur until that transaction, it gives that code block for where the error is occurring.

Sorry if this is a little hard to follow. I'm new to explaining some of these issues to people. The takeaway is that you need to be mindful of how that data is really persisting over to the database. Thankfully, with CF 10, the error messages get a little better:

org.hibernate.HibernateException: Property : price - The value '' cannot be converted to a number.

Wow! Property names! That's actually a _huge_ improvement over where things were before. That is definitely helpful in the troubleshooting process.

tl;dr - Orm session troubleshooting can be a headache. Better brush up on how they work.


For more explinations, see:
Adobe's docs and A good post by Mark Mandell