Apache Hop version?
2.19
Java version?
open jdk 21
Operating system
Windows
What happened?
When using a Generic Database connection with the SAP HANA JDBC driver, Apache Hop fails when reading VARBINARY columns.
A query such as:
SELECT *
FROM MARA
is successfully executed by SAP HANA, but Hop fails while reading the ResultSet with:
Unable to get value 'Binary' from database resultset
SAP DBTech JDBC: Cannot convert SQL type VARBINARY to Java type java.sql.Blob.
The failing column is exposed by SAP HANA JDBC as VARBINARY.
According to the SAP HANA JDBC documentation, VARBINARY should be handled as binary byte data, while BLOB is mapped to java.sql.Blob.
It appears that Hop Generic Database maps the VARBINARY column to Hop Binary and then retrieves it using java.sql.Blob handling instead of byte[] / ResultSet.getBytes().
Steps to Reproduce
-
Install the SAP HANA JDBC driver (ngdbc.jar) in Apache Hop.
-
Create a Relational Database Connection using "Generic database".
-
Configure:
Driver class:
com.sap.db.jdbc.Driver
JDBC URL:
jdbc:sap://:
-
Test the connection successfully.
-
Create a pipeline with a Table Input transform.
-
Run a query against a table containing a VARBINARY column, for example:
SELECT *
FROM MARA
-
Preview or execute the pipeline.
-
Hop starts reading the ResultSet but fails when it reaches the VARBINARY column.
Observed error:
Unable to get value 'Binary' from database resultset, index 189
SAP DBTech JDBC:
Cannot convert SQL type VARBINARY to Java type java.sql.Blob.
Expected Behavior
VARBINARY columns returned by the SAP HANA JDBC driver should be read as binary byte data, for example using ResultSet.getBytes() / byte[] handling.
A VARBINARY column should not be requested from the JDBC driver as java.sql.Blob.
Queries containing VARBINARY columns should therefore be readable through a Generic Database connection without requiring the user to exclude or cast those columns manually.
Additional Information / Possible Cause
The issue appears to be in the generic JDBC type handling.
SAP HANA JDBC distinguishes between:
VARBINARY -> binary byte data
BLOB -> java.sql.Blob
However, Hop reports the VARBINARY column as Hop type "Binary" and appears to retrieve it using Blob handling.
This causes the SAP HANA JDBC driver to reject the conversion:
Cannot convert SQL type VARBINARY to Java type java.sql.Blob.
A possible fix would be to distinguish JDBC Types.VARBINARY / BINARY / LONGVARBINARY from Types.BLOB when retrieving values from the ResultSet.
For VARBINARY/BINARY types, ResultSet.getBytes() or equivalent byte[] handling would seem more appropriate.
Additional Context / References
SAP HANA JDBC documentation:
https://help.sap.com/docs/SAP_HANA_CLIENT/f1b440ded6144a54ada97ff95dac7adf/109397c2206a4ab2a5386d494f4cf75e.html?locale=en-US
Issue Priority
Priority: 0
Issue Component
Component: Transforms, Component: Database
Apache Hop version?
2.19
Java version?
open jdk 21
Operating system
Windows
What happened?
When using a Generic Database connection with the SAP HANA JDBC driver, Apache Hop fails when reading VARBINARY columns.
A query such as:
SELECT *
FROM MARA
is successfully executed by SAP HANA, but Hop fails while reading the ResultSet with:
Unable to get value 'Binary' from database resultset
SAP DBTech JDBC: Cannot convert SQL type VARBINARY to Java type java.sql.Blob.
The failing column is exposed by SAP HANA JDBC as VARBINARY.
According to the SAP HANA JDBC documentation, VARBINARY should be handled as binary byte data, while BLOB is mapped to java.sql.Blob.
It appears that Hop Generic Database maps the VARBINARY column to Hop Binary and then retrieves it using java.sql.Blob handling instead of byte[] / ResultSet.getBytes().
Steps to Reproduce
Install the SAP HANA JDBC driver (ngdbc.jar) in Apache Hop.
Create a Relational Database Connection using "Generic database".
Configure:
Driver class:
com.sap.db.jdbc.Driver
JDBC URL:
jdbc:sap://:
Test the connection successfully.
Create a pipeline with a Table Input transform.
Run a query against a table containing a VARBINARY column, for example:
SELECT *
FROM MARA
Preview or execute the pipeline.
Hop starts reading the ResultSet but fails when it reaches the VARBINARY column.
Observed error:
Unable to get value 'Binary' from database resultset, index 189
SAP DBTech JDBC:
Cannot convert SQL type VARBINARY to Java type java.sql.Blob.
Expected Behavior
VARBINARY columns returned by the SAP HANA JDBC driver should be read as binary byte data, for example using ResultSet.getBytes() / byte[] handling.
A VARBINARY column should not be requested from the JDBC driver as java.sql.Blob.
Queries containing VARBINARY columns should therefore be readable through a Generic Database connection without requiring the user to exclude or cast those columns manually.
Additional Information / Possible Cause
The issue appears to be in the generic JDBC type handling.
SAP HANA JDBC distinguishes between:
VARBINARY -> binary byte data
BLOB -> java.sql.Blob
However, Hop reports the VARBINARY column as Hop type "Binary" and appears to retrieve it using Blob handling.
This causes the SAP HANA JDBC driver to reject the conversion:
Cannot convert SQL type VARBINARY to Java type java.sql.Blob.
A possible fix would be to distinguish JDBC Types.VARBINARY / BINARY / LONGVARBINARY from Types.BLOB when retrieving values from the ResultSet.
For VARBINARY/BINARY types, ResultSet.getBytes() or equivalent byte[] handling would seem more appropriate.
Additional Context / References
SAP HANA JDBC documentation:
https://help.sap.com/docs/SAP_HANA_CLIENT/f1b440ded6144a54ada97ff95dac7adf/109397c2206a4ab2a5386d494f4cf75e.html?locale=en-US
Issue Priority
Priority: 0
Issue Component
Component: Transforms, Component: Database