Обсуждение: Question about refcursors

Hi,

I'm trying to add support for refcursors in my JDBC tool and while trying out
the examples in the docs
(<http://jdbc.postgresql.org/documentation/82/callproc.html#callproc-resultset>)

I stumbled over something where I'm not sure whether this is intended or simply
something which was never thought of.


My generic code to call the procedure does basically a prepareCall() (but not
with the "? =" as I cannot distinguish between a function and a procedure at
that moment. And it seems to be working.

Now after running the procedure (using execute()) I'm (generically) calling
getResultSet() on the statement object. And indeed the driver does return a
ResultSet, but it seems that the actual result of the call is "hidden" inside
one of the columns of the resultset. If I do a getObject() on that resultset I
get another result set which indeed is the result of my SELECT in the procedure.

My question: is there a reason why the "real" result cannot be obtained using
getResultSet() but is wrapped into another ResultSet?

I can check the database to find out if a function or a procedure is called, but
it would be really cool, if the driver could handle that for me.

I am aware that I'm not using the calls 100% according to the API and the
documentation, but still :)

Cheers
Thomas

On Sat, 27 Oct 2007, Thomas Kellerer wrote:

> Now after running the procedure (using execute()) I'm (generically) calling
> getResultSet() on the statement object. And indeed the driver does return a
> ResultSet, but it seems that the actual result of the call is "hidden" inside
> one of the columns of the resultset. If I do a getObject() on that resultset
> I get another result set which indeed is the result of my SELECT in the
> procedure.
>
> My question: is there a reason why the "real" result cannot be obtained using
> getResultSet() but is wrapped into another ResultSet?
>

Consider what should be returned by:

SELECT 1, 'some text', func_returning_refcursor();

Or a function returning a refcursor as an out parameter.

Or a function returning a setof refcursor.

Just like you don't know exactly what you're executing, neither does the
driver.

Kris Jurka

Kris Jurka wrote on 27.10.2007 01:20:
>> Now after running the procedure (using execute()) I'm (generically)
>> calling getResultSet() on the statement object. And indeed the driver
>> does return a ResultSet, but it seems that the actual result of the
>> call is "hidden" inside one of the columns of the resultset. If I do a
>> getObject() on that resultset I get another result set which indeed is
>> the result of my SELECT in the procedure.
>>
>> My question: is there a reason why the "real" result cannot be
>> obtained using getResultSet() but is wrapped into another ResultSet?
>>
>
> Consider what should be returned by:
>
> SELECT 1, 'some text', func_returning_refcursor();
>
> Or a function returning a refcursor as an out parameter.
>
> Or a function returning a setof refcursor.
>
> Just like you don't know exactly what you're executing, neither does the
> driver.

Makes sense, didn't think about the SELECT usage, as I was thinking about a
{call somefunc()} usage

Cheers
Thomas

On Sat, 27 Oct 2007, Thomas Kellerer wrote:

> Makes sense, didn't think about the SELECT usage, as I was thinking about a
> {call somefunc()} usage
>

somefunc may have multiple output parameters which generates many of the
same situations select does, so even knowing that you've got a
CallableStatement doesn't help much.

Kris Jurka