dav-git-ide opened a new issue, #8207:
URL: https://github.com/apache/hop/issues/8207
### 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
1. Install the SAP HANA JDBC driver (ngdbc.jar) in Apache Hop.
2. Create a Relational Database Connection using "Generic database".
3. Configure:
Driver class:
com.sap.db.jdbc.Driver
JDBC URL:
jdbc:sap://<host>:<port>
4. Test the connection successfully.
5. Create a pipeline with a Table Input transform.
6. Run a query against a table containing a VARBINARY column, for example:
SELECT *
FROM MARA
7. Preview or execute the pipeline.
8. 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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
