Subject | Re: [Firebird-Java] numeric(15,2) and number of decimal places |
---|---|

Author | Helen Borrie |

Post date | 2007-02-28T23:38:29Z |

At 09:03 AM 1/03/2007, you wrote:

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.

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.

Helen

> > Ok, let me know if this works for you:It has nothing to do with Firebird. IB 5.6 never supported 64-bit

>

>Confirmed.

>

>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

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.

Helen