I'm on holidays, as soon as I return I'll review that.

----- Original Message -----
From: "tsangccn" <tsang_cn@...>
To: <Firebird-Java@yahoogroups.com>
Sent: Friday, August 09, 2002 10:16 AM
Subject: [Firebird-Java] Incorrect values from DatabaseMetaData and ResultSetMetaData


> Hello,
>
> I found the following result from a sample
> (the VARCHAR(10) column is CHARSET UNICODE_FSS)
>
> Database MetaData (interclient)
>
> type COLUMN_SIZE DECIMAL_DIGITS
>
> VARCHAR(10) 30 0
> INTEGER 10 0
> DECIMAL(11,2) 19 2
> DECIMAL(5,4) 10 4
> DATE 10 0
> TIME 8 0
> TIMESTAMP 19 0
>
> ResultSet MetaData (interclient)
>
> type getColumnDisplaySize getPrecision getScale
>
> VARCHAR(10) 10 10 0
> INTEGER 11 10 0
> DECIMAL(11,2) 22 19 2
> DECIMAL(5,4) 13 10 4
> DATE 10 10 0
> TIME 8 8 0
> TIMESTAMP 19 19 0
>
> Database MetaData (firebirdsql)
>
> type COLUMN_SIZE DECIMAL_DIGITS
>
> VARCHAR(10) 30 0
> INTEGER 4 0
> DECIMAL(11,2) 11 2
> DECIMAL(5,4) 5 4
> DATE 4 0
> TIME 4 0
> TIMESTAMP 8 0
>
> ResultSet MetaData (firebirdsql)
>
> type getColumnDisplaySize getPrecision getScale
>
> VARCHAR(10) 30 30 0
> INTEGER 4 4 0
> DECIMAL(11,2) 8 8 2
> DECIMAL(5,4) 4 4 4
> DATE 10 4 0
> TIME 4 4 0
> TIMESTAMP 8 8 0
>
> Then I read the API documentation, and find the result
>
> Database MetaData
> * 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.
> * BUFFER_LENGTH is not used.
> * DECIMAL_DIGITS int => the number of fractional digits
>
> ResultSet MetaData
> * getColumnDisplaySize() => the normal maximum width in characters
> * getPrecison() => the number of decimal digits
> * getScale() => the number of digits to right of the decimal point
>
> So, I have the following comment:
> * VARCHAR(10) - In terms of COLUMN_SIZE, it should be 10 (characters).
> In terms of buffer size, as it is defined as CHARSET UNICODE_FSS, in
> Firebird, it is stored in UTF8, so the maximum allowd size is 30 (bytes)
> Depend on the fonts, display size may be 20 (for most
> caes of double-byte characters)
> * INTEGER - the maximum value is 2147483648. So it is 10 digits width,
> but in case of precision, COLUMN_SIZE should be 9 (decimal digits)
> while display size is 11 characters (10 + 1 for sign)
> firebirdsql is wrong, as 4 (bytes) is the buffer size but
> not the precision or display size
> * DECIMAL(11,2) - COLUMN_SIZE should be 11 (precision), display size
> should be 13 (11 + 1 for sign + 1 for decimal point). If it is stored
> as double, buffer size is 8 (bytes)
> interclient returns miserable figure.
> firebirdsql is right for database metadata, but wrong
> for resultset metadata.
> * DECIMAL(5,4) - COLUMN_SIZE should be 5 (precision), display size
> should be 7 (5 + 1 for sign + 1 for decimal point). If it is stored
> as float, buffer size is 4 (bytes)
> interclient returns miserable figure.
> firebirdsql is right for database metadata, but wrong
> for resultset metadata.
> * DATE - the ISO format for SQL date is YYYY-MM-DD. So COLUMN_SIZE and
> display size is 10. The buffer size in bytes depends on database
> implmentation.
> * TIME - the ISO format for SQL time is HH:MM:SS. So COLUMN_SIZE and
> display size is 8. The buffer size in bytes depends on database
> implementation.
> * TIMESTAMP - the ISO format for SQL timestamp is
> YYYY-MM-DD.HH:MM:SS.SSSSSS. So COLUMN_SIZE and display size is 26.
> The buffer size in bytes depends on database implementation.
>
> Display size, precision, scale, and number of bytes for data items are
> the fundamental of computer data processing.
> Be careful, in terms of UNICODE, or multi-byte characters, the number
> of characters and number of bytes is different.
>
> So the result should be as follows
>
> Database MetaData
>
> type COLUMN_SIZE DECIMAL_DIGITS BUFFER_LENGTH
>
> VARCHAR(10) 10 0 30
> INTEGER 9 0 4
> DECIMAL(11,2) 11 2 8 (if stored as dobule)
> DECIMAL(5,4) 5 4 4 (if stored as float)
> DATE 10 0 10 (if stored as character), 8
> (if stored as long)
> TIME 8 0 8 (if stored as character or long)
> TIMESTAMP 26 0 26 (if stored as character), 8
> (if stored as long)
>
> ResultSet MetaData
>
> type getColumnDisplaySize getPrecision getScale
>
> VARCHAR(10) 20 10 0
> INTEGER 11 9 0
> DECIMAL(11,2) 13 11 2
> DECIMAL(5,4) 7 5 4
> DATE 10 10 0
> TIME 8 8 0
> TIMESTAMP 26 26 0
>
> BUFFER_LENGTH depends on internal representation of the data, so it is
> vendor dependent.
> But COLUMN_SIZE, DECIMAL_DIGITS, getColumnDisplaySize, getPrecision,
> getScale (I think) should be vendor independent, and consistent with
> the value used in CREATE TABLE.
> Please check if interclient and firebirdsql returns incorrect value.
> And a patch is appreciated.
>
> Thanks
>
> CN
>
>
>
> To unsubscribe from this group, send an email to:
> Firebird-Java-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>