Обсуждение: Bug when retrieving money datatype.

Hello,

columns with type "money" are reported as Types.DOUBLE by the JDBC driver.

However using ResultSet.getDouble() (or getObject()) on those columns throws an error:

Bad value for type double : €100,00
(SQL State=22003)

It seems that for money columns, only getString() works properly which doesn't really make sense. It breaks
applicationswhich assume that ResultSet.getDouble() can be used on a column that is identified as Types.DOUBLE 

I would at least expect ResultSet.getObject() to work properly on those columns.

Regards
Thomas

On 30 November 2011 10:36, Thomas Kellerer <spam_eater@gmx.net> wrote:

> Bad value for type double : €100,00

Looks like an internationalization problem - the code that handles
parsing money values isn't expecting a euro sign, just a dollar sign.
Is there a standard way to correctly interpret money values formatted
by the server when the client's locale might be different to the
server's?

Oliver

On 11/29/2011 11:51 PM, Oliver Jowett wrote:
> On 30 November 2011 10:36, Thomas Kellerer<spam_eater@gmx.net>  wrote:
>
>> Bad value for type double : €100,00
>
> Looks like an internationalization problem - the code that handles
> parsing money values isn't expecting a euro sign, just a dollar sign.
> Is there a standard way to correctly interpret money values formatted
> by the server when the client's locale might be different to the
> server's?

I was planning to add the binary format support for money to the
jdbc driver when I had some free time. It will solve all parsing
problems related to locale and give nice speed boost at the same time.

The current way is a hack where the generic number parsing knows how to
ignore $ and (). Of course we should also fix the text format parsing.

In both cases I think the correct way is to handle the money as a
PGmoney type in jdbc driver.

Here is my mental list of what should be improved around the PGmoney:
- Use BigDecimal instead of double
- Support sending the text format in the server locale
- Proper conversion when requested with getBigDecimal, getDouble,
   getInt, getLong in the driver
- Making sure getString of money type will no longer return the $/€ or
   whatever currency the user might have
- add binary encoding support

-Mikko

Mikko Tiihonen <mikko.tiihonen@nitorcreations.com> writes:
> I was planning to add the binary format support for money to the
> jdbc driver when I had some free time. It will solve all parsing
> problems related to locale and give nice speed boost at the same time.

I don't think that's exactly a magic bullet.  Yes, it'll mean you don't
need to know the locale's currency symbol or what it uses for decimal
point, but how are you going to determine where the decimal point is
in the binary representation?  You'll still be dependent on knowing the
server's lc_monetary locale.

            regards, tom lane

On 11/30/2011 10:36 PM, Tom Lane wrote:
> Mikko Tiihonen<mikko.tiihonen@nitorcreations.com>  writes:
>> I was planning to add the binary format support for money to the
>> jdbc driver when I had some free time. It will solve all parsing
>> problems related to locale and give nice speed boost at the same time.
> I don't think that's exactly a magic bullet.  Yes, it'll mean you don't
> need to know the locale's currency symbol or what it uses for decimal
> point, but how are you going to determine where the decimal point is
> in the binary representation?  You'll still be dependent on knowing the
> server's lc_monetary locale.

I thought `money' was due to go away at some point? Isn't it about time
to start logging:

    WARNING: The "money" datatype is deprecated. Use NUMERIC instead.

... whenever MONEY is used in DDL? Issues like locale-dependent
representation make it all the more painful, and I can't help but wonder
if it's worth trying to fix it.

--
Craig Ringer