Subject Re: [Firebird-Java] numeric(15,2) and number of decimal places
Author Helen Borrie
At 09:03 AM 1/03/2007, you wrote:
> > Ok, let me know if this works for you:
>Seems to be Firebird's specifics - when database is created in dialect
>1, it does not really matter what client dialect is passed - server
>always return the column type as SQL_DOUBLE (value of
>XSQLVAR.sqltype=481). When database has dialect 3, server returns the
>column type as SQL_INT64 (XSQLVAR.sqltype=581).
>So this is either bug or feature of the Firebird

It has nothing to do with Firebird. IB 5.6 never supported 64-bit
integers. It's the way that IB 5.6 and earlier handled numeric and
decimal types that were defined with precision higher than 9 (the
maximum precision of the 32-bit integer): it would store the data as
double precision. Although double precision numbers in IB 5.6 are
64-bit, they max out at around 15 digits.

> Jaybird correctly translates what is returned from the server.

On the assumption that the stored data is NUMERIC(15,2). But there
are some traps there for the driver if it assumes that scale is
irrelevant for double precision numbers, because there are no
conditions under which a dialect 3 double precision number
(rdb$field_type 27) has any scale stored (in rdb$field_scale) other than zero.

However - and here's the trap - fields in a dialect 1 database that
were defined as NUMERIC or DECIMAL with a precision higher than 9 are
stored as rdb$field_type 27 *but* they also have a non-zero value in
rdb$field_scale. Hence, Steve's NUMERIC(15,2) field comes through as
type 27 with a scale of minus 2 but Jaybird ignores the scale. When
the driver is set to be a dialect 1 client, it needs to be forced to
read the scale for double precision numbers and recognise them as a
special type if the scale is less than zero.