Re: SQL-Invoked Procedures for 8.1

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

Not a user of JDBC, but this is fairly common in the ADO/ADO.NET world
with MS SQL Server as well (not sure about other dbs and .NET - I'ev
only used it with mssql and pgsql)... As for an example, something along
the line of (though in my cases usually with a *lot* more parameters):

--
CREATE PROCEDURE get_info_for_user(@userid varchar(16)) AS
SELECT something FROM contentstable WHERE userid=(at)userid

SELECT whatever,somethingelse FROM anothertable WHERE
something=anything
--

You get the point :-)
Then in my .net code I'd do a simple:
SqlDataReader rdr = cmd.ExecuteReader();
... process first result ...
rdr.NextResult();
... process second result...

This is the very simple case. In this case, the only thing you gain is
less server roundtrips and less parsing steps.

In an example of a more complex case, the first part of the stored
procedure will do some complex (and expensive) work to get to a
resulting variable. This variable is then applied to several different
queries after each other, and their respective resultsets are returned
to the client. In this case, you save having to run that complex logic
more than once. (You could cache the result at the client, but if you're
going down that path then you don't need stored procs at all.. It is
usually necessary to keep it in the db to maintain abstraction)

//Magnus