DBI Timeout

[gregarican]

I am currently trying to retrieve a rather large recordset from a
remote MS SQL Server. The recordset is retrieved using Ruby DBI. The
recordset is roughly 120,000 rows. The Ruby script bombs out with an
SQL timeout error. Is there a parameter I can use to extend the
timeout value in the DBI library so that I can pull everything?

 

[gregarican]

I am currently trying to retrieve a rather large recordset from a
remote MS SQL Server. The recordset is retrieved using Ruby DBI. The
recordset is roughly 120,000 rows. The Ruby script bombs out with an
SQL timeout error. Is there a parameter I can use to extend the
timeout value in the DBI library so that I can pull everything?

Specifically I am using the dbd_ado DBI library. Since the fetch_all
method was timing out (the DB is alive and kicking; I've verified
that) I tried while-looping a fetch method to append to the result
array as an alternative. Either way the operation still times out :-(

 

[khaines]

Specifically I am using the dbd_ado DBI library. Since the fetch_all
method was timing out (the DB is alive and kicking; I've verified
that) I tried while-looping a fetch method to append to the result
array as an alternative. Either way the operation still times out :-(

It sounds to me like the timeout is coming from the database. Do you have
a full stack trace for the exeption?


Kirk Haines

 

[gregarican]

It sounds to me like the timeout is coming from the database. Do you have
a full stack trace for the exeption?

Kirk Haines

It is definitely coming from the database. Right now I'm trying to
narrow down how to specify a CommandTimeOut parameter for the ADO
conncection. My provider is SQLOLEDB. It's not specified in the
connection string, I know that at least. But it's a property of the
ADO connection itself. I know how to do this in VB, VBScript, C#, etc.
but not using the Ruby DBI implementation.

 

[khaines]

It is definitely coming from the database. Right now I'm trying to
narrow down how to specify a CommandTimeOut parameter for the ADO
conncection. My provider is SQLOLEDB. It's not specified in the
connection string, I know that at least. But it's a property of the
ADO connection itself. I know how to do this in VB, VBScript, C#, etc.
but not using the Ruby DBI implementation.

I've never used ADO, but, here's the connect method:


def connect(dbname, user, auth, attr)
# connect to database

handle = WIN32OLE.new('ADODB.Connection')
handle.Open(dbname)
handle.BeginTrans() # start new Transaction

return Database.new(handle, attr)
rescue RuntimeError => err
raise DBI:atabaseError.new(err.message)
end


Unless I am badly understanding, you need to set a property on that handle
that is returned, right?

The actual dbi handle that is ultimately returned has a handle() method on
it which will return the lower level handle. I'd check what methods you
have available on it, but I would bet that once you are at that level,
you'll see what you need to set the CommandTimeOut property on the handle.


Kirk Haines