How to Set Timeout for Ruby ODBC Driver for SQL Server?

Question

Hello,

I would like to know how to explicitly set a timeout for the Ruby DBI ODBC driver, when connecting to SQL Server. I would like long running queries to simply timeout and cancel themselves, saving further server resources and Rails processes.

This was happening while we were using the ADO based Ruby driver, but now that we've switched to DBD::ODBC we are no longer experiencing timeouts.

I've looked around but cannot find out how to set the query timeout value. I've tried explicitly setting some values in the driver, and specifying both Timeout and ConnectionTimeout and Connect Timeout in the DSN configuration for the connection. No luck.

Your tips and advice are very appreciated.

Thanks! Seth

Answer 1

What about wrapping the whole query in Ruby's built-in timeout library?

require 'timeout'

begin
  Timeout::timeout(5) {
    @db.query(...)
  }
rescue Timeout::Error
  puts "Too slow, forget about it."
end

Answer 2

Hm, good idea! We'll give it a shot and let you know.