Dave Thomas wrote:
> markus jais <info / mjais.de> writes:
> 
> > Is it a good idea to create a database (e.g. called "dbh") handle in
> > each method and call at the end dbh.disconncect..
> ... 
> > on the other hand: are the performace disadvantages with this
> ...
> > another idea would be to make dbh global because several classes
> > need a database handle.
> 
> In a single threaded application, this isn't much different to using
> the global, except the discipline of having to signal the end of your
> use of the connection with a 'close' will help structure your code. If
> you start to handle multiple things concurrently, though, this
> approach will start to shine.
> 
> Using this approach does have some downsides. I've seen Java
> programmers (connection pooling is common in Java-based server
> applications) forget that they're dealing with a transactional
> database, and fail to group things together properly. For example,
> there may be a transaction which debits one account and credits
> another. They might (wrongly) write a method to do the debit, another
> to do the credit, and have each use it's own DB connection. Obviously
> in this case the connection needs to be shared between the two so that
> the debit and credit happen in the same transaction.
> 
> Anyway, writing a simple connection pool in Ruby should be just a few
> lines of code.

require "dbi"

class ConnectionPool

  def initialize(nr_handles, driver_url, user=nil, pass=nil, attrs={})
    @driver_url, @user, @pass, @attrs = driver_url, user, pass, attrs

    @handles = (1..nr_handles).collect do 
      dbh = DBI.connect(@driver_url, @user, @pass, @attrs)
      class << dbh
        attr_accessor :in_use
        def close; @in_use = false end
      end
      dbh.in_use = false
      dbh
    end
  end

  def getConnection
    con = @handles.find {|dbh| dbh.in_use == false }
    raise "No free connection" if con.nil?
    con.in_use = true
    con
  end

  def close
    @handles.each {|dbh| dbh.disconnect}
  end
end


if __FILE__ == $0
  MyPool = ConnectionPool.new(4, "dbi:Pg:mydatabase", "user", "name", 'AutoCommit' => false)

  dbh = MyPool.getConnection
  # use dbh..
  dbh.commit
  dbh.close 

  MyPool.close
end


Note: I have not tested if it works.

Regards,

  Michael

-- 
Michael Neumann
merlin.zwo InfoDesign GmbH
http://www.merlin-zwo.de