Ruby-DBI does not ensure the statement handles are finished properly when an exception is raised.  DBI needs to cleanup
statement handles it creates.  Instead, the underlying ruby-odbc level is forced to clean up the handle.  Perhaps you
could wrap the statement handle execution in an ensure block which would perform the sanity_check method regardless
if any exceptions are raised.

Here is a script the demonstrates this issue:

require 'rubygems'
require 'timeout'
require 'dbi'
dbh = DBI.connect('dbi:ODBC:MYCONNECTION', 'sa', 'password')
1000.times do 
  begin  
    Timeout.timeout(0.01) do  
      sth = dbh.execute("select * from vms")
      res = sth.fetch
      sth.finish
      sth = dbh.execute("select * from vms")
      res = sth.fetcha
      sth.finish
    end
  rescue Timeout::Error
    puts "timeout encountered"
  end  
end

Here is the output from the above script when run through irb against sql server 2005 and ruby odbc 0.999:

WARNING: #<ODBC::Statement:0x2b417c656b78> was not dropped before garbage collection.
timeout encountered
WARNING: #<ODBC::Statement:0x2b417c64cf88> was not dropped before garbage collection.
WARNING: #<ODBC::Statement:0x2b417c655660> was not dropped before garbage collection.
timeout encountered
timeout encountered
WARNING: #<ODBC::Statement:0x2b417c64ece8> was not dropped before garbage collection.
timeout encountered
WARNING: #<ODBC::Statement:0x2b417c64f148> was not dropped before garbage collection.
timeout encountered
WARNING: #<ODBC::Statement:0x2b417c64eea0> was not dropped before garbage collection.
timeout encountered
timeout encountered
WARNING: #<ODBC::Statement:0x2b417c64eef0> was not dropped before garbage collection.
WARNING: #<ODBC::Statement:0x2b417c667e50> was not dropped before garbage collection.
timeout encountered
WARNING: #<ODBC::Statement:0x2b417c6b1780> was not dropped before garbage collection.
timeout encountered
WARNING: #<ODBC::Statement:0x2b417e29bdd8> was not dropped before garbage collection.
timeout encountered
WARNING: #<ODBC::Statement:0x2b417c64ce70> was not dropped before garbage collection.

Date: 2009-10-27 23:22
Sender: Joe  Rafaniello

Hi Erik,

I tried putting in the finishing code in the rescue
TimeoutError but I'm still getting the warning about
ODBC::Statement objects not being dropped before gc.  I'm
not sure if my logic is correct or complete.  For example, I
just stumbled on the fact the DBI::Statement object has a
"handle" which is an ODBC::Statement.  There might
be other
ODBC::Statement handles created elsewhere that I am not
"finishing".  

I know enough about the DBI layer to get myself in trouble
so I'll need your help in determining which ODBC::Statement
handles are not being finished, and if it is reasonable to
provide a mechanism at the DBI layer to cleanup these handles.

At this point, I don't know where these statement objects
are being created and not "finished" so I can't even
say
whether this is a DBI not cleaning issue or not.

My concern with leaving it up the client to clean up the
handles is that I find no easy way to navigate the handles
that I have opened and the subsequent handles that were
created to service these handles. Ideally, when I execute a
query and get back a statement handle, calling finish on
that handle should transparently cleanup all the subordinate
handles associated with that statement handle.


require 'rubygems'
require 'timeout'
require 'dbi'
dbh = DBI.connect('dbi:ODBC:VMDB_CONNECTION', 'sa', 'password')

1000.times do
  begin
    sth = nil
    Timeout.timeout(0.011) do
      sth = dbh.execute("select * from users")
      res = sth.fetch
      sleep 1
      sth.finish
    end
  rescue Timeout::Error
    puts "timeout encountered"
 #   puts sth.nil?

    unless sth.nil?
      unless sth.handle.nil?
 #       puts "finish2: #{sth.inspect[0..50]}"
        sth.handle.finish
      end
 #     puts "finish: #{sth.inspect[0..50]}"
      sth.finish unless sth.finished?
    end
  end
end

Date: 2009-10-26 20:10
Sender: Erik Hollensbe

What happens if you put your finishing code in the rescue
portion?

We've been pretty clear, both in the documentation and the mailing
list that currently, cleaning up statement handles is the job
of the user. We expect this to change, but the reality is that
auto-finishing in any context could cause a number of problems
that need to be considered carefully.

In the event your code still cannot cleanup, I'll look into it.