I’m trying to use transactions with DBI and PostgreSQL and I’m running
into problems. I’m using ruby-dbi 0.0.17 and PostgreSQL 7.2.3 (latest
Debian unstable package).
The first problem is that I can’t convince Pg to switch autocommit mode
off. All I get is “‘autocommit’ is not a valid option name”, no matter
if I do it via DBI or in psql. From this follows the second problem:
DatabaseHandle#transaction doesn’t work as is and I need to add explict
BEGIN/END statements:
DBI.connect(dbrc.dsn, dbrc.user, dbrc.password) do |dbh|
dbh.transaction do |dbh|
dbh.do "BEGIN"
dbh.do "INSERT …"
raise "Don’t commit!!!"
dbh.do "END"
end
end
This is the first time I’m playing with DBI and I’m a Pg newbie, too.
So take the following with a shovel of salt.
I propose to change dbi.rb in the following way
class DatabaseHandle
def transaction
raise InterfaceError, “Database connection was already closed!” if
@handle.nil?
raise InterfaceError, “No block given” unless block_given?
commit
begin_transaction
begin
yield self
commit
end_transaction
rescue Exception
rollback
end_transaction
raise
end
end
def begin_transaction
@handle.begin_transaction
end
def end_transaction
@handle.end_transaction
end
end
class BaseDatabase
def start_transaction
raise NotSupportedError
end
def end_transaction
raise NotSupportedError
end
end
And change Pg.rb in the following way
class Database
def start_transaction
@connection.exec(“BEGIN”)
end
def end_transaction
@connection.exec(“END”)
end
end
Possibly these methods need to (or should) take into account the state
of AutoCommit.
Michael
···
–
Michael Schuerig If at first you don’t succeed…
mailto:schuerig@acm.org try, try again.
http://www.schuerig.de/michael/ --Jerome Morrow, “Gattaca”