PostgreSQL JDBC - bug with 8.X level 4 driver?

Author

bug with 8.X level 4 driver?

Damon Anderson

2005-12-06, 3:23 am

Hello,

I currently have a problem with the following statement:

ppS = dd. createPPStatement();

ppS.setQueryString(
"INSERT INTO sandwich_side (name) VALUES ('pickles');" +
"INSERT INTO sandwich ( name, sideid) VALUES('club',
currval('sandwich_si
de_id_seq'));" +
"SELECT currval('sandwich_si
de_id_seq')");
Resultset rs = ppS. getPreparedStatement
().executeQuery();
try {
while( rs.next()) {}
} catch { SQLException ex1) {
ex1.printStackTrace();
}

(This use to work, but updated to 8.X driver (for a unicode issue))
SQLMessage: No results were returned by the query.
Statement: Pooled statement wrapping physical statement INSERT INTO
sandwich_side (name) VALUES ('pickles');INSERT INTO sandwich ( name,
sideid) VALUES('club', currval('sandwich_si
de_id_seq'));SELECT
currval('sandwich_si
de_id_seq')
org.postgresql.util.PSQLException: No results were returned by the query.
at
org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. executeQuery(Abstrac
tJdbc2Statement.java:241)

------------------

So did some searching on the forums, lists, etc.. Changed it to this:
try
{
ppS = dd. createPPStatement();

ppS.setQueryString(
"INSERT INTO sandwich_side (name) VALUES ('pickles');" +
"INSERT INTO sandwich ( name, sideid) VALUES('club',
currval('sandwich_si
de_id_seq'));" +
"SELECT currval('sandwich_si
de_id_seq')");
ppS.initStatement(); //Prepares the statement.
ppS. getPreparedStatement
().execute();
if( ppS. getPreparedStatement
().getMoreResults())
rs = ppS. getPreparedStatement
().getResultSet();
}
catch (SQLException ex1)
{
ex1.printStackTrace();
}

//ResultSet rs = ppS.getGeneratedKeys();
try
{
while (rs.next()) <-----
{
System.err.println("getInt: " + rs.getInt(1));
}
}
catch (SQLException ex) { ex.printStackTrace(); }

So at the moment I can't get the serial id that was created for this new
insert. The old way with just executeQuery worked great (must be 7.X
driver). I tried several version of the 8.X driver, but all had the same
results.

Anyone have the same results? Doing something wrong?

Thanks much,

Damon
P.S. This is with version 8.0 postgresql server.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dave Cramer

2005-12-06, 7:23 am

Damon,

Try using batches instead. The new driver can no longer do multiple
statements that way.

Dave
On 6-Dec-05, at 1:40 AM, Damon Anderson wrote:

> Hello,
>
> I currently have a problem with the following statement:
>
> ppS = dd. createPPStatement();

> ppS.setQueryString(
> "INSERT INTO sandwich_side (name) VALUES
> ('pickles');" +
> "INSERT INTO sandwich ( name, sideid) VALUES
> ('club', currval('sandwich_si
de_id_seq'));" +
> "SELECT currval('sandwich_si
de_id_seq')");
> Resultset rs = ppS. getPreparedStatement
().executeQuery();
> try {
> while( rs.next()) {}
> } catch { SQLException ex1) {
> ex1.printStackTrace();
> }
>
> (This use to work, but updated to 8.X driver (for a unicode issue))
> SQLMessage: No results were returned by the query.
> Statement: Pooled statement wrapping physical statement INSERT INTO
> sandwich_side (name) VALUES ('pickles');INSERT INTO sandwich
> ( name, sideid) VALUES('club', currval
> ('sandwich_side_id_s
eq'));SELECT currval('sandwich_si
de_id_seq')
> org.postgresql.util.PSQLException: No results were returned by the
> query.
> at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt.executeQuery
> (AbstractJdbc2Statem
ent.java:241)
>
> ------------------
>
> So did some searching on the forums, lists, etc.. Changed it to this:
> try
> {
> ppS = dd. createPPStatement();

> ppS.setQueryString(
> "INSERT INTO sandwich_side (name) VALUES
> ('pickles');" +
> "INSERT INTO sandwich ( name, sideid) VALUES
> ('club', currval('sandwich_si
de_id_seq'));" +
> "SELECT currval('sandwich_si
de_id_seq')");
> ppS.initStatement(); //Prepares the statement.
> ppS. getPreparedStatement
().execute();
> if( ppS. getPreparedStatement
().getMoreResults())
> rs = ppS. getPreparedStatement
().getResultSet();
> }
> catch (SQLException ex1)
> {
> ex1.printStackTrace();
> }
>
> //ResultSet rs = ppS.getGeneratedKeys();
> try
> {
> while (rs.next()) <-----
> {
> System.err.println("getInt: " + rs.getInt(1));
> }
> }
> catch (SQLException ex) { ex.printStackTrace(); }
>
> So at the moment I can't get the serial id that was created for
> this new insert. The old way with just executeQuery worked great
> (must be 7.X driver). I tried several version of the 8.X driver,
> but all had the same results.
>
> Anyone have the same results? Doing something wrong?
>
> Thanks much,
>
> Damon
> P.S. This is with version 8.0 postgresql server.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kris Jurka

2005-12-06, 11:24 am

On Tue, 6 Dec 2005, Damon Anderson wrote:

> So did some searching on the forums, lists, etc.. Changed it to this:
> try
> {
> ppS = dd. createPPStatement();

> ppS.setQueryString(
> "INSERT INTO sandwich_side (name) VALUES ('pickles');" +
> "INSERT INTO sandwich ( name, sideid) VALUES('club',
> currval('sandwich_si
de_id_seq'));" +
> "SELECT currval('sandwich_si
de_id_seq')");
> ppS.initStatement(); //Prepares the statement.
> ppS. getPreparedStatement
().execute();
> if( ppS. getPreparedStatement
().getMoreResults())
> rs = ppS. getPreparedStatement
().getResultSet();

What this is going to retrieve is two update counts and two ResultSets.
This means you need to call getMoreResults twice to get to the first
ResultSet.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Oliver Jowett

2005-12-06, 8:26 pm

Dave Cramer wrote:

> Try using batches instead. The new driver can no longer do multiple
> statements that way.

It can handle this sort of multiple statement just fine, actually.

However, the driver now correctly follows the JDBC spec, and you must
use getMoreResults() to skip past the update counts returned by the
INSERT to get at the actual ResultSet-returning query, then use
getResultSet().

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links