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

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.