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