JDBC: BigDecimal and Money confusion - Mailing list pgsql-interfaces
From | Mark Lillywhite |
---|---|
Subject | JDBC: BigDecimal and Money confusion |
Date | |
Msg-id | 37E33854.715C30AE@plasticsoftware.com.au Whole thread Raw |
Responses |
Re: [INTERFACES] JDBC: BigDecimal and Money confusion
Re: [INTERFACES] JDBC: BigDecimal and Money confusion |
List | pgsql-interfaces |
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
pgsql-interfaces by date: