Re: Multiple Statement result set problem with PreparedStatements ?

What I'm trying to do is do 2 inserts and get the resultant key values
from the 2 inserts - i.e. effectively the row indexes (there are
separate sequences for the pkey for each table, so the currval() gives
the key of the insert for that table.

This SQL works fine from any command line interface to the DB - giving
the expected results.

However through the JDBC driver, I don't get any results with a select
involving the 2 seqs, but if I choose just one I do !!.

BTW - The followup analysis below is flawed.

With this SQL the driver always returns false for the execute() thus
taking the getMoreResults() path & there are never results for the
INSERT (at least not easily obtainable via the API in a generic way).

Rohan
| -----Original Message-----
| From: Oliver Jowett [mailto:oliver@opencloud.com]
| Sent: Thursday, July 14, 2005 11:40 AM
| To: Lenard, Rohan (Rohan)
| Cc: pgsql-jdbc@postgresql.org
| Subject: Re: [JDBC] Multiple Statement result set problem
| with PreparedStatements ?
|
| Lenard, Rohan (Rohan) wrote:
|
| > final PreparedStatement stmt = conn.createPreparedStatement("
| > INSERT INTO foo ( a, b ) VALUES ( ?, ? ); INSERT INTO bar (
| foo_fkey,
| > c ) VALUES ( ?, currval('foo_seq'::text) ); SELECT
| > currval('bar_seq'::text), currval('foo_seq'::text);");
| >
| > stmt.setString(1, "A");
| > stmt.setString(2, "B");
| > stmt.setString(3, "C");
| >
| > boolean result = stmt.execute();
| > if (result) {
| >   processRS(stmt.getResultSet());
| > }
|
| That should handle the first INSERT.
|
| > else {
| >   if (getMoreResults()) {
| >      processRS(stmt.getResultSet()); // only gets called with 1
| > element SELECTED.
| >   }
| > }
|
| That should handle the second INSERT.
|
| You do not appear to be handling the results of the SELECT at all.
|
| Your testcase is not very clear about what you're trying to
| do and the incorrect behaviour you see :/
|
| -O
|