Re: [Firebird-Java] EDITING: JDBC - Firebird: Integer-fields formatted with wrong field properties
- On 23-7-2013 10:26, Mark Rotteveel wrote:
> On Tue, 23 Jul 2013 08:01:53 -0000, "mariuz"I just commented on the ticket:
>> This happens in LibreOffice 4.1
> I will take a look at it, but at first glance the fact it says length is
> 10 seems right. The thing is a 32 bit integer has a maximum length (or
> precision) of 10, and - IIRC - it is what Jaybird returns as the length in
> the DatabaseMetaData.getColumns() in column COLUMN_SIZE, as required by
> DatabaseMetaData.getColumns(): "The COLUMN_SIZE column specifies the column
> size for the given column. For numeric data, this is the maximum precision.
I am the developer of Jaybird (the JDBC driver for Firebird).
The actual bugs is in the creation stage where the length is displayed
as 32 (or 64 for BIGINT). The 'problem' is that Jaybird 2.2.3 (and
earlier) in DatabaseMetaData.getTypeInfo() returns the binary precision
(ie 32 base 2) of the datatype, while the getColumns() method declares
it in decimal (ie 10 base 10) precision.
See section 4.4.2 of SQL:2011 Foundation:
"An exact numeric type has a precision P and a scale S. P is a positive
integer that determines the number of significant digits in a particular
radix R, where R is either 2 or 10."
The value of 32 comes from getTypeInfo() (column PRECISION, combined
with column NUM_PREC_RADIX). Jaybird 2.2.3 returns 32 base 2 for integer
(64 base 2 for BIGINT and 16 base 2 for SMALLINT.
The value of 10 shown after creation comes from
DatabaseMetaData.getColumns() (column COLUMN_SIZE, combined with
column NUM_PREC_RADIX). Jaybird returns 10 here if the column is an
integer, and 19 for a bigint and 5 for a smallint (all with base 10 as
specified in NUM_PREC_RADIX).
This has been changed for the upcoming 2.3 release of Jaybird, so
getTypeInfo() uses the same base as getColumns (ie base 10).
On the Libre Office side, if this is intended to always display the
number of digits (characters), it can be achieved by transforming the
(with log = 10log, power = power function, ceil = ceiling function).
For 32 base 2 this will yield 20 for bigint, which is not entirely
correct (it should be 19 for the signed bigint that Firebird has), but
it is close enough for most purposes. For numbers in base 10 this will
simply return the original value.
Changing this will yield another bug with current versions of Jaybird,
as getTypeInfo() incorrectly uses radix 2 for other lengths as well
(those lengths are specified in base 10, but NUM_PREC_RADIX reports 2).