Issue Details (XML | Word | Printable)

Key: JDBC-426
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Steve Peterson
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

DatabaseMetadata.getColumn returns 0 for DECIMAL_DIGITS

Created: 24/Mar/16 06:18 PM   Updated: 20/Aug/16 01:13 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 2.2.7, Jaybird 2.2.8, Jaybird 2.2.9, Jaybird 2.2.10
Fix Version/s: Jaybird 2.2.11, Jaybird 3.0.0

Environment: JDK 1.8, Firebird 2.5.1


 Description  « Hide
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)
}

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 25/Mar/16 10:45 AM - edited
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).

Mark Rotteveel added a comment - 25/Mar/16 10:54 AM
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.

Mark Rotteveel added a comment - 25/Mar/16 11:02 AM
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'

Steve Peterson added a comment - 25/Mar/16 12:30 PM
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]

Mark Rotteveel added a comment - 25/Mar/16 01:31 PM - edited
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 d9a223599fef96f4e4e2510b749e8c1d966ddc9c