Thread: JDBC 9.2: multiple execution of prepared statement-query delivers different double precision results via getString()

Hello,

there might be a problem in the latest JDBC-driver (9.2 build 1003) when querying double precision-result columns
multipletimes 
and retrieving the result with ResultSet.getString():

The first five queries work as expected, further calls append ".0" to the result-string.

Here is a sample code:

PreparedStatement stmt = myConnection.prepareStatement("select cast(1 as double precision)");
for (int x=0;x<12;x++){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()){
        System.out.println("Value: " + rs.getString(1));
    }
}

-------
Output:
-------

Value: 1
Value: 1
Value: 1
Value: 1
Value: 1
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0


The problem does not occur when using JDBC-driver version 9.1;
I use PostgreSQL Version 9.3.0 (Win64/Vista)

Best regards,
  Tilman Sandig

The first few times the driver requests the server to return "1 as double precision" as a text value (thus using server
sidetoString function). 

After that the driver evaluates the actual types of the columns and requests the server to send the value in binary
format.Your toString method will then invoke Double(1).toString on your client side. 

If you modify your code to do
  System.out.println("Value: " + rs.getDouble(1));
the result should not change as statements change column values to binary data encoding.

I do not think the driver can make guarantees about the format of getString values, unless the original database field
istextual. 

-Mikko
________________________________________
From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Tilman Sandig <info@sandig.com>
Sent: 13 September 2013 18:44
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] JDBC 9.2: multiple execution of prepared statement-query delivers different double precision results
viagetString() 

Hello,

there might be a problem in the latest JDBC-driver (9.2 build 1003) when querying double precision-result columns
multipletimes 
and retrieving the result with ResultSet.getString():

The first five queries work as expected, further calls append ".0" to the result-string.

Here is a sample code:

PreparedStatement stmt = myConnection.prepareStatement("select cast(1 as double precision)");
for (int x=0;x<12;x++){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()){
        System.out.println("Value: " + rs.getString(1));
    }
}

-------
Output:
-------

Value: 1
Value: 1
Value: 1
Value: 1
Value: 1
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0


The problem does not occur when using JDBC-driver version 9.1;
I use PostgreSQL Version 9.3.0 (Win64/Vista)

Best regards,
  Tilman Sandig



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

I think, you are right; but it might be a potential pitfall, since

- the behavior is nondeterministic
- the results differ from documented sample codes and PgAdmin-query-results, especially when using the
EXTRACT-function like

    select EXTRACT(YEAR FROM now())

I didn't expect to retrieve "2013.0" with the 6th call...

Actually, my website crashed when processing the results in my xml-framework (that is the reason, why I use
getString())
building a new date-string from extracted components.

-Tilman




-----Ursprüngliche Nachricht-----
Von: Mikko Tiihonen [mailto:Mikko.Tiihonen@nitorcreations.com]
Gesendet: Freitag, 13. September 2013 18:12
An: Tilman Sandig; pgsql-jdbc@postgresql.org
Betreff: RE: [JDBC] JDBC 9.2: multiple execution of prepared statement-query delivers different double precision
resultsvia 
getString()

The first few times the driver requests the server to return "1 as double precision" as a text value (thus using server
side
toString function).

After that the driver evaluates the actual types of the columns and requests the server to send the value in binary
format.Your 
toString method will then invoke Double(1).toString on your client side.

If you modify your code to do
  System.out.println("Value: " + rs.getDouble(1)); the result should not change as statements change column values to
binary
data encoding.

I do not think the driver can make guarantees about the format of getString values, unless the original database field
is
textual.

-Mikko
________________________________________
From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Tilman Sandig <info@sandig.com>
Sent: 13 September 2013 18:44
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] JDBC 9.2: multiple execution of prepared statement-query delivers different double precision results
via
getString()

Hello,

there might be a problem in the latest JDBC-driver (9.2 build 1003) when querying double precision-result columns
multipletimes 
and retrieving the result with ResultSet.getString():

The first five queries work as expected, further calls append ".0" to the result-string.

Here is a sample code:

PreparedStatement stmt = myConnection.prepareStatement("select cast(1 as double precision)"); for (int x=0;x<12;x++){
    ResultSet rs = stmt.executeQuery();
    while (rs.next()){
        System.out.println("Value: " + rs.getString(1));
    }
}

-------
Output:
-------

Value: 1
Value: 1
Value: 1
Value: 1
Value: 1
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0
Value: 1.0


The problem does not occur when using JDBC-driver version 9.1; I use PostgreSQL Version 9.3.0 (Win64/Vista)

Best regards,
  Tilman Sandig



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Tilman Sandig wrote
> I think, you are right; but it might be a potential pitfall, since
>
> - the behavior is nondeterministic
> - the results differ from documented sample codes and
> PgAdmin-query-results, especially when using the
> EXTRACT-function like
>
>     select EXTRACT(YEAR FROM now())
>
> I didn't expect to retrieve "2013.0" with the 6th call...

The action itself is not really supported so it isn't surprising that there
are oddities.  If the "getXXX" doesn't match the actual result type the
behavior is undetermined.

The PgAdmin equivalent is not a fair comparison though it does make sense to
try it.  The "correct" way is to accept the double at the client side and
let the client, not the driver, convert it for display.

I'm not sure the driver can or even should save the user from themselves in
this kind of situation; any such parenting is going to result in either a
"strict/non-strict" mode setup or complaints because of "type mis-match"
errors.


> If you modify your code to do
>   System.out.println("Value: " + rs.getDouble(1)); the result should not
> change as statements change column values to binary
> data encoding.

the "correct" modification is:

SELECT extract(year FROM now())::integer  --or "::varchar; or
::integer::varchar" to be perfectly safe

The extract function is multi-purpose and so has to have an output that is
flexible.  If you know what field you are extracting you should cast the
double result to that which most closely matches the field in question.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/JDBC-9-2-multiple-execution-of-prepared-statement-query-delivers-different-double-precision-results--tp5770716p5770793.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.