RE: JDBC Driver42.2.2 throws error when dealing with money datatype

From: "Suwalka, Kriti" <ksuwalka(at)informatica(dot)com>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>, Dave Cramer <davecramer(at)gmail(dot)com>
Cc: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: RE: JDBC Driver42.2.2 throws error when dealing with money datatype
Date: 2018-06-05 15:53:44
Message-ID: BN6PR03MB3284F50EB6D66ED81E712287C7660@BN6PR03MB3284.namprd03.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi Thomas,

I too had seen the discussion on the JDBC mailing list but wanted to confirm if it was fixed.
Thanks for the clarification :)

Regards,
Kriti

-----Original Message-----
From: Thomas Kellerer [mailto:spam_eater(at)gmx(dot)net]
Sent: Tuesday, June 05, 2018 11:49 AM
To: Dave Cramer <davecramer(at)gmail(dot)com>; Suwalka, Kriti <ksuwalka(at)informatica(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: JDBC Driver42.2.2 throws error when dealing with money datatype

This has been discussed on the JDBC mailing list before (and belongs there) and as far as I can tell has never worked properly:

https://www.postgresql.org/message-id/flat/9322(dot)1322663810%40sss(dot)pgh(dot)pa(dot)us#9322(dot)1322663810(at)sss(dot)pgh(dot)pa(dot)us
https://www.postgresql.org/message-id/flat/4B99F6E9(dot)7000407%40wp(dot)pl#4B99F6E9(dot)7000407(at)wp(dot)pl
https://www.postgresql.org/message-id/flat/7fbead4f-1375-40b9-8a5b-95d3a1a5e5a5%40e23g2000prf(dot)googlegroups(dot)com#7fbead4f-1375-40b9-8a5b-95d3a1a5e5a5(at)e23g2000prf(dot)googlegroups(dot)com
https://www.postgresql.org/message-id/flat/47C415C7(dot)6070200%40openplans(dot)org#47C415C7(dot)6070200(at)openplans(dot)org

Isn't this caused by the fact that the server sends a "formatted" string including the currency symbol, decimal symbol and grouping symbols?
But Double.parseDouble(String) can't parse such a String

And I do agree with the recommendations in some of the above threads: do not use the money data type.
For all intents and purposes numeric is a much better choice.

Thomas

Dave Cramer schrieb am 04.06.2018 um 20:01:
> Hello,
>
> Well that truly is a bug, and shouldn't happen.
>
> Thanks
>
> Dave Cramer
>
> On 4 June 2018 at 11:47, Suwalka, Kriti <ksuwalka(at)informatica(dot)com <mailto:ksuwalka(at)informatica(dot)com>> wrote:
>
> Hi,____
>
> __ __
>
> I was trying to read a table with money column using JDBC driver(version 42.2.2).  JDBC driver returns data type as double and it internally tries to use ResultSet.getDouble when ResultSet.getObject() is used.____
>
> __ __
>
> Please find the details below____
>
> Database version : 9.6____
>
> Driver version : 42.2.2____
>
> Table DDL: create table src_money(col1 money);____
>
> Insert DML : insert into src_money values(1000);____
>
> __ __
>
> JDBC Code Snippet:____
>
>                  String sqlQuery= "select * from src_money";____
>
>                   PreparedStatement stmt= con.prepareStatement(sqlQuery);____
>
>                   ResultSet readRs= stmt.executeQuery();____
>
>                   *while*(readRs.next()) {____
>
>                      System.*/out/*.println(readRs.getObject(1));____
>
>                   } ____
>
> __ __
>
> JDBC Code Output:____
>
>        _org.postgresql.util.PSQLException_: Bad value for type double : 1,000.00____
>
>        at org.postgresql.jdbc.PgResultSet.toDouble(_PgResultSet.java:2930_)____
>
>        at org.postgresql.jdbc.PgResultSet.getDouble(_PgResultSet.java:2317_)____
>
>        at org.postgresql.jdbc.PgResultSet.internalGetObject(_PgResultSet.java:194_)____
>
>        at org.postgresql.jdbc.PgResultSet.getObject(_PgResultSet.java:2568_)____
>
>        at jdbc_sample_latest.PostgresSSL.main(_PostgresSSL.java:74_)____
>
> __ __
>
> What is the fix or work-around here to get rid of this error? My application is a generic application and I expected that driver would take care of retrieving money data type when using ResultSet.getObject() but unfortunately driver isn’t working as expected.____
>
> __ __
>
> P.S.> I have seen the discussion on the same issue on forum but couldn’t find a solution for the same.____
>
> __ __
>
>

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavan Teja 2018-06-05 17:05:09 Re: Warning when using psql
Previous Message Jean Thomas SERVANT 2018-06-05 15:42:57 Warning when using psql