From: Michael Neumann <neumann@...>
Date: 2001-12-07T21:39:52+09:00
Subject: [ruby-talk:27805] Re: DBI and large result sets

 JamesBritt wrote:
> I'm starting to use Ruby DBI, and I'm wondering about its use when processing
> very large result sets.
> 
> Given that DBI sits on top of various DB drivers, is there some way to use DBI
> to run a query and process each record set without necessarily having to hold
> the entire result set in memory?  Or is DBI too abstract to allow you to make
> any assumptions about that?

No, it's possible.

> DBI::StatementHandle has fetch_many, but I think it works on a local store of
> the entire result set (whereas I want it to pull the data from the database
> only as I request a number of rows).
> 
> Basically I want the SQL query to behave like a stream, not a single, huge,
> chunk o' data, but not have to tie the code to a specific database (if
> possible).

use StatementHandle#fetch (or #each):

  dbh.execute("SELECT * FROM EMPLOYEE") do |sth|

    sth.fetch do |row|
      p row
    end

  end

Only fetch_all or fetch_many reads all (a part) rows in.

Regards,

  Michael


-- 
Michael Neumann
merlin.zwo InfoDesign GmbH
http://www.merlin-zwo.de