John

 

Thank you for your detailed response(s).

 

I think a closer look at the API docs would have told me the answer J Apparently the ResultSet will allow me to do what I want to do;  stream data from the database to a web client without too much memory impact. Somehow, my mental model was wrong—I assumed the result set represented the complete data set.

 

I have a grails plugin that I wrote for our project at work that simplifies the construction and execution of SQL queries. But the current implementation gathers the whole result set and returns the data to the user.

 

It looks like I can simply expose the Sql.eachRow closure and stream the data more-o- less directly into the response output stream and not concern myself too much about the number of rows.

 

Great!

 

Out of curiosity, if the eachRow closure partially exposes the cursor, what did you need the complete cursor for?  Did you need a scrollable and updateable resultset?

 

Cheers,

John Kline

 

From: John Bito [mailto:jwbito@gmail.com]
Sent: Tuesday, February 09, 2010 11:58 AM
To: Kline, John; user@groovy.codehaus.org
Subject: Re: Extending groovy SQL to gain access to cursors

 

Realizing there may be an incorrect assumption:

Situations where returning the cursor from the Sql method is part of an optimal solution are not that common. If the apparent problem is the memory needed to process the result, the groovy.sql.Sql#eachRow method gives the closure (as the only parameter - 'it') access to the ResultSet (with GroovyResultSet extension).  The closure can, of course, write to a file or directly to the HTTP response.  Since I'm more familiar with Rails than Grails, I can't say off-hand whether writing to the HTTP response or writing to a file will be better - Rails will hold the response in memory; that's why I think of writing to a file.

On Mon, Feb 8, 2010 at 14:33, John Bito <jwbito@gmail.com> wrote:

The patch I submitted is in Groovy 1.6.5 and 1.7.0 (I'm using 1.7.0, so I can't say how exactly things work with 1.6.5).  A Couple of things you will have to deal with in a Grails app are threading and session life-cycle.  One approach is to use a cursor to write a local file and allow the web server to stream the file for downloading.

If you want access to the cursor, you may subclass groovy.sql.Sql. The protected method groovy.sql.Sql#executePreparedQuery will give you access to the implementation of java.sql.ResultSet from JDBC. In order to make that groovy, you wrap it in a proxy:

new GroovyResultSetProxy(theResultSet).getImpl();

If there's work to do, I can be available on a consulting basis :)

John

 

On Mon, Feb 8, 2010 at 14:12, Kline, John <jkline@aclara.com> wrote:

John,

 

I saw this message thread archived on codehaus (http://archive.codehaus.org/lists/org.codehaus.groovy.user/msg/3ae83b000908031408l465f01abue93d1f4783e4fa01@mail.gmail.com )  where you discussed adding cursor support to Groovy Sql.

 

I was looking for something similar for my grails application where I may need to stream tens of thousands of rows as users “download” large record sets. A cursor seems much more efficient and eliminates the risk of OOM errors.

 

I was wondering how much luck you had with this? The Groovy Sql docs are pretty thin…

 

 

Thanks,

John Kline