Thread: Multiple Statement result set problem with PreparedStatements ?

Okay - my bad - that works and I think I can see what I did wrong.  I
have to call "both" methods to get the right results..

That's something that should be in the documentation.  The JDBC javadoc
doesn't make it obvious you really need to get it right so call them
both - it says call one or the other ...

| -----Original Message-----
| From: Oliver Jowett [mailto:oliver@opencloud.com]
| Sent: Thursday, July 14, 2005 12:20 PM
| To: Lenard, Rohan (Rohan)
| Cc: pgsql-jdbc@postgresql.org
| Subject: Re: [JDBC] Multiple Statement result set problem
| with PreparedStatements ?
|
| Lenard, Rohan (Rohan) wrote:
| > I have one but it relies on particular tables existing - if I get a
| > few minutes I too will simplify it to cause it ..
|
| Hm, ok, try the attached testcase. It works fine for me here against
| 8.0.1 and 7.3.8 servers:
|
| > oliver@extrashiny ~/pgjdbc-test $ java -classpath
| .:/home/oliver/pgjdbc/jars/postgresql.jar TestMultiInsert
| 'jdbc:postgresql://localhost:5432/test?user=oliver'
| > Result #1 has resultset? false update count 1 Result #2 has
| resultset?
| > false update count 1 Result #3 has resultset? true update count -1
| > oliver@extrashiny ~/pgjdbc-test $ java -classpath
| .:/home/oliver/pgjdbc/jars/postgresql.jar TestMultiInsert
| 'jdbc:postgresql://localhost:5738/test?user=oliver'
| > Result #1 has resultset? false update count 1 Result #2 has
| resultset?
| > false update count 1 Result #3 has resultset? true update count -1
|
| Those are the results I was expecting, anyway.. were you
| expecting something different?
|
| -O
|

I have one but it relies on particular tables existing - if I get a few
minutes I too will simplify it to cause it ..

Rohan
| -----Original Message-----
| From: Oliver Jowett [mailto:oliver@opencloud.com]
| Sent: Thursday, July 14, 2005 12:04 PM
| To: Lenard, Rohan (Rohan)
| Cc: pgsql-jdbc@postgresql.org
| Subject: Re: [JDBC] Multiple Statement result set problem
| with PreparedStatements ?
|
| Lenard, Rohan (Rohan) wrote:
| > 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 !!.
|
| That is weird.
|
| I will try to find time to put together a selfcontained
| testcase later on (you could speed this up by sending me one :)
|
| > 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).
|
| Sorry -- not thinking there :)
|
| What I'd expect to see is this sequence:
|
|   execute() returns false (no resultset for this result)
|   getUpdateCount() returns 1 (1 row inserted)
|   getMoreResults() returns false (no resultset for this result)
|   getUpdateCount() returns 1 (1 row inserted)
|   getMoreResults() returns true (resultset present)
|   getResultSet() returns an appropriate resultset for the SELECT
|   getMoreResults() returns false (no resultset for this result)
|   getUpdateCount() returns -1 (no more results)
|
| Your code seems to be expecting something different..
|
| -O
|

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
|

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