JDBC Driver42.2.2 throws error when dealing with money datatype

Started by Suwalka, Kritialmost 8 years ago5 messagesbugs

ksuwalka@informatica.com

almost 8 years ago

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.

Dave Cramer

pg@fastcrypt.com

almost 8 years ago

In reply to: Suwalka, Kriti (#1)

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

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> wrote:

Show quoted text

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.

Suwalka, Kriti

ksuwalka@informatica.com

almost 8 years ago

In reply to: Dave Cramer (#2)

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

Hi Dave,

Thanks for the response ??. Any work-around available? Or any plan to fix it ?

Regards,
Kriti

Get Outlook for Android<https://aka.ms/ghei36>

________________________________
From: Dave Cramer <davecramer@gmail.com>
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

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,

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);

P.S.> I have seen the discussion on the same issue on forum but couldn’t find a solution for the same.

Thomas Kellerer

spam_eater@gmx.net

almost 8 years ago

In reply to: Dave Cramer (#2)

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:

/messages/by-id/9322.1322663810@sss.pgh.pa.us
/messages/by-id/4B99F6E9.7000407@wp.pl
/messages/by-id/7fbead4f-1375-40b9-8a5b-95d3a1a5e5a5@e23g2000prf.googlegroups.com
/messages/by-id/47C415C7.6070200@openplans.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:

Show quoted text

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 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.____

__ __

Suwalka, Kriti

ksuwalka@informatica.com

almost 8 years ago

In reply to: Thomas Kellerer (#4)

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

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:

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:

Show quoted text

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 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.____

__ __