Bug in handling of money type

From: RW Shore <rws228(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Bug in handling of money type
Date: 2011-02-08 19:36:53
Message-ID: AANLkTindOsx8yAGJGgCH+Mem-SJXOW1x1RtFbyv2Yym0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Driver build: 9.0-801
Server version: 9.0 (Windows x64)

From a Java/JDBC perspective, the handling of the money data type seems
broken. My code is DBMS independent and reasonably generic, which means that
the code makes extensive use of prepared statements and the
getObject()/setObject() method calls. I am able to insert money data by
writing a custom class that extends PGmoney, allowing me to control the
string format through PGmoney.getValue(). However, data successfully
inserted cannot be retrieved via getObject():

org.postgresql.util.PSQLException: Bad value for type double :
12,345,789,000,000.00
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toDouble(AbstractJdbc2ResultSet.java:2863)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toDouble(AbstractJdbc2ResultSet.java:2863)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getDouble(AbstractJdbc2ResultSet.java:2220)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.internalGetObject(AbstractJdbc2ResultSet.java:136)
at
org.postgresql.jdbc3.AbstractJdbc3ResultSet.internalGetObject(AbstractJdbc3ResultSet.java:38)
at
org.postgresql.jdbc4.AbstractJdbc4ResultSet.internalGetObject(AbstractJdbc4ResultSet.java:298)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:2541)
at
com.cst.chetx.data.map.postgres.PostgresDataMapTest.test_2(PostgresDataMapTest.java:323)

IMO the root of the problem involves the typing of money from a JDBC
perspective. The table metadata reports the type of a money attribute as
standard type java.sql.Types.DOUBLE, dbms-specific type money. My first
issue is that the prepared statement will not accept a Double value via
setObject() for a money attribute, even though the metadata says that it is
a Double - the only thing I could get to work was a PGmoney extension.
Second, once a value is inserted into a money attribute, a result set
insists on converting it into a Double, with results like the above.

My suggestions to fix these issues are the following:

1. The type of a money field could be java.sql.Types.OTHER with
dbms-specific type money. This would give fair warning that there's
something, er, interesting about money-type attributes. Note that mapping
money to OTHER would not necessarily stop a result set from implementing the
getDouble() method for money types - code using this method could still work
without change. In this case ResultSet.getObject() should IMO return a
PGmoney instance.

2. If OTHER isn't acceptible for whatever reason, then use
java.sql.Types.DECIMAL or NUMERIC, again with dbms-specific type money. In
this case PreparedStatement.setObject() should always accept a BigDecimal
(in addition to a PGmoney), as long as the precision and scale were
acceptible. As in the first case, ResultSet.getObject() should return a
PGmoney instance.

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2011-02-09 00:20:32 Re: ResultSet.getClob() causing problems when used with JPA's @Lob
Previous Message John R Pierce 2011-02-08 18:04:31 Re: Connecting over UNIX domain sockets