Thread: No. of rows on result set

Hello List,

Recently upgraded to Postgresql 8.0.3, JDBC Driver 8.0 Build 311 and
realized that I can no longer use the last() method for ResultSet
because of the default TYPE_FORWARD_ONLY.  Several of our apps use this
to find the number of rows on a result set. Looking at the archives, my
options would be to (1) do a separate SELECT COUNT(*) . . ., (2) change
result set type to TYPE_SCROLL_INSENSITIVE, (3) replace arrays with
something else that will not require the size of the result set, or (4)
a better solution I haven't thought of : ).

Sorry for the newbie question. I'm inclined to just use
TYPE_SCROLL_INSENSITIVE, but I want to do this the right way (not just
the easy way) and I'm not sure if it has any repercussions.

Thank you very much.

dianne

Dianne Yumul wrote:
> Hello List,
>
> Recently upgraded to Postgresql 8.0.3, JDBC Driver 8.0 Build 311 and
> realized that I can no longer use the last() method for ResultSet
> because of the default TYPE_FORWARD_ONLY.

Right, you're not meant to use last() on TYPE_FORWARD_ONLY per the JDBC
spec, and the driver implementation now means we can't support it..

> Several of our apps use this
> to find the number of rows on a result set. Looking at the archives, my
> options would be to (1) do a separate SELECT COUNT(*) . . ., (2) change
> result set type to TYPE_SCROLL_INSENSITIVE, (3) replace arrays with
> something else that will not require the size of the result set, or (4)
> a better solution I haven't thought of : ).

If you need to use last(), then TYPE_SCROLL_INSENSITIVE is the way to go.

Note that this means the driver will retrieve the entire resultset in
one go rather than possibly using cursors (if you've set fetchsize and
have autocommit off) -- you might have problems with big resultsets. If
so I'd go with COUNT.

-O

On Jul 14, 2005, at 5:10 PM, Oliver Jowett wrote:
> If you need to use last(), then TYPE_SCROLL_INSENSITIVE is the way to
> go.
>
> Note that this means the driver will retrieve the entire resultset in
> one go rather than possibly using cursors (if you've set fetchsize and
> have autocommit off) -- you might have problems with big resultsets. If
> so I'd go with COUNT.

Ok, looking at the apps, the query either returns one row or a large
resultset. But using TYPE_SCROLL_INSENSITIVE should not be a problem
because fetchsize is not set and autocommit is not off.

Thank you so much for the great help.

Dianne