JDBC: BigDecimal and Money confusion

Hi there folks,

I searched the archives and haven't found anyone with this problem, but I'd
be surprised if it's unique.

We use BigDecimal for money values throughout our application. This is
because our experience with using floats was that when you were adding up a
large number of money values (e.g. to round an invoice to the nearest 5
cents), eventually we would have a problem with rounding errors causing our
values to be out by cents. In order to talk more accurately with the database
we converted everything to BigDecimals.

We are now porting our application to Postgres (thanks guys +/or gals!!) and
run into a problem that if we have a PreparedStatement and we attempt to use
a BigDecimal in a money column, we get the exception, java.sql.SQLException:
ERROR: Attribute 'amount' is of type 'money' but expression is of type
'float8'. It seems to me that a BigDecimal of scale 2 is a perfect fit for an
attribute of type Money since it is perfectly accurate.

I had a look at the JDBC driver's PreparedStatement class and worked out that
it just uses strings to communicate with the back end. When I use psql I know
I have to use quotes around money values in order to prevent them from being
interpreted as floats. So, all I did was to modify the JDBC1 driver to call
set(paramIndex, s) with a quoted string. I took this idea from the
setString() method. This seems to work perfectly. While I haven't tried it
yet, I think this would still work for float values.

So, my questions are: (a) why couldn't I find anyone else having this
problem? is it just me? (b) have I done the right thing? we have two days to
port this so I didn't want to learn everything if I could avoid it (doh - we
thought the hard part would be the ODBC bit)! and (c) is anyone interested in
the patch, which I am happy to contribute? I would also patch the JDBC2
driver and I intend to look for an exception message bug that seems to want
more java.text.format parameters than there are available.

Anyway, I know I have jumped in with both feet here but we are very happy and
impressed with Postgresql and I look forward to being able to contribute, if
possible. I have quite a few questions about postgresql in general but I will
wait until I am not such a newbie any more. So, apologies if I have done the
wrong thing here.

(As an aside, I found the PGMoney class but it too only accepts doubles. I
could use doubleValue() on the BigDecimal but I am worried about the loss of
precision given my past experience. So my position is that I reckon I should
be able to use arbitrary precision up until the database interprets it).

Cheers
Mark

--
Mark Lillywhite - Plastic Software http://www.plasticsoftware.com.au/
-------------------------------------------------------------------------------
Plastic Software provides fully integrated, easy to use products and support
for Internet Service Providers, such as authentication and accounts receivable.

"The great thing about free mail is that you don't have to pay for it."
-- Scott McNealy