# DBI Timeout

**URL:** <https://www.ruby-forum.com/t/dbi-timeout/87155>\
**Category:** Ruby\
**Created:** [February 28, 2007, 4:20pm UTC](https://www.ruby-forum.com/t/dbi-timeout/87155 "2007-02-28T16:20:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![gregarican](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7feea3/32.png) [@gregarican](https://www.ruby-forum.com/u/gregarican)\
**Post date:** [February 28, 2007, 4:20pm UTC](https://www.ruby-forum.com/t/dbi-timeout/87155/1 "2007-02-28T16:20:58Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![gregarican](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7feea3/32.png) [@gregarican](https://www.ruby-forum.com/u/gregarican)\
**Post date:** [February 28, 2007, 6:20pm UTC](https://www.ruby-forum.com/t/dbi-timeout/87155/2 "2007-02-28T18:20:29Z")

</div>

On Feb 28, 10:17 am, “gregarican” [removed\_email\_address@domain.invalid](mailto: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 ☹

---

<div class="post-metadata">

**Author:** ![gregarican](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7feea3/32.png) [@gregarican](https://www.ruby-forum.com/u/gregarican)\
**Post date:** [February 28, 2007, 6:48pm UTC](https://www.ruby-forum.com/t/dbi-timeout/87155/3 "2007-02-28T18:48:35Z")

</div>

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 ☹

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

Kirk H.

---

<div class="post-metadata">

**Author:** ![gregarican](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7feea3/32.png) [@gregarican](https://www.ruby-forum.com/u/gregarican)\
**Post date:** [February 28, 2007, 6:55pm UTC](https://www.ruby-forum.com/t/dbi-timeout/87155/4 "2007-02-28T18:55:39Z")

</div>

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 ☹
> 
> 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.

---

<div class="post-metadata">

**Author:** ![gregarican](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7feea3/32.png) [@gregarican](https://www.ruby-forum.com/u/gregarican)\
**Post date:** [February 28, 2007, 7:12pm UTC](https://www.ruby-forum.com/t/dbi-timeout/87155/5 "2007-02-28T19:12:41Z")

</div>

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.