[Bug]: Parameter metadata misclassifies NUMERIC and character types

[fskorgen]

Apache Hop version?

2.19

Java version?

21

Operating system

Windows

What happened?

Affected: 2.19.0 and earlier.

Database.getParameterMetaData has a separate java.sql.Types mapping from the result-metadata
path. That mapping is already inconsistent in two directly reproducible ways:

• The string case is only case java.sql.Types.CHAR, java.sql.Types.VARCHAR, so LONGVARCHAR,
  NCHAR, NVARCHAR and LONGNVARCHAR all fall through to ValueMetaNone — note LONGVARCHAR is
  not a national-character type and has been in JDBC since 1.0. A SQL Server parameter is untyped
  whenever the driver reports Types.NVARCHAR, which is its normal string type.
• Types.NUMERIC is grouped with BIGINT, INTEGER, SMALLINT and TINYINT, so
  NUMERIC(18,4) becomes ValueMetaInteger and loses its scale.
• Types.TIMESTAMP_WITH_TIMEZONE (2014) and Types.TIME_WITH_TIMEZONE (2013) have no case either,
  so a zoned parameter reaches ValueMetaNone rather than a date type. The result-metadata path
  does not map them either, but there it at least degrades to String; what the correct mapping for
  these two constants should be is a question of its own and is not part of this report.

The later length > 18 || precision > 18 check does not repair the numeric problem: scale-bearing
values at or below that threshold remain Integer. The parameter path also differs from result
metadata for NUMERIC(p,0), values wider than 18 digits, and whether TIMESTAMP uses Hop Date or
Timestamp.

Steps to reproduce

Supply a ParameterMetaData implementation that returns Types.NVARCHAR for one parameter and
Types.NUMERIC, precision 18, scale 4 for another.

Expected: String, and Number/BigNumber with the reported scale.
Actual: getParameterMetaData() returns ValueMetaNone for the first and ValueMetaInteger
for the second.

Suggested fix

Extract the java.sql.Types classification — which constant maps to which Hop type — into one
helper used by both the parameter and the result path. What should not happen is calling
getDataTypeFromKnownSqlType wholesale from here: it also derives length and precision, and that
part is the subject of a separate report. The parameter path needs the length and scale that
ParameterMetaData supplies. It is the type classification, not the whole method, that must stop
being maintained in two drifting switches.

Issue Priority

Priority: 2

Issue Component

Component: Database