Re: Getting a ResultSet for a refcursor element.

Nic,

I am a bit swamped right now. So I haven't had a chance to look at this
in any greater detail. However in response to your comments see below.

Nic Ferrier wrote:
> 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?
>
It can't be done with a result set since the point of a refcursor it to
pass around the pointer to the cursor. It is the final function that
will take the pointer and do the fetching. If the rows have already
been fetched that code will not work.

So you have function a() that returns a refcursor. It has some black
box implementation that is creating a query and returning the refcursor
to that query. Then you have function b(refcursor) that takes a
refcursor and fetches the results and processes them. So when the
refcursor is passed to function b() the assumption is that function b()
can get the rows from the refcursor.

>
>
>>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.
>

I agree that a ResultSet represents the results of a query, but a
refcursor is not the results of a query. It is a pointer to the query
itself. Thus the 'ref' in the name. The point to a refcursor is the
ability to only pass the pointer around and to only at the end use the
pointer to get the results. It can't be assumed that the caller who
gets a refcursor actually wants the results, he may just want the
pointer so that it can be passed on to other functions.

It has been a while, but I beleive that Oracle has two different types
of cursors in plsql, one that is similar in nature to refcursors (i.e. a
pointer to a query) and a second that more or less is the result set. I
want to spend some time going through the Oracle doc to understand the
different functionality in this area.

>
>
>>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).
>

Just because Oracle does it one way doesn't mean that is the correct way
to do it. However, it certainly does suggest that the Oracle way should
given a lot of consideration.

>
> Nic
>
>

Basically I need to spend some more time investigating. However I am
still leaning towards a wrapper object that is just the pointer to the
query (the 'ref' in refcursor) that has a method on it to get the result
set. So instead of the implementation you have suggested:

ResultSet rset2 = (ResultSet)set.getObject(x);

it would be:

ResultSet rset2 = ((PGRefCursor)rset.getObject(x)).getResultSet();

or possibly:

ResultSet rset2 = ((PGResultSet)rset).getRefCursor(x).getResultSet();

thanks,
--Barry