Hi Oliver,

Thanks for pointing me to this discussion. From what I can tell, work must have been done on the postgres backend to improve support for cursors. I have been using cursors with fairly complex queries (joins and nested subqueries and order bys) without any problems.

I went ahead and patched the JDBC jar from the CVS head on gborg. This patch should enable scrollable cursor support, and provide absolute() w/o loading all results into the JDBC buffer. Unfortunately, the patch involves changing some core aspects of the driver. I have not reviewed the existing code thoroughly, so I could have introduced problems. I have also not tested extensively. However, the patch works for me, and I could use the patched driver with existing applications. Is there a test suite for the driver? At this point I hope to get some feedback.

Here is a summary of the patch:
    AbstractJdbc1ResultSet
        - added "buffer_row" and "cursor_row" to track cursor and buffer position. remove "current_row", and use these instead.
        - use a new variable "needsRefresh" to control reload state explicitly
        - convert next() method to load on demand instead of pre-load. the former method does not work with cursor positioning, or would require a hack to load data twice, and support multiple modes of loading.

    Abstract1Jdbc1Statement
        - to prevent pre-load, I made the while loop over reallyResultSet() occur only if cursors are disabled. I don't think this is needed in this context for cursors, but I'm not certain.
        - added DECLARE SCROLL CURSOR to handle scrollable case.

    AbstractJdbc2ResultSet
        - use a new variable "internalIndex" in AbstractJdbc2ResultSet to track absolute position, to avoid overlapping repositioning.
        - implement absolute() as FETCH ABSOLUTE. relative() uses this code, so it should be supported as well.

thanks,
Indra

Oliver Jowett wrote:

Indra Heckenbach wrote:

Thanks for the info. So the implementation is functional, but does so by preventing cursors. Unfortunately, I am dealing with a large result set, so I need to use cursors. I started modifying the jdbc driver. Any other suggestions?

There was a discussion about implementing this a couple of months ago that might be useful to you, starting at: http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00288.php

-O