> > 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=@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
 

Thu, 23 Sep 2004 09:57:55 +0200
"Magnus Hagander" <mha@sollentuna.net>
Re: SQL-Invoked Procedures for 8.1
Hi Magnus,

Yes, this is the situation that I have been thinking about. Specifically
when a single stored procedure returns many recordsets.

Perhaps I should also clarify that the "spec" I have been using is
the JDK javadoc documentation.

Using java with Magnus' procedure:
   CallableStatement cs = connection.prepareCall("call get_info_for_user ?");
   cs.setString(1, "test");
   if(cs.execute()) {
      ResultSet rs = cs.getResultSet();
      while(rs != null) {
        // Process rs
      }
   }

Regards,
Grant

Magnus Hagander wrote:
[snip]
> 
> 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=@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...
> 

Thu, 23 Sep 2004 10:34:12 +0200
Grant Finnemore <grantf@guruhut.co.za>
Re: SQL-Invoked Procedures for 8.1
On N, 2004-09-23 at 11:34, Grant Finnemore wrote:
> Hi Magnus,
> 
> Yes, this is the situation that I have been thinking about. Specifically
> when a single stored procedure returns many recordsets.

IIRC support for returning multiple recordsets was removed from
postgresql fe-be protocol years ago as "nobody ever needs it" ;)

---------------
Hannu


Thu, 23 Sep 2004 13:58:00 +0300
Hannu Krosing <hannu@tm.ee>
Re: SQL-Invoked Procedures for 8.1
Hannu Krosing wrote:

>On N, 2004-09-23 at 11:34, Grant Finnemore wrote:
>  
>
>>Hi Magnus,
>>
>>Yes, this is the situation that I have been thinking about. Specifically
>>when a single stored procedure returns many recordsets.
>>    
>>
>
>IIRC support for returning multiple recordsets was removed from
>postgresql fe-be protocol years ago as "nobody ever needs it" ;)
>  
>
Until recently I would have said the same thing.

A couple of months ago I started a new job where they are MS-SQL based, 
and they do have several procedures that actually do return multiple 
recordsets from a single procedure. However this is the only time that 
I've ever seen any procedure actually require it.

Peter


Thu, 23 Sep 2004 13:22:59 +0100
Peter Mount <peter@retep.org.uk>
Re: SQL-Invoked Procedures for 8.1
On N, 2004-09-23 at 15:22, Peter Mount wrote:
> Hannu Krosing wrote:
> 
> >On N, 2004-09-23 at 11:34, Grant Finnemore wrote:
> >  
> >
> >>Hi Magnus,
> >>
> >>Yes, this is the situation that I have been thinking about. Specifically
> >>when a single stored procedure returns many recordsets.
> >>    
> >>
> >
> >IIRC support for returning multiple recordsets was removed from
> >postgresql fe-be protocol years ago as "nobody ever needs it" ;)
> >  
> >
> Until recently I would have said the same thing.
> 
> A couple of months ago I started a new job where they are MS-SQL based, 
> and they do have several procedures that actually do return multiple 
> recordsets from a single procedure. However this is the only time that 
> I've ever seen any procedure actually require it.

Actually the original support was inhetited from Postgres4.2 where
PostQuel had native support for it.

It was probably decided that SQL (which replaced PostQuel) would not
generate such things.

------------
Hannu


Thu, 23 Sep 2004 15:39:11 +0300
Hannu Krosing <hannu@tm.ee>
Re: SQL-Invoked Procedures for 8.1
Hannu Krosing wrote:

>On N, 2004-09-23 at 15:22, Peter Mount wrote:
>  
>
>>Hannu Krosing wrote:
>>
>>    
>>
>>>On N, 2004-09-23 at 11:34, Grant Finnemore wrote:
>>> 
>>>
>>>      
>>>
>>>>Hi Magnus,
>>>>
>>>>Yes, this is the situation that I have been thinking about. Specifically
>>>>when a single stored procedure returns many recordsets.
>>>>   
>>>>
>>>>        
>>>>
>>>IIRC support for returning multiple recordsets was removed from
>>>postgresql fe-be protocol years ago as "nobody ever needs it" ;)
>>> 
>>>
>>>      
>>>
>>Until recently I would have said the same thing.
>>
>>A couple of months ago I started a new job where they are MS-SQL based, 
>>and they do have several procedures that actually do return multiple 
>>recordsets from a single procedure. However this is the only time that 
>>I've ever seen any procedure actually require it.
>>    
>>
>
>Actually the original support was inhetited from Postgres4.2 where
>PostQuel had native support for it.
>
>It was probably decided that SQL (which replaced PostQuel) would not
>generate such things.
>  
>
I'm not so sure as both JDBC & ODBC have implicit support for it and 
they are younger than SQL.

ie: with Prepared/Callable statements in JDBC you are supposed to check 
for the existence of any other ResultSets when reading the results... 
this was what I had to do with CallableStatement last week with MSSQL.

Peter


Thu, 23 Sep 2004 13:45:51 +0100
Peter Mount <peter@retep.org.uk>
Re: SQL-Invoked Procedures for 8.1
Hannu Krosing <hannu@tm.ee> writes:
> IIRC support for returning multiple recordsets was removed from
> postgresql fe-be protocol years ago as "nobody ever needs it" ;)

The protocol can still do it, and so can the backend, but it will
certainly break most if not all clients.  Here's an example:

regression=# create table surprise(f1 text);
CREATE TABLE
regression=# create rule r1 as on insert to surprise do
regression-# ( select 'hello' ; select 'how are you' );
CREATE RULE
regression=# insert into surprise values ('boo');
  ?column?
-------------
 how are you
(1 row)

regression=#

The 'hello' result was in fact computed and sent by the backend, but it
was discarded in libpq (see the documentation about PQexec: only the
last resultset returned by a querystring is returned to the caller).
psql could have printed both results, but it would need to use
PQsendQuery/PQgetResult instead of PQexec.

			regards, tom lane

Thu, 23 Sep 2004 10:54:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SQL-Invoked Procedures for 8.1 
Tom Lane wrote:

> regression=# create rule r1 as on insert to surprise do
> regression-# ( select 'hello' ; select 'how are you' );
> CREATE RULE

> The 'hello' result was in fact computed and sent by the backend, but it
> was discarded in libpq (see the documentation about PQexec: only the
> last resultset returned by a querystring is returned to the caller).
> psql could have printed both results, but it would need to use
> PQsendQuery/PQgetResult instead of PQexec.

Yikes. I thought this couldn't happen under the V3 extended query protocol.

The JDBC driver currently sends Describe/Execute and expects exactly one 
of RowDescription/NoData followed by zero or more DataRows followed by 
one of CommandComplete/EmptyQueryResponse/PortalSuspended. This seems 
wrong if there could be multiple resultsets from a single Execute.

How can clients distinguish multiple resultsets if they're using the 
extended query protocol?

-O

Fri, 24 Sep 2004 08:40:43 +1200
Oliver Jowett <oliver@opencloud.com>
Re: SQL-Invoked Procedures for 8.1
Oliver Jowett <oliver@opencloud.com> writes:
> The JDBC driver currently sends Describe/Execute and expects exactly one 
> of RowDescription/NoData followed by zero or more DataRows followed by 
> one of CommandComplete/EmptyQueryResponse/PortalSuspended. This seems 
> wrong if there could be multiple resultsets from a single Execute.

This is okay if you know the query was a SELECT, since we don't allow
ON SELECT rules that aren't view-like (ie, DO INSTEAD SELECT something-else).
Non-SELECT queries can return multiple result sets, though, as I
illustrated.  It's probably reasonable for the driver to throw these
away if it's not looking for a resultset at all.  Or you could follow
PQexec's lead and discard all but the last.

> How can clients distinguish multiple resultsets if they're using the 
> extended query protocol?

You'll get multiple repetitions of RowDescription/DataRows.

			regards, tom lane

Thu, 23 Sep 2004 16:54:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SQL-Invoked Procedures for 8.1 
Tom Lane wrote:

>>How can clients distinguish multiple resultsets if they're using the 
>>extended query protocol?
> 
> You'll get multiple repetitions of RowDescription/DataRows.

Ah, so the Execute spontaneously generates a RowDescription 
spontaneously when it hits the second resultset, without needing an 
extra Describe?

-O

Fri, 24 Sep 2004 09:03:03 +1200
Oliver Jowett <oliver@opencloud.com>
Re: SQL-Invoked Procedures for 8.1
Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>>> How can clients distinguish multiple resultsets if they're using the 
>>> extended query protocol?
>> 
>> You'll get multiple repetitions of RowDescription/DataRows.

> Ah, so the Execute spontaneously generates a RowDescription 
> spontaneously when it hits the second resultset, without needing an 
> extra Describe?

Oh, wait, you won't get anything.  My example was using simple-Query
protocol.  In extended Query you get nothing, per this comment in
pquery.c:

    /*
     * If the destination is RemoteExecute, change to None.  The reason is
     * that the client won't be expecting any tuples, and indeed has no
     * way to know what they are, since there is no provision for Describe
     * to send a RowDescription message when this portal execution
     * strategy is in effect.  This presently will only affect SELECT
     * commands added to non-SELECT queries by rewrite rules: such
     * commands will be executed, but the results will be discarded unless
     * you use "simple Query" protocol.
     */

We will of course have to rethink this stuff if we want to be able to
send back multiple resultsets from a single procedure call ...

			regards, tom lane

Thu, 23 Sep 2004 17:12:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SQL-Invoked Procedures for 8.1 
Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
> 
>>Tom Lane wrote:
>>
>>>>How can clients distinguish multiple resultsets if they're using the 
>>>>extended query protocol?
>>>
>>>You'll get multiple repetitions of RowDescription/DataRows.
> 
> 
>>Ah, so the Execute spontaneously generates a RowDescription 
>>spontaneously when it hits the second resultset, without needing an 
>>extra Describe?
> 
> 
> Oh, wait, you won't get anything.  My example was using simple-Query
> protocol.  In extended Query you get nothing, per this comment in
> pquery.c:

Ok, thanks. It looks like the driver is doing the right thing then.

Here's a one-line patch that clarifies the Execute protocol docs slightly.

-O
Fri, 24 Sep 2004 11:07:51 +1200
Oliver Jowett <oliver@opencloud.com>
Re: SQL-Invoked Procedures for 8.1
Oliver Jowett <oliver@opencloud.com> writes:
> Here's a one-line patch that clarifies the Execute protocol docs slightly.

Applied.

			regards, tom lane

Thu, 23 Sep 2004 19:35:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SQL-Invoked Procedures for 8.1 