Обсуждение: 8.0 + JDBC3 Driver

First, I have an oddity with the 8.0-310.jdbc3 driver.  When I
query the db, I have to explicity specify the ResultSet type and
concurrency if I want something other than a FORWARD_ONLY
ResultSet.  This was not necessary in 7.4.  How do I tell when a
table will return a FORWARD_ONLY ResultSet by default?

Second, I'm having a problem with a query, which I will get to
in a second.  However, I notice from the stack trace that calls
are being made into the jdbc2 portion of the driver, even though
I am using the JDBC3 Jar with JDK 1.5.  Is this normal, or is there
something wrong?

Third, this is my actual problem:

INSERT INTO my_table
    (my_fields, ...)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ;
SELECT currval('my_schema.my_seq')
    ;

I do a prepareStatement() with the above query, I fill in the params,
and I call executeQuery() hoping to get a ResultSet.  However,
instead I get:

PSQLException: No results were returned by the query.

But if I run the query in pgAdmin, it works just fine,
not to mention that this worked perfectly under 7.4,
and works just fine under 8.0 in another app.  Any
suggestions welcome.

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East,  Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129

On Tue, 9 Aug 2005, Dave Held wrote:

> First, I have an oddity with the 8.0-310.jdbc3 driver.  When I
> query the db, I have to explicity specify the ResultSet type and
> concurrency if I want something other than a FORWARD_ONLY
> ResultSet.  This was not necessary in 7.4.  How do I tell when a
> table will return a FORWARD_ONLY ResultSet by default?

The javadoc for Connection.prepareStatement says "Result sets created
using the returned PreparedStatement object will by default be type
TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY."

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String)

The 7.4 driver didn't correctly enforce this and let you scroll a forward
only result, but this has been fixed in 8.0.

> Second, I'm having a problem with a query, which I will get to
> in a second.  However, I notice from the stack trace that calls
> are being made into the jdbc2 portion of the driver, even though
> I am using the JDBC3 Jar with JDK 1.5.  Is this normal, or is there
> something wrong?

This is expected.  The JDBC 2 + 3 drivers share source code, so a JDBC 3
build is mostly just a JDBC 2 build plus some extra stuff.

> Third, this is my actual problem:
>
> INSERT INTO my_table
>     (my_fields, ...)
>     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
>     ;
> SELECT currval('my_schema.my_seq')
>     ;
>
> I do a prepareStatement() with the above query, I fill in the params,
> and I call executeQuery() hoping to get a ResultSet.  However,
> instead I get:
>
> PSQLException: No results were returned by the query.
>

What the driver does is split this query into two pieces and issues them
separately.  This returns two results from the server, an update count and
the SELECT result.  You should use PreparedStatement.execute() and then
getMoreResults and then getResultSet to navigate to the second part of the
query results.

Kris Jurka

[reply snipped]

Thanks for the excellent answers!

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East,  Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129