PostgreSQL JDBC - Multiple Statement result set problem with PreparedStatements ?

Author

Multiple Statement result set problem with PreparedStatements ?

Lenard, Rohan

2005-07-13, 8:24 pm

I have SQL like this (foo has a pkey from a serial) -

INSERT INTO foo ( a, b ) VALUES ( ?, ? );
INSERT INTO bar ( foo_fkey, c ) VALUES ( ?, currval('foo_seq'::t
ext) );
SELECT currval('bar_seq'::t
ext) AS x, currval('foo_seq'::t
ext) AS y;

that I'm using like this

final PreparedStatement stmt = conn. createPreparedStatem
ent("
INSERT INTO foo ( a, b ) VALUES ( ?, ? );
INSERT INTO bar ( foo_fkey, c ) VALUES ( ?, currval('foo_seq'::t
ext) );
SELECT currval('bar_seq'::t
ext), currval('foo_seq'::t
ext);");

stmt.setString(1, "A");
stmt.setString(2, "B");
stmt.setString(3, "C");

boolean result = stmt.execute();
if (result) {
processRS(stmt.getResultSet());
}
else {
if (getMoreResults()) {
processRS(stmt.getResultSet()); // only gets called with 1 element
SELECTED.
}
}

This works fine if the SELECT returns 1 value only, but as soon as it
returns 2 values the inserts work but no results are given... What the
???

I have pg-8.0-311 against a 7.3.10 server..

Any ideas ?

Thx,
Rohan

Lenard, Rohan

2005-07-14, 3:24 am

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 & #91;mailto:oliver@op
encloud.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
|

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Lenard, Rohan

2005-07-14, 3:24 am

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 & #91;mailto:oliver@op
encloud.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
|

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Lenard, Rohan

2005-07-14, 3:24 am

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 & #91;mailto:oliver@op
encloud.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. createPreparedStatem
ent("
| > INSERT INTO foo ( a, b ) VALUES ( ?, ? ); INSERT INTO bar (
| foo_fkey,
| > c ) VALUES ( ?, currval('foo_seq'::t
ext) ); SELECT
| > currval('bar_seq'::t
ext), currval('foo_seq'::t
ext);");
| >
| > 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
|

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links