From: Tom Sawyer <transami@...>
Date: 2003-02-09T12:27:31+09:00
Subject: Re: DBI commit and rollback with postgesql DBD

On Saturday 08 February 2003 06:09 pm, Jim Weirich wrote:

thanks Jim. wow! 8-o  did you hack that out solely in repsonse to this email? 
impressive.

> I have no problems using transaction in DBI and Postgresql.  I've
> included the following Test::Unit testcase to demonstrate.  All of the
> tests pass for me.
>
> Make sure you do ...
>
>   db['AutoCommit'] = false

it must be something to do with this. i need to test and see. i have been 
using the hash parameter, i.e. DBI.connect(DSN, DBUSER, DBPW, 'AutoCommit' => 
false), but additionally i have been passing it through a secondary method, 
ex- 

	def make_connection(dsn,dbu,dbpw,*args)
		DBI.connect(dsn,dbu,dbpw,*args)
	end

perhaps i misunderstand *args in relation to hash arguments and this is 
fouling up the hash parameter somehow?

i'll test and see what i come up with. if i don't get anywhere i'll run your 
test case and see what i get on my end.

thanks again,
transami


> before you start using transactions.  That's a common problem.
>
> Unit test follows
> ---------------------------------------------------------------------------
>-------- #!/usr/bin/env ruby
>
> # Unit tests to show transactions in DBI/Pg are working (or not).
>
> require 'dbi'
> require 'test/unit'
> require 'dsn'
>
> class TestTransaction < Test::Unit::TestCase
>   def setup
>     @db = DBI.connect(DSN, DBUSER, DBPW)
>     @db['AutoCommit'] = false
>     initialize_table
>     @db.commit
>   end
>
>   def initialize_table
>     @db.do("DELETE FROM testtransaction WHERE name=?", 'jim')
>     @db.do("INSERT INTO testtransaction VALUES(?,?)",  'jim', 46)
>   end
>
>   def test_setup
>     check_age(46)
>   end
>
>   def test_commit
>     set_age(30)
>     @db.commit
>     check_age(30)
>   end
>
>   def test_rollback
>     set_age(31)
>     @db.rollback
>     check_age(46)
>   end
>
>   def test_transaction
>     @db.transaction do
>       set_age(32)
>     end
>     check_age(32)
>   end
>
>   def test_transaction_rollback
>     @db.transaction do
>       set_age(33)
>       @db.rollback
>     end
>     check_age(46)
>   end
>
>   def test_transaction_mid_rollback
>     @db.transaction do
>       set_age(33)
>       @db.rollback
>       set_age(34)
>     end
>     check_age(34)
>   end
>
>   def test_transaction_exception
>     begin
>       @db.transaction do
> 	set_age(35)
> 	fail "Abort!  Abort!"
>       end
>     rescue
>     end
>     check_age(46)
>   end
>
>   def test_transaction_throw
>     catch(:abort) do
>       @db.transaction do
> 	set_age(36)
> 	throw :abort
>       end
>     end
>     check_age(36)
>   end
>
>   def set_age(age)
>     @db.do("UPDATE testtransaction SET age=? WHERE name=?",
>       age, 'jim')
>   end
>
>   def check_age(age)
>     row = @db.select_one("SELECT age FROM testtransaction")
>     assert_equal age, row['age']
>   end
> end
> --------------------------------------------------------------------

-- 
tom sawyer, aka transami
transami@transami.net