Tuesday, August 3, 2010

ColdFusion 9 ORM and the unsavedvalue Attribute

I was working on a project today that I decided to use with CF 9 ORM and ran into an issue while saving a new record. The database I am using is MySQL 5.1. The error I was getting:
Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
Google had some stuff to say, primarily about unrelated solutions, so I decided to use a lifeline and phone a friend. Marc Esher apparently has seen this (amongst some of the other less than helpful Hibernate errors CF causes) and suggested using the 'unsavedvalue' attribute. Worked like a charm so I decided to post here...

The class in question:
<cfcomponent displayname="Project" hint="I am the Project class." persistent="true">

<!--- properties --->
<cfproperty name="id" fieldtype="id" type="string" generator="uuid" length="32" />
<cfproperty name="title" type="string" length="75" />
...

<!--- constructor --->
<cffunction name="init" returntype="Project" access="public" output="false" hint="Constructor">

<cfreturn this />
</cffunction>
</cfcomponent>
I'm using a uuid PK and the MySQL database table does not autogenerate the PK so CF ORM needs to handle this, hence the 'generator' attribute on the id tag. What seems to be occurring is CF tries to commit the instance when I call entitySave(), but sees it as a update as opposed to a create. Marc's suggestion to use the 'unsavedvalue' attribute lets CF know that it has not saved the record yet, forcing it to create a new record. The updated CFC:

<cfcomponent displayname="Project" hint="I am the Project class." persistent="true">

<!--- properties --->
<cfproperty name="id" fieldtype="id" type="string" generator="uuid" unsavedvalue="-1" length="32" />
<cfproperty name="title" type="string" length="75" />
...

<!--- constructor --->
<cffunction name="init" returntype="Project" access="public" output="false" hint="Constructor">

<cfscript>
setID(-1);
</cfscript>

<cfreturn this />
</cffunction>
</cfcomponent>
The 'unsavedvalue' attribute is being set to -1 (which is safe even if you are using a numeric PK). When CF ORM creates an instance of the Project class it automatically calls the init() method for us, which in turn sets the starting ID value to -1. When entitySave() is called, CF ORM checks the value of the id field before committing the object and correctly creates a new method.

3 comments:

  1. Cool.

    Out of interest did you try setting the forceinsert=true attribute on entitySave()?

    ReplyDelete
  2. I did not. I bet it would've worked though :) The reason I did it in the object is to abstract that detail from the user, essentially making the object to work as one would expect it to (save performing the appropriate action of create or update).

    ReplyDelete
  3. Thanks Nic. Worked like a charm. Guess I must have looked over it in the past times I have used it.

    ReplyDelete