Hi, I have a problem where I need an auto-incremented id back from the database for a statement that has yet to be committed. The operation that uses the id may fail, and if so, I need to rollback the database. I can't figure out how to send only the statement to the database without the commit without sending over hard coded SQL. This is what I started out with: 1 def add_album 2 Album.transaction do 3 @page = Page.find(params[:id]) 4 @album = Album.new(params[:album]) #creates a new Album in the database 5 6 linking = Linking.new(:page_id => params[:id]) 7 linking.linkable = @album 8 9 create_path(@album.id) #makes a directory using the album id 10 11 linking.save! #sends statement AND commits? 12 @album.save! 13 14 redirect_to :action => 'index' 15 end 16 end The problem with this is that @album's id has not yet been populated. Moving the create_path call past the object saves solves that, but now the rows are committed to the database and I can't roll them back. I'm sure there's a way to solve this, but I can't seem to figure it out from here. Where exactly will Rails send the statements over? Is the "save!" both sending statements AND committing?
on 2006-05-31 20:24
on 2006-05-31 21:06
On May 31, 2006, at 11:24 AM, Joe Cairns wrote: > 2 Album.transaction do > 3 @page = Page.find(params[:id]) > 4 @album = Album.new(params[:album]) #creates a new Album in > the database This is your problem. new DOESN'T create a new object in the database, it creates a new object which may or may not end up in the database (via save). If you want to create a new object in the DB in one step, use create instead. > 5 > 6 linking = Linking.new(:page_id => params[:id]) You shouldn't need to mess with id's directory, AR will handle it via object assignment. > 7 linking.linkable = @album > 8 > 9 create_path(@album.id) #makes a directory using the album id > 10 > 11 linking.save! #sends statement AND commits? > 12 @album.save! > 13 > 14 redirect_to :action => 'index' > 15 end > 16 end Why are you using save! ??? Wouldn't save suffice?
on 2006-05-31 21:21
Thanks Tom, Maybe I'm missing something here: Create will send the statement AND commit, correct? If so, that's inappropriate since I do NOT want to commit at that point. I want to send the insert statement but not commit it. Here's my problem with that: //This succeeds album = Album.create //I now have an instantiated object and a new row in the DB //this fails linking = Linking.create //database row not created and I can't rollback album Does putting those both in the transaction block prevent this scneario even when using create? If so that's pretty nice and handles my question. Will any error in the transaction block, even if not a database call, prevent a commit? Is the rollback automagically issued? Can I manually call one? "Why are you using save! ??? Wouldn't save suffice?" I was using the save to raise an exception as opposed to just signalling a failure... did I walk into a rails no-no?
on 2006-05-31 21:38
I changed my function to this to see what would happen: 1 def add_album 2 Album.transaction do 3 #~ @page = Page.find(params[:id]) 4 @album = Album.create(params[:album]) 5 6 #~ linking = Linking.new(:page_id => params[:id]) 7 linking = Linking.create() 8 linking.linkable = @album 9 10 #~ create_path(@album.id) 11 create_path(30) 12 13 redirect_to :action => 'index' 14 end 15 end I hard coded 30 into my create_path function in order to cause it to error. The problem I feared happened: The database rows were created, but the create_path errors. These are mutually dependent tasks, one can't happen without the other - if one fails, they all need to fail. create_path is merely a wrapper around a Dir.mkdir(path). It is raising a SystemCallError. I'm still hoping I'm doing something wrong and that the Transaction block should be rolling things back.
on 2006-05-31 21:52
On May 31, 2006, at 12:21 PM, Joe Cairns wrote: > Maybe I'm missing something here: > > Create will send the statement AND commit, correct? If so, that's > inappropriate since I do NOT want to commit at that point. I want to > send the insert statement but not commit it. It's my understanding (heaven forbid I'm wrong!) that the transaction block will only commit at the end, or rollback on an exception within. How else could it work? -- -- Tom Mornini
on 2006-05-31 21:52
Are you using a DB that actually handles transactions properly? MySQL with InnoDB or Postgres, for instance? -- -- Tom Mornini
on 2006-05-31 22:05
Tom Mornini wrote: > Are you using a DB that actually handles transactions > properly? > > MySQL with InnoDB or Postgres, for instance? > > -- > -- Tom Mornini Yup.... mySQL with Inno, eventually Oracle if I can sell my department. This is something really fundamental to any application that deals with databases so I'm trying to get my head around the Rails way. Most of the other tools I've used have done stuff like this: //sends 2 insert statements, and a delete to the database through the transaction object ObjectA.new (trans) ObjectB.new (trans) ObjectC.delete (trans) //Then I can commit or rollback everything whenever I want: if do_stuff() = 'good' trans.commit else trans.rollback end All the automatic stuff in Rails is great and I love it, but if I can't handle transactions, then I'm really up a creek with no paddle.... and no boat for that matter. I'm really guessing(hoping?) there is something wrong with my code though!
on 2006-05-31 22:28
Tom you saved the day! I started thinking about the database type (Inno vs other) as I couldn't believe this wasn't working for everyone else. I started checking and all of the tables were set to Inno - except for one which was migrated over from an older database - the album table. Thanks again, it wouldn't havbe even been in my head to check that without your reply. This works 100% as expected now. When the create_path function fails the rollback is issued. PHEW!