From: Gavin Sinclair <gsinclair@...>
Date: 2003-01-02T21:53:01+09:00
Subject: Re: Ruby DBI

On Thursday, January 2, 2003, 11:38:01 PM, Tom wrote:

> speaking of Ruby DBI, does anyone know if there is a limitation to the size of 
> the sql executed by DBI?

> in my case i'm thinking of sending multiple queries at once:

> sql = %Q{
>                 UPDATE table SET a='1' WHERE x=1;
>                 UPDATE table SET a='2' WHERE x=2;
>                 UPDATE table SET a='3' WHERE x=3;
>                 ...
>                 }

> rahter than doing each of these individually. will i hit a cap on this type of 
> execution? does it depend on the DBD or database itself?

I doubt if that SQL will actually work, since it is many statements,
and I'd bet $250 that DBI will only allow one statement at a time.
The smart money is on:

   dbh.prepare("update table set a = ? where x = ?") do |sth|
     records.each do |rec|
       sth.execute(rec.a, rec.x)
     end
     dbh.commit
   end

i.e. a prepared statement will let you handle multiple statements
gracefully, and more efficiently (depending on DB) than sending
boatloads of hardcoded SQL at it.
   
Cheers,
Gavin