[R-sig-DB] Opening multiple result sets

[Seth Falcon]

"Paul Dlug" <paul.dlug using gmail.com> writes:

> I'm rather new to using R DBI so please excuse me if this is beyond
> basic. I don't seem to be able to open more than one resultset at
> time, I'm trying the following:

The RMySQL sources contain the following comment:

   /* Do we have a pending resultSet in the current connection?  
    * MySQL only allows  one resultSet per connection.
    */

So I don't think you can do that.  You could try:

 - opening more than one connection

 - using a subselect.  I'm not certain that MySQL supports this, but
   you can try: SELECT ... from (SELECT ... from ...) WHERE ...

> When I try to execute it I get the error:
> Error in mysqlExecStatement(conn, statement, ...) :
> 	RS-DBI driver: (connection with pending rows, close resultSet before
> continuing)
>
> What I'm trying to accomplish is operating on the results of the first
> query one row at a time and using the returned values to perform
> another query and analyze the results. The first query is going to
> return a large amount of data so I don't really want to fetch all rows
> into memory up front, close the result set and proceed.
>
> Any help/suggestions would be greatly appreciated.

Perhaps another workaround might be LIMIT and OFFSET?

+ seth

-- 
Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center
http://bioconductor.org