Re: To much data for a ResultSet - Mailing list pgsql-jdbc

Ben,

Cursors are supported.  My guess as to your problem (since you didn't
send a full code example of what you are doing) is that you need to
issue multiple 'fetch' statements.  Each fetch statement will return its
own result set.  So when you have used up the results from one fetch
statement, you need to execute another.  I have provided an example
below which I have hacked from some of my own code.

thanks,
--Barry



     PreparedStatement l_stmt = null;
     PreparedStatement l_stmtFetch = null;
     PreparedStatement l_stmtClose = null;
     ResultSet l_rset = null;
     try { //exception
       try { //finally
         //open the cursor
         l_stmt = m_dbcon.prepareStatement("DECLARE FOO CURSOR FOR
SELECT BAR FROM FOOBAR");
         l_stmt.execute();

         l_stmtFetch = m_dbcon.prepareStatement("FETCH FORWARD 10 FROM
FOO");
         while (true) {
           //perform a fetch from the cursor (possibly multiple fetches
will be done)
           l_rset = l_stmtFetch.executeQuery();
           l_rows = 0;
           while (l_rset.next()) {
             String l_bar = l_rset.getString(1);
             //do something useful with the data
             l_rows++;
           }

           l_rset.close();
           l_rset = null;

           if (l_rows == 0) {
             //no more rows, so we are done
             break;
           }
         }

         //don't forget to close the cursor
         l_stmtClose = m_dbcon.prepareStatement("CLOSE FOO");
         l_stmtClose.execute();
       } finally {
         if (l_rset != null) {
           l_rset.close();
         }
         if (l_stmt != null) {
           l_stmt.close();
         }
         if (l_stmtFetch != null) {
           l_stmtFetch.close();
         }
         if (l_stmtClose != null) {
           l_stmtClose.close();
         }
       }
     } catch (SQLException l_se) {
       //do something useful here
     }




ben.suffolk@orange.co.uk wrote:

> Hello,
>
> I am retrieveing a lot of data from a table and it results in a
> java.lang.OutOfMemoryError and so I figured I need to make sure I don;t get all
> the data from the database back in one go.
>
> I have tried setting a cursorName and this does indeed only return the first
> part of the data into the ResultSet. However I can not seem to find a way to
> retrieve the remaining part of the data. I have tried using the following
> command :-
>
>     if(select.getMoreResults())
>      rs = select.getResultSet();
>
> However there are never are never any more ResultsSets avaliable.
>
> Am I doing something wrong or are cursors not implemented in this version of the
> JDBC drivers (jdbc7.1-1.2.jar)?
>
> An obvious bodge for me (as the data in the database is not changing on a
> regular basis) is to use LIMIT in the SELECT command, however I would rather do
> it a nicer way than that!
>
> Regards
>
> Ben
>
>
>
> *******************************************************************************
> Important. This E-mail is intended for the above named person and may be
> confidential and/or legally privileged. If this has come to you in error you
> must take no action based on it, nor must you copy or show it to anyone; please
> inform the sender immediately.
> *******************************************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>