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).
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
Great post! CF10's more helpful messages are definitely a boos here.
ReplyDeleteI think all of us ORM users have hit this (repeatedly!). Speaking just for me, these types of problems have led me to validate everything prior to save, because validation errors happen earlier, and give you a chance to provide meaningful causes for problems, prior to Hibernate's unhelpful messages. I use ValidateThis, and consequently would have a validation XML file for this object with a "price" validation that would ensure it was a number.
At work, all of our ORM objects get validated prior to save, and if validation fails save never happens. Validation failures then get passed back up the chain to whatever is responsible for handling them. Validation is A Good Thing(tm) in general, and here's definitely a case where the pain of ORM probably leads you to the best practice, because it's the first defense against that pain.
You'll appreciate this: the good thing here is that it's trivial to write an MXUnit test where you create one of those objects, with no data population, and pass it to your service layer's "save" method. You expect it to not get saved, and you expect a slew of validation failures. Then, in your test, you just have assertions for those expected failures to ensure your validation config is solid.
A typical test looks something like this (in pseudocode):
function save_validates_expected_validations(){
var fields = "price,name,hoozie,whatsit";
var thingie = new Thing();
var result = service.save( myObject );
for( field in fields ){
assertTrue( result.getValidation().hasFailure(field);
}
}
Thanks again for the post!