Re: SQL-Invoked Procedures for 8.1

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>, ...]}

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.

For maximum portability, a call's ResultSet objects and update counts should be
processed prior to getting the values of output parameters.

Regards,
Grant

Gavin Sherry wrote:
> On Thu, 23 Sep 2004, Grant Finnemore wrote:
>
>
>>Hi Gavin,
>>
>>Although I have not read the SQL 2003 spec, my recollection of other database
>>products' stored procs differed from your description in one significant way,
>>namely that they could return multiple (and varied) sets of rows.
>>
>>For example, a stored proc could do a SELECT over foo and then a SELECT over
>>bar and return the tuples of both foo and bar. (each having different column
>>counts, types, etc)
>>
>>The JDBC interfaces would appear to illustrate this point.
>>(In CallableStatement)
>>
>>"A CallableStatement can return one ResultSet object or multiple ResultSet
>>objects. Multiple ResultSet objects are handled using operations inherited
>>from Statement."
>
>
> I read the JDBC 3.0 spec and didn't see this. Like with all specs, some
> details are hard to find. However, from what I've seen in the spec, I
> think they have functions in mind here. That being said, I can't think how
> SQL2003 would allow such behaviour. If you could show us an example,
> that'd be great.
>
> Thanks,
>
> Gavin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>