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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
