[Bug]: SQL Server string DDL loses Unicode and uses legacy TEXT

[fskorgen]

Apache Hop version?

2.19

Java version?

21

Operating system

Windows

What happened?

Affected: 2.19.0 and earlier.

MsSqlServerDatabaseMeta.getFieldDefinition() emits VARCHAR(n) for every Hop String shorter than
8000 characters and TEXT for longer Strings. It does not inspect the original JDBC type:

case IValueMeta.TYPE_STRING:
  if (length < getMaxVARCHARLength()) {
    retval += length > 0 ? "VARCHAR(" + length + ")" : "VARCHAR(100)";
  } else {
    retval += "TEXT";
  }

A SQL Server nvarchar(20) column is read as Hop String with original type Types.NVARCHAR, but
the generated definition is VARCHAR(20). Any Hop String with metadata length at or above 8000
becomes TEXT, regardless of whether the original JDBC type was national-character data. The native
SQL Server meta inherits the same method.

This breaks a metadata round trip in two ways:

• VARCHAR cannot represent characters outside the column collation's code page, so an existing
  Unicode column can become lossy when recreated. SQL Server uses NCHAR/NVARCHAR for
  UTF-16 Unicode data.
• Microsoft states that text, ntext and image will be removed
  and recommends VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.

There is also a query-performance consequence. Microsoft's JDBC driver
sends String parameters as Unicode by default,
and SQL Server gives NVARCHAR higher type precedence than VARCHAR. Comparing that parameter
with a generated VARCHAR key can force conversion of the indexed column; the driver documentation
explicitly notes the implicit-conversion overhead for VARCHAR/CHAR columns.

Steps to reproduce

1. Create a SQL Server table with an nvarchar(20) column and an nvarchar(max) column.
2. Read it and generate the table DDL from the resulting row metadata.

Expected: NVARCHAR(20) and NVARCHAR(MAX).
Actual: VARCHAR(20), and TEXT for the long one.

Suggested fix

Preserve Types.NCHAR, Types.NVARCHAR and Types.LONGNVARCHAR as NCHAR, NVARCHAR and
NVARCHAR(MAX) when original-column metadata is available. Replace legacy TEXT with
VARCHAR(MAX) for non-national Strings. If new Hop String fields should remain VARCHAR by default
for compatibility, expose an explicit SQL Server connection option for Unicode DDL rather than
discarding the type of columns that were actually read. Observe SQL Server's declared limits — 8000
bytes for VARCHAR(n) and 4000 byte-pairs for NVARCHAR(n) — before switching to (MAX).

Issue Priority

Priority: 2

Issue Component

Component: Database

[hansva]

same here, please do these against current codebase