From: Tom Sawyer <transami@...>
Date: 2003-01-02T22:08:23+09:00
Subject: Re: Ruby DBI

On Thursday 02 January 2003 05:53 am, Gavin Sinclair wrote:
> 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.

gavin, i'll take that bet! :-)

%Q{
 CREATE TABLE je_d
 ( record        SERIAL PRIMARY KEY,
   je_ref        int REFERENCES je(record) ON DELETE CASCADE,
   debit         float DEFAULT 0,
   credit        float DEFAULT 0,
   account_ref   int REFERENCES acct(record),
   pr            text DEFAULT '',
   sort          int DEFAULT 0
 );
 INSERT INTO je_d (je_ref, debit, credit, account_ref, sort) VALUES (1, 0, 0, 
NULL, 1);
 INSERT INTO je_d (je_ref, debit, credit, account_ref, sort) VALUES (1, 0, 0, 
NULL, 2);
}

run it all the time. 3 statements in one. well, at least it works against 
postgresql DBD. what was that, $250, did you say? :-)

really think prep'd statements are more efficient? do they really do anything 
special? i thought they just did a gsub before sending off the sql, nothing 
more.

well, maybe i'll throw a quck test together. not much for profiling, but it 
shouldn't be too hard. just two big long repretitve scripts and a watch.

-transami

-transami