Re: AbstractJdbc2Statement.setFetchSize()

Oliver Jowett wrote:
> On Thu, Oct 16, 2003 at 06:09:47PM -0400, Rich Cullingford wrote:
>
>>All,
>>I had hoped to use this method, which is implemented in PG 7.4, to limit
>>the number of rows retrieved (for display purposes) out of some massive
>>datasets we have. I know the JDBC spec says that this is just a hint,
>>but calling the method on a Statement doesn't seem to have any effect on
>>the SELECT. It still seesm to try to load the whole dataset.
>>
>>Is there any way to avoid cursored FETCH approaches to this problem?
>
>
> Recent drivers should transparently transform queries into a cursor-based
> form if you set a non-zero fetchsize. However there are a few gotchas:
>
>  - it doesn't do this if (JDBC-level) autocommit is on
>  - it doesn't do this if you use a PreparedStatement with parameters
>  - it tries to do this even if the statement is something other than a
>     SELECT statement, and gets horribly confused in the process.
>
> I have patches for the second two if needed; they probably won't reach CVS
> until after 7.4 is out, though, since they're a bit invasive.

Oliver,
Thanks for your help. I checked that the Connection I'm using had
autoCommit=false, and that the Statement (not PreparedStatement with
parameters) had a small fetchsize (200 rows) just before sending an
executeQuery() for a SELECT against a 7 million-record dataset. It just
sat there. The query works against smaller datasets.

I tried attaching a fetchsize to the ResultSet, but that lead to an
exception (as expected, since one is apparently expected to provide a
custom cursored-fetch in this case).

I wonder what I'm doing wrong...
                                   Rich C.