Обсуждение: BUG #1640: Using JDBC, multiple statements do not return results if select follows insert,delete or update

The following bug has been logged online:

Bug reference:      1640
Logged by:          Kevin Self
Email address:      kself@micromedia.ca
PostgreSQL version: 8.0.2
Operating system:   Windows XP
Description:        Using JDBC, multiple statements do not return results if
select follows insert,delete or update
Details:

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.

Pre-conditions:
Assuming a database called 'test'
Assuming a user called 'test' with a password 'test'.
create a table as follows:

create table test (a serial primary key,b int);

Example:
public static void main(String[] args) throws Exception
{
try
{
Class.forName("org.postgresql.Driver");
Connection lc =
DriverManager.getConnection("jdbc:postgresql://localhost/test","test","test"
);
PreparedStatement stmt = lc.prepareStatement("insert into test(b)
values(100);select currval('test_a_seq');");

if(!stmt.execute()){System.out.println("NO RESULTS!");}
else        {System.out.println("RESULTS!");}
lc.close();
}
catch(Exception e)
{
System.out.println(e.toString());
}
}

Expectd Results:
RESULTS!

Actual results:
NO RESULTS!

Notes:
This used to work in the previous version of PostgreSQL (7.x).

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