Re: BUG #1640: Using JDBC, multiple statements do not return

On Fri, 29 Apr 2005, Kevin Self wrote:

> Bug reference: 1640
> PostgreSQL version: 8.0.2
> Description: Using JDBC, multiple statements do not return results if
> select follows insert,delete or update
>
> When attempting to perform a multi-statement command through the JDBC
> Statement.execute() or the Statement.executeQuery() function, the database
> does not return any result set if the select statement follows an insert,
> update or delete. This ability is required for atomic record inserts for
> auto-generated keys that must be returned to the caller, since postgreSQL
> does not support RETURN_GENERATED_KEYS as an option, and calling the
> sequence ahead of time to obtain the value is not possible.

There is no "atomicity" from issuing them in the same query. This is
guaranteed by the sequence for the entire session.

If you do what to do this it should be written as:

Statement.execute("INSERT ...; SELECT currval('myseq')");
Statement.getMoreResults();
ResultSet rs = Statement.getResultSet();

The javadoc for execute() says it should return "true if the first result
is a ResultSet object" the first result is an update count, not a
ResultSet. You need to move to the next result, which is actually a
ResultSet.

Kris Jurka