Re: Getting a ResultSet for a refcursor element.

Did you have any more thoughts on this Barry?

I didn't see a response to my last email about it. Here's some more
thoughts on the points you razed.

Barry Lind <barry(at)xythos(dot)com> writes:
> Nic,
>
> I don't think this is the correct approach. I think a better approach
> would be to return a pg specific object (lets call it PGrefcursor). The
> object would have at least the following two methods: getRefCursorName()
> and getResultSet(). The reason I think this is a better approach is
> then you can turn around and use the PGrefcursor object on a setObject()
> call to bind the refcursor to a different function call. So you can
> have a function that returns a refcursor and another that takes a
> refcursor and you can get the refcursor object from one call and pass it
> onto the other.

Why couldn't we do that with a ResultSet?

> Now it is true that you could do this today using
> getString()/setString() but that isn't very intuitive.
>
> The other reason I don't like returning a result set directly from
> getObject is that it doesn't seem to follow the same pattern as all the
> other objects that are being returned. You are losing the distinction
> that the refcursor is a pointer to a result set, not the actual result
> set itself.

I'm not sure about the validity of this claim. IMO the ResultSet
object "represents" the results of a query. That doesn't include any
implementation expectation. eg: pgsql retrieves all values returned
from the query for each RS but Oracle doesn't, it uses a cursor and
fetches the results in batches (this is the approach I'm playing
with for postgres, though I actually prefer the "get it all at once"
system).

As such, using a ResultSet to represent a cursor's seems to me just
as valid as using a ResultSet to respresent a non-cursor's resutls.

> Finally, does anyone know how other databases' jdbc drivers deal with
> this type of functionality? I would rather try to follow an existing
> example of how someone else has done this then to go it alone and build
> our own mechanism. Since I know Oracle has refcursors, how does oracle
> expose them through jdbc?

As I said before, oracle does it as I have suggested. I think there's
an important porting issue here. One of the reasons I wrote the patch
is that I have some code that I want to port from ora to pgsql and it
uses cursor based procs extensively. The P*SQL is easy to move, but
the Java was impossible (until my patch).

Nic