From: Michael Schuerig <schuerig@...>
Date: 2002-10-17T08:05:06+09:00
Subject: DBI:Pg 'autocommit' is not a valid option name


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"