ORM Transaction Rollback not working as expected

[Henry.d]

Hi, if I run the calling code below, with forced error on setproductid call to generate an exception I expect the transaction to rollback any database changes. However this is not the case as the user data is persisted.

How can I get User to rollback when Licence, or anything else, fails within the transaction? Or am I looking at this the wrong way?

Thanks.

## Licence.cfc ##
component persistent="true" table="Licence" {
property name="LicenceID" column="LicenceID" type="numeric" ormtype="int" fieldtype="id" generator="identity";
property name="ProductID" column="ProductID" type="numeric" ormtype="int";
.......
property name="Users" fieldtype="many-to-one" cfc="Users" fkcolumn="UserID" cascade="all" ;


## User.cfc ##
component persistent="true" table="User" {
property name="UserID" column="UserID" type="numeric" ormtype="int" fieldtype="id" generator="identity";
property name="Name" column="Name" type="string" ormtype="string";
property name="Licence" fieldtype="one-to-many" cfc="Licence" fkcolumn="UserID" type="array" inverse="true" cascade="all";


## calling code ##
transaction {
User = entitynew("User");
User.setName("name");

Licence = entitynew("Licence");
Licence.setProductID(""); // force boolean required db error
User.addLicence(Licence);

entitysave(User);

}

[Andrew Scott]

Don't you have to manage that, with a try catch block and TransactionRollback/Commit?

Regards,

Andrew Scott

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.
To post to this group, send email to cf-or...@googlegroups.com.
Visit this group at http://groups.google.com/group/cf-orm-dev.
For more options, visit https://groups.google.com/d/optout.

[Nando Breiter]

Here's what the code looks like, in case you aren't familiar with it:

       transaction {

try {

                          User = entitynew("User");

                           ....
                           entitysave(User);

transaction action="commit";

} catch(any e) {

transaction action="rollback";

                        rethrow;

}

}

You could also include validation code in the transaction block and only commit if valid:

transaction {
	//do stuff
	if (good) {
		transaction action="commit";
	else {
		transaction action="rollback";
	}
}

Aria Media Sagl
Via Rompada 40
6987 Caslano
Switzerland

+41 (0)91 600 9601
+41 (0)76 303 4477 cell
skype: ariamedia

[Henry.d]

Thanks Nando, I left the try catch out to simplify as any exception within the transaction should roll back the transaction anyway right? I have attempted with try catch and explicit rollback and get the same results anyway.

Of course, with that sort of setup and persisting a simple object all works well. The problem I am having is when related objects are introduced and the database error occurs on the child/many (licence) object of the relationship, the persisted parent/one (user) object is not rolled back.

So again in the example I gave above
user is created
licence is created and added to user
user is persisted and as a result the attempt to persist licence is made. This fails at the db end (my forced boolean error).
this causes an exception and licence is not persisted....
here is where I expect user to be rolled back... but it is not.

I know the errors can, and probably should be caught at the object level with the validate and validateparams property attributes etc, but there will always be exceptions. And when they occur, at the database level, within a transaction all persisted data needs to be rolled back.. pretty important for data integrity.

Again I feel like I am missing something here (setup wise perhaps?) as it is such a simple process.

Thanks for your help

[Henry.d]

I have been looking through John Whish's book for an answer!! Great book but only highlighted my plight.

Here is Johns one to many relationship example which is basically the same as mine.. even has the same rollback problem!! I added a try catch block for clarity. The results are the same with or without try catch block.

transaction {
try {
// create a new Author
Author = entityNew( "Author" );
Author.setFirstName( "John" );
Author.setLastName( "Whish" );
// create a new Post
Post = entityNew( "Post" );
Post.setTitle( "Post 1" );
Post.setContent( "Lorem ipsum dolor amet." );
Post.setIsPublished(true);
Post.setDatePublished( Now() );
// add the Post to the Author
Author.addPost( Post );

// save the new Author
EntitySave(Author);

} catch(any e) {
transaction action="rollback";
rethrow;
}
}

the above works fine. It creates an Author and Post entry with the correct relationship.

I cause the Post entry to fail at the database level by simply changing
Post.setIsPublished(true); to Post.setIsPublished(""); There is no validation on the isPublished cfc property so the invalid data is accepted at that point. But when persisted the database throws an exception as it needs a bit value for isPublished. Fair enough.. Post entry fails... Author entry does not.. and is not rolledback!

[Cameron Childress]

On a hunch - what are your ormsettings here? Are both of these false?

this.ormsettings.flushatrequestend = false;

this.ormsettings.automanagesession = false;

If not, change them both to false and try again. Theory being that Author may still be getting swept up on the end of request flush somehow.

You will also now need to explicitly commit that transaction if it does not error and the catch doesn't fire.

-Cameron

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.
To post to this group, send email to cf-or...@googlegroups.com.
Visit this group at http://groups.google.com/group/cf-orm-dev.
For more options, visit https://groups.google.com/d/optout.

--

Cameron Childress
--
p:   678.637.5072

im: cameroncf

facebook | twitter | google+

[Henry.d]

Hi Cameron,

I already had this.ormsettings.flushAtRequestEnd = false;
I've now added this.ormsettings.automanagesession = false;

It didn't change anything.. but you did put me back in the application settings where I found the problem!! Bit of a weird one really.

Basically setting the datasource username and password in the application.cfc was the problem. So I moved those into cfadmin to fix.

So this application.cfc did NOT allow the rollback.

component {
this.name = "myORMApplication";
this.ormenabled = "true";
this.datasource = {name = 'mydsn', username = "dbuser", password = "dbpwd"};
this.ormsettings.flushAtRequestEnd = false;
this.ormsettings.automanagesession = false;

}

But this one (with cfadmin handling dsusername and dspassword) DOES allow the rollback.

component {
this.name = "myORMApplication";
this.ormenabled = "true";
this.datasource = {name = 'mydsn'};
this.ormsettings.flushAtRequestEnd = false;
this.ormsettings.automanagesession = false;

}

Thanks all for the help.