Subject | Incorrect DatabaseMeta and ResultSetMetaData |
---|---|

Author | tsangccn |

Post date | 2002-08-27T05:43:09Z |

Hello,

I have download firebirdsql from CVS and rebuild.

The results of DatabaseMetaData and ResultSetMetaData have been patched.

But I think that there are still some errors.

I have some questions want to clarify.

Take the sample I used last time

(the VARCHAR(10) column is CHARSET UNICODE_FSS)

Database MetaData

type COLUMN_SIZE DECIMAL_DIGITS

VARCHAR(10) 30 0

INTEGER 10 0

DECIMAL(11,2) 11 2

DECIMAL(5,4) 5 4

DATE 10 0

TIME 8 0

TIMESTAMP 19 0

ResultSet MetaData

type getColumnDisplaySize getPrecision getScale

VARCHAR(10) 30 30 0

INTEGER 11 10 0

DECIMAL(11,2) 8 8 2

DECIMAL(5,4) 4 4 4

DATE 10 10 0

TIME 8 8 0

TIMESTAMP 19 19 0

I have try using NUMERIC instead of DECIMAL, but it returns the same

result.

------

The following is extracted from

http://java.sun.com/j2se/1.4/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

7. COLUMN_SIZE int => column size. For char or date types this is the

maximum number of characters, for numeric or decimal types this is

precision.

8. BUFFER_LENGTH is not used.

9. DECIMAL_DIGITS int => the number of fractional digits

------

The following is extracted from API documentation for ResultSetMetaData

getColumnDisplaySize

--------------------

public int getColumnDisplaySize(int column)

throws SQLException

Indicates the designated column's normal maximum width in characters.

Parameters:

column - the first column is 1, the second is 2, ...

Returns:

the normal maximum number of characters allowed as the width

of the designated column

Throws:

SQLException - if a database access error occurs

getPrecision

------------

public int getPrecision(int column)

throws SQLException

Get the designated column's number of decimal digits.

Parameters:

column - the first column is 1, the second is 2, ...

Returns:

precision

Throws:

SQLException - if a database access error occurs

------

The following is extracted from

http://www.ibphoenix.com/ibp_data_types.html

To summarise the manual:

* Precision means the total number of digits stored - inclusive of

both sides of the decimal point.

* Scale is the number of those digits that are to the right of the

decimal point.

* Numeric means that the largest value stored is dictated by

(precision-scale).

* Decimal means that numbers with larger than precision-scale will

be stored; decimal specifies at least in contrast to numerics' the most.

------

The following is extracted from

http://www.ibphoenix.com/netfradb/search.nfs?a=knowledgebase&l=;KNOWLEDGEBASE.PAGES;ID=%27482%27

Once you use precision 10 or greater, InterBase internally stores the

value as a 64-bit IEEE double float.

If you use precision 9 or less, InterBase uses 16-bit or 32-bit

integer type with a scale value to store the number, ......

------

(1) VARCHAR(10) CHARSET UNICODE_FSS

* According to the API documentation, the COLUMN_SIZE of

DatabaseMetaData should return maximum number of charcters for char (&

varchar)

But interclient and firebirdsql returns 30. I think it should

return 10.

I have tested with DB2 and Oracle, they do return 10.

Please note that 30 is the maximum number of BYTES for a maximum

of 10 UTF8 CHARACTERS.

* According to API doc, ResultSetMetaData.getColumnDisplaySize()

should be maximum width characters, and thus 10 in the sample, but

when count display size, ...

* ResultSetMetaData.getPrecision() - for char or varchar, I think

precision is also the maximum number of characters.

So it should return 10.

I have tested with DB2 and Oracle, they do return 10.

------

(2) NUMERIC(11,2)

* DatabaseMeta COLUMN_SIZE return 11. It is OK.

* The API doc for ResultSetMetaData.getPrecision() returns precision,

so it should be 11.

But firebirdsql returns 8. As it is stored in IEEE double, 8 is

the internal buffer size in BYTES but not the precision.

* The maximum format for NUMERIC(11,2) as displayed is -999999999.99

(without thousands separator).

So ResultSetMetaData.getColumnDisplaySize() should return 13. But

firebirdsql returns 8.

------

(3) NUMERIC(5,4)

* DatabaseMeta COLUMN_SIZE return 5. It is OK.

* The API doc for ResultSetMetaData.getPrecision() returns precision,

so it should be 5.

But firebirdsql returns 4. As it is stored in 16-bit or 32-bit

integer, 4 is the internal buffer size in BYTES but not the precision.

* The maximum format for NUMERIC(5,4) as displayed is -9.9999

(without thousands separator).

So ResultSetMetaData.getColumnDisplaySize() should return 7. But

firebirdsql returns 4.

------

Please check and a patch is appreciated.

Thanks

CN

I have download firebirdsql from CVS and rebuild.

The results of DatabaseMetaData and ResultSetMetaData have been patched.

But I think that there are still some errors.

I have some questions want to clarify.

Take the sample I used last time

(the VARCHAR(10) column is CHARSET UNICODE_FSS)

Database MetaData

type COLUMN_SIZE DECIMAL_DIGITS

VARCHAR(10) 30 0

INTEGER 10 0

DECIMAL(11,2) 11 2

DECIMAL(5,4) 5 4

DATE 10 0

TIME 8 0

TIMESTAMP 19 0

ResultSet MetaData

type getColumnDisplaySize getPrecision getScale

VARCHAR(10) 30 30 0

INTEGER 11 10 0

DECIMAL(11,2) 8 8 2

DECIMAL(5,4) 4 4 4

DATE 10 10 0

TIME 8 8 0

TIMESTAMP 19 19 0

I have try using NUMERIC instead of DECIMAL, but it returns the same

result.

------

The following is extracted from

http://java.sun.com/j2se/1.4/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

7. COLUMN_SIZE int => column size. For char or date types this is the

maximum number of characters, for numeric or decimal types this is

precision.

8. BUFFER_LENGTH is not used.

9. DECIMAL_DIGITS int => the number of fractional digits

------

The following is extracted from API documentation for ResultSetMetaData

getColumnDisplaySize

--------------------

public int getColumnDisplaySize(int column)

throws SQLException

Indicates the designated column's normal maximum width in characters.

Parameters:

column - the first column is 1, the second is 2, ...

Returns:

the normal maximum number of characters allowed as the width

of the designated column

Throws:

SQLException - if a database access error occurs

getPrecision

------------

public int getPrecision(int column)

throws SQLException

Get the designated column's number of decimal digits.

Parameters:

column - the first column is 1, the second is 2, ...

Returns:

precision

Throws:

SQLException - if a database access error occurs

------

The following is extracted from

http://www.ibphoenix.com/ibp_data_types.html

To summarise the manual:

* Precision means the total number of digits stored - inclusive of

both sides of the decimal point.

* Scale is the number of those digits that are to the right of the

decimal point.

* Numeric means that the largest value stored is dictated by

(precision-scale).

* Decimal means that numbers with larger than precision-scale will

be stored; decimal specifies at least in contrast to numerics' the most.

------

The following is extracted from

http://www.ibphoenix.com/netfradb/search.nfs?a=knowledgebase&l=;KNOWLEDGEBASE.PAGES;ID=%27482%27

Once you use precision 10 or greater, InterBase internally stores the

value as a 64-bit IEEE double float.

If you use precision 9 or less, InterBase uses 16-bit or 32-bit

integer type with a scale value to store the number, ......

------

(1) VARCHAR(10) CHARSET UNICODE_FSS

* According to the API documentation, the COLUMN_SIZE of

DatabaseMetaData should return maximum number of charcters for char (&

varchar)

But interclient and firebirdsql returns 30. I think it should

return 10.

I have tested with DB2 and Oracle, they do return 10.

Please note that 30 is the maximum number of BYTES for a maximum

of 10 UTF8 CHARACTERS.

* According to API doc, ResultSetMetaData.getColumnDisplaySize()

should be maximum width characters, and thus 10 in the sample, but

when count display size, ...

* ResultSetMetaData.getPrecision() - for char or varchar, I think

precision is also the maximum number of characters.

So it should return 10.

I have tested with DB2 and Oracle, they do return 10.

------

(2) NUMERIC(11,2)

* DatabaseMeta COLUMN_SIZE return 11. It is OK.

* The API doc for ResultSetMetaData.getPrecision() returns precision,

so it should be 11.

But firebirdsql returns 8. As it is stored in IEEE double, 8 is

the internal buffer size in BYTES but not the precision.

* The maximum format for NUMERIC(11,2) as displayed is -999999999.99

(without thousands separator).

So ResultSetMetaData.getColumnDisplaySize() should return 13. But

firebirdsql returns 8.

------

(3) NUMERIC(5,4)

* DatabaseMeta COLUMN_SIZE return 5. It is OK.

* The API doc for ResultSetMetaData.getPrecision() returns precision,

so it should be 5.

But firebirdsql returns 4. As it is stored in 16-bit or 32-bit

integer, 4 is the internal buffer size in BYTES but not the precision.

* The maximum format for NUMERIC(5,4) as displayed is -9.9999

(without thousands separator).

So ResultSetMetaData.getColumnDisplaySize() should return 7. But

firebirdsql returns 4.

------

Please check and a patch is appreciated.

Thanks

CN