Re: SQL-Invoked Procedures for 8.1

Gavin Sherry wrote:

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

It's not specific to CallableStatement; you can generate multiple
resultsets from a plain Statement, and CallableStatement is just
inheriting that functionality.

The common way of generating multiple resultsets is, indeed, a
multi-statement query. For example:

> Statement stmt = conn.createStatement();
> stmt.execute("SELECT * FROM foo; SELECT * FROM bar");
>
> ResultSet rs1 = stmt.getResultSet();
> // process rs1
> rs1.close();
>
> boolean moreResults = stmt.getMoreResults();
> assert moreResults;
>
> ResultSet rs2 = stmt.getResultSet();
> // process rs2
> rs2.close();
>
> stmt.close();

AFAIK the multiple-resultset stuff is not *required* functionality in
JDBC, it's just there to support it if it does happen. The postgresql
JDBC driver didn't actually support multiple resultsets at all until
recently.

For function/procedure calls, I'd expect it to look like:

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

and for the driver to turn that into two separate SELECT/CALL/whatever
queries at the protocol level, and manage the multiple resultsets
itself. The current driver doesn't handle multiple call escapes in one
query at all, but that's really just a limitation of the reasonably dumb
call-escape parser it currently has.

I wouldn't worry about this case unless there's some other reason that a
*single* function/procedure call needs to return more than one set of
results.

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

That's because there's no standard way to generate them :)

-O