DBI Timeout


#1

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?


#2

On Feb 28, 10:17 am, “gregarican” removed_email_address@domain.invalid wrote:

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 :frowning:


#3

On Thu, 1 Mar 2007, gregarican wrote:

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 :frowning:

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

Kirk H.


#4

On Feb 28, 12:48 pm, removed_email_address@domain.invalid wrote:

On Thu, 1 Mar 2007, gregarican wrote:

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 :frowning:

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

Kirk H.

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.


#5

On Thu, 1 Mar 2007, gregarican wrote:

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::DatabaseError.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 H.