Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DatabaseMetadata.getColumn returns 0 for DECIMAL_DIGITS [JDBC426] #467

Closed
firebird-automations opened this issue Mar 24, 2016 · 9 comments
Closed

Comments

@firebird-automations
Copy link

Submitted by: Steve Peterson (speterson)

I get metadata for a column of type NUMERIC(15,2), then look at the column DECIMAL_DIGITS, it returns 0; it used to return "2" because that is how many digits to the right of the decimal are in the field. So when I run something like the below program the entry for MYNUMERICCOLUMN that is of type Numeric(15,2) says:

...MYNUMERICCOLUMN, p=15, scale=0

DatabaseMetaData md = connection.getMetaData();
ResultSet r = md.getColumns(null, null,"MYTABLETNAME", "%");

//Loop through each entry to get the metadata for each column in table
while ((r != null) && (r.next())) {
String columnName = r.getString("COLUMN_NAME");
int precision = r.getInt("COLUMN_SIZE");
int scale = r.getShort("DECIMAL_DIGITS");
System.out.prinln(columnName + "p=" + precision + ", scale=" + scale)
}

Commits: bd224c1 4a46817

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Jaybird has tests for this (TestFBDatabaseMetaDataColumns) and those don't fail. What is the exact CREATE TABLE you have used, and is the numeric column defined through a domain or directly? And is your database dialect 1 or dialect 3?

Also: 2.5.1 is pretty old (and a lot of bugs have been fixed since then).

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

I have just tested this with a domain and without a domain, and with dialect 1 and dialect 3, and DECIMAL_DIGITS returns 2 as expected.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

What do you get for this query:

SELECT RF.RDB$RELATION_NAME AS RELATION_NAME,
RF.RDB$FIELD_NAME AS FIELD_NAME,
F.RDB$FIELD_TYPE AS FIELD_TYPE,
F.RDB$FIELD_SUB_TYPE AS FIELD_SUB_TYPE,
F.RDB$FIELD_PRECISION AS FIELD_PRECISION,
F.RDB$FIELD_SCALE AS FIELD_SCALE,
F.RDB$FIELD_LENGTH AS FIELD_LENGTH,
F.RDB$CHARACTER_LENGTH AS CHAR_LEN
FROM RDB$RELATION_FIELDS RF,
RDB$FIELDS F
WHERE RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
AND RF.RDB$RELATION_NAME = 'MYTABLETNAME'

@firebird-automations
Copy link
Author

Commented by: Steve Peterson (speterson)

Thanks for taking a look. The database is SQL dialect 1 -- sorry, it is a legacy system.

We created a table called, 'PRICESTORE' using the following:

CREATE TABLE PRICESTORE
(
PRODID Integer NOT NULL,
PRICE Numeric(15,2),
PRIMARY KEY (PRODID)
);

For the above metadata query, I get the below results. The field with the problem is "PRICE".

PRICESTORE PRODID 8 0 0 0 4 [null]
PRICESTORE PRICE 27 [null] [null] -2 8 [null]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

The test I created earlier seems to have been using dialect 3 even though I created a dialect 1 database. The problem is that the metadata is 'wrong' the NUMERIC(15,2) looks like a DOUBLE PRECISION to Jaybird because the subtype is NULL, I can reproduce this when I create it manually in dialect 1.

I seem to have broken it between 2.1.6 and 2.2.0, specifically by commit d9a2235

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Version: Jaybird 2.2.9 [ 10691 ]

Version: Jaybird 2.2.8 [ 10664 ]

Version: Jaybird 2.2.7 [ 10660 ]

Fix Version: Jaybird 2.2.11 [ 10751 ]

Fix Version: Jaybird 3.0 [ 10440 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Fixed for Jaybird 2.2.11

Jaybird 3: bd224c1
Jaybird 2.2: 4a46817

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment