Re: ResultSet construction and byte arrays

On Fri, 6 Feb 2004, Jan Oksfeldt Jonasen wrote:

> I can see from the archives this has been up more than once, but I'd still
> like to take a stab at it.
> Construction of ResultSets based on searches with many results or based on
> large tables does have some issues, the most noticable one being loading
> everything found into memory as byte arrays.
>
> Are there any ideas on how to fix this? Are there plans to look into it?

The 7.4 driver (which can be used against older dbs) has support for using
cursors behind the scenes to retrieve large ResultSets when hinted to do
so via setFetchSize.  This only works for ResultSets of TYPE_FORWARD_ONLY
though.  I have noted the potential obstacles to making this functionality
available for scrollable result sets here:

http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00288.php

> To fix this I created wrappers for multiple java.sql interfaces, this way
> making sure to have full control over which ResultSet implementation was
> being used. The idea was to utillize an expression similiar to (taken from
> an article at OTN):
> from (
>   select name.*, rownum rnum
>   from ( <everything in a query, including order by clause> ) name
>   where rownum <= MAX_ROWS
> )
> where rnum >= MIN_ROWS
>
> If it's possible to make an expression like the above in Postgres, I think
> that could also be the solution to not loading everything in a result set.
> The result set should be enabled to re-query the database once some row
> above its max was requested. So, how would I make such an expression?

See the LIMIT/OFFSET clause:
http://www.postgresql.org/docs/current/static/queries-limit.html

>
> As a last alternative, are there any commercial drivers available which
> aren't just wrappers for the standard one? The ones I can search out at Sun
> just seems to be.
>

There is another jdbc driver available here:

http://sourceforge.net/projects/jxdbcon/

but I have never used it.

Kris Jurka