Re: SQL-Invoked Procedures for 8.1

On Thu, 23 Sep 2004, Grant Finnemore wrote:

> Quoth the JDBC spec:
>
> public interface CallableStatement
> extends PreparedStatement
>
> The interface used to execute SQL stored procedures. The JDBC API provides a
> stored procedure SQL escape syntax that allows stored procedures to be called
> in a standard way for all RDBMSs. This escape syntax has one form that includes
> a result parameter and one that does not. If used, the result parameter must be
> registered as an OUT parameter. The other parameters can be used for input,
> output or both. Parameters are referred to sequentially, by number, with the
> first parameter being 1.
>
> {?= call <procedure-name>[<arg1>,<arg2>, ...]}
> {call <procedure-name>[<arg1>,<arg2>, ...]}
>

I didn't see this in my copy of the spec, which is version 3.0 FR (final
release). Still, I think we're fine. As I said before, what I think the
spec had in mind was allowing functions to be called from the
callablestatement stuff and have their output put in the first OUT
variable.

This is... reasonable. Compare the stuff Neil's been working on with
"bare" function calls in PL/PgSQL and Tom (I think) saying that it might
be reasonable to just issue func(); as an SQL query: no CALL, no SELECT.

>
> IN parameter values are set using the set methods inherited from
> PreparedStatement. The type of all OUT parameters must be registered prior to
> executing the stored procedure; their values are retrieved after execution via
> the get methods provided here.
>
> A CallableStatement can return one ResultSet object or multiple ResultSet
> objects. Multiple ResultSet objects are handled using operations inherited from
> Statement.

I don't get this multiple ResultSet stuff. All I can think of is that the
spec has this in mind:

CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");

or

CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");

or some other permutation.

I see plenty of references to multiple ResultSets but I cannot find an
example or information on how to generate one.

Gavin