Subject | Re: [Firebird-Java] EDITING: JDBC - Firebird: Integer-fields formatted with wrong field properties |
---|---|

Author | Mark Rotteveel |

Post date | 2013-07-23T18:56:09Z |

On 23-7-2013 10:26, Mark Rotteveel wrote:

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()[1] (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()[1] (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

values using:

ceil(log(power(NUM_PREC_RADIX, COLUMN_SIZE))/log(NUM_PREC_RADIX))

(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).

[1]

http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()

[2]

http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

--

Mark Rotteveel

> On Tue, 23 Jul 2013 08:01:53 -0000, "mariuz"I just commented on the ticket:

> <mariuz@...-dev.ro>

> wrote:

>> This happens in LibreOffice 4.1

>>

>> https://bugs.freedesktop.org/show_bug.cgi?id=67179

>

> 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.

> [...]".

>

> See

> http://en.wikipedia.org/wiki/Integer_(computer_science)#Common_integral_data_types

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()[1] (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()[1] (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

values using:

ceil(log(power(NUM_PREC_RADIX, COLUMN_SIZE))/log(NUM_PREC_RADIX))

(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).

[1]

http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()

[2]

http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

--

Mark Rotteveel