2

I am testing Firebird NUMERIC / DECIMAL fields behavior in JDBC (Jaybird) in Java.

When using FBResultSetMetaData to check the column properties (for using ResultSet.getObject method for e.g. SELECT * FROM NUMERICTEST query) I get the precision (FBResultSetMetaData.getPrecision) and scale (FBResultSetMetaData.getScale) exactly as declared in table definition in Firebird, e.g.

NUMERIC(3,2) field ... precision 3, scale 2
DECIMAL(3,2) field ... precision 3, scale 2

When using FBParameterMetaData to check the parameter properties (for using PreparedStatement.setObject method for e.g. INSERT INTO NUMERICTEST VALUES (?, ?) query) I get for the same fields different values of precision (FBParameterMetaData.getPrecision) and scale (FBParameterMetaData.getScale).

NUMERIC(3,2) field ... precision 4, scale 2
DECIMAL(3,2) field ... precision 9, scale 2

I know that these values correspond somehow to the internal db storage type of these fields (smallint in the first example, integer in the second?).

What is the reason for the different behavior of FBResultSetMetaData and FBParameterMetaData when related to the same field? It is quite misleading.

Java 8 u 181, Jaybird 3.0.4, Firebird 2.5

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Tom
  • 423
  • 1
  • 4
  • 13

1 Answers1

2

The problem is that a prepare itself only provides the following information for numeric columns (both for parameters and result set columns):

  • type (SQL_SHORT, SQL_LONG, SQL_INT64, SQL_INT128),
  • subtype (0 for SMALLINT/INTEGER/BIGINT/INT128, 1 for NUMERIC and 2 for DECIMAL,
  • scale,
  • length in bytes (2, 4, 8 or 16).

In other words, the declared precision is not available.

For parameters, there is no way to know the declared precision of the column it is compared against or assigned to, as Firebird provides no information that would allow for discovery of the actual precision. So, Jaybird uses the maximum precision for the column type (that is SQL_SHORT: 4, SQL_LONG: 9, SQL_INT64: 18 or SQL_INT128: 38).

For result set columns, Firebird can provide - in some cases - the underlying column name and table, and Jaybird uses this information to query the metadata tables for the actual precision information. This information is not always available, for example calculated/derived columns or columns of queries involving a UNION do not have an underlying column name and table name. If this information is not available, Jaybird will estimate in the same way as for parameters.

The precision of this information makes no real difference: even a column declared as DECIMAL(6,2) (or NUMERIC(6,2)) can store and return values with a maximum precision of 9 (even 10 if you consider the fact that a SQL_LONG is a 32 bit signed integer). For all intents and purposes in Firebird, a DECIMAL(6,2) is actually a DECIMAL(9,2). However, we decided to provide the actual declared precision information when that information is available.

In other words: Jaybird is as precise as it can be, with graceful degradation if it doesn't have enough information.

Disclosure: I'm one of the developers of Jaybird.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197