Обсуждение: JDBC Driver42.2.2 throws error when dealing with money datatype
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.
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(PgResultS et.java:2930) at org.postgresql.jdbc.
PgResultSet.getDouble(PgResult Set.java:2317) at org.postgresql.jdbc.
PgResultSet.internalGetObject( PgResultSet.java:194) at org.postgresql.jdbc.
PgResultSet.getObject(PgResult Set.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.
Sent: Monday, June 4, 2018 11:31:58 PM
To: Suwalka, Kriti
Cc: pgsql-bugs@postgresql.org
Subject: Re: JDBC Driver42.2.2 throws error when dealing with money datatype
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(PgResultS et.java:2930) at org.postgresql.jdbc.
PgResultSet.getDouble(PgResult Set.java:2317) at org.postgresql.jdbc.
PgResultSet.internalGetObject( PgResultSet.java:194) at org.postgresql.jdbc.
PgResultSet.getObject(PgResult Set.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.
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.1322663810%40sss.pgh.pa.us#9322.1322663810@sss.pgh.pa.us https://www.postgresql.org/message-id/flat/4B99F6E9.7000407%40wp.pl#4B99F6E9.7000407@wp.pl https://www.postgresql.org/message-id/flat/7fbead4f-1375-40b9-8a5b-95d3a1a5e5a5%40e23g2000prf.googlegroups.com#7fbead4f-1375-40b9-8a5b-95d3a1a5e5a5@e23g2000prf.googlegroups.com https://www.postgresql.org/message-id/flat/47C415C7.6070200%40openplans.org#47C415C7.6070200@openplans.org Isn't this caused by the fact that the server sends a "formatted" string including the currency symbol, decimal symbol andgrouping 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@informatica.com <mailto:ksuwalka@informatica.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 asdouble 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 expectedthat driver would take care of retrieving money data type when using ResultSet.getObject() but unfortunately driverisn’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.____ > > __ __ > >
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@gmx.net] Sent: Tuesday, June 05, 2018 11:49 AM To: Dave Cramer <davecramer@gmail.com>; Suwalka, Kriti <ksuwalka@informatica.com> Cc: pgsql-bugs@postgresql.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.1322663810%40sss.pgh.pa.us#9322.1322663810@sss.pgh.pa.us https://www.postgresql.org/message-id/flat/4B99F6E9.7000407%40wp.pl#4B99F6E9.7000407@wp.pl https://www.postgresql.org/message-id/flat/7fbead4f-1375-40b9-8a5b-95d3a1a5e5a5%40e23g2000prf.googlegroups.com#7fbead4f-1375-40b9-8a5b-95d3a1a5e5a5@e23g2000prf.googlegroups.com https://www.postgresql.org/message-id/flat/47C415C7.6070200%40openplans.org#47C415C7.6070200@openplans.org Isn't this caused by the fact that the server sends a "formatted" string including the currency symbol, decimal symbol andgrouping 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@informatica.com <mailto:ksuwalka@informatica.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 asdouble 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 expectedthat driver would take care of retrieving money data type when using ResultSet.getObject() but unfortunately driverisn’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.____ > > __ __ > >