setBinaryStream() fails with Bulk Copy for Batch Insert in JDBC Driver 13.2.0

[pranav-j-zstch1447]

Driver version

Microsoft JDBC Driver for SQL Server 13.2.0

SQL Server version

Microsoft SQL Server 2019 (RTM)

Client Operating System

Ubuntu 22.04 LTS (x86_64).
Developer Edition (64-bit) on Linux (Ubuntu 16.04 LTS)

JAVA version

1.8.0_352

Table schema

CREATE TABLE BinaryInsertTest (
    ID   INT PRIMARY KEY,
    Data VARBINARY(MAX)
);

Reproduction code

import com.microsoft.sqlserver.jdbc.SQLServerConnection;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.*;

public class BinaryInsert {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlserver://<HOST>:1433;databaseName=<DB>;trustServerCertificate=true;";
        String createSql = "CREATE TABLE BinaryInsertTest (ID int PRIMARY KEY, Data varbinary(max));";
        String insertSQL = "INSERT INTO BinaryInsertTest (ID, Data) VALUES (?, ?);";

        String data = "Sample string to be inserted as binary data.";
        byte[] bytes = data.getBytes(StandardCharsets.UTF_8);
        InputStream inputStream = new ByteArrayInputStream(bytes);

        try (Connection connection = DriverManager.getConnection(url, "sa", "<PASSWORD>")) {
            SQLServerConnection sqlServerConnection = (SQLServerConnection) connection;
            sqlServerConnection.setUseBulkCopyForBatchInsert(true);
            sqlServerConnection.setBulkCopyForBatchInsertBatchSize(1);

            PreparedStatement statement = connection.prepareStatement(insertSQL);
            statement.setObject(1, 1);
            statement.setBinaryStream(2, inputStream);

            statement.addBatch();
            statement.executeBatch();
        }
    }
}

Problem description

When using Bulk Copy for Batch Insert with setUseBulkCopyForBatchInsert(true) and inserting data into a VARBINARY(MAX) column using PreparedStatement.setBinaryStream(), the insert fails with the following error:com.microsoft.sqlserver.jdbc.SQLServerException: The string is not in a valid hex format.

Root cause:

After analyzing the JDBC driver source (v13.2.0), the suspected issue lies in the method
com.microsoft.sqlserver.jdbc.SQLServerBulkBatchInsertRecord.convertValue(ColumnMetadata, Object).

In this method, the case for Types.BINARY, Types.VARBINARY, Types.LONGVARBINARY, and Types.BLOB handles byte[] inputs correctly, but does not handle InputStream values. As a result, when setBinaryStream() is used, the code incorrectly calls data.toString() and later tries to interpret the resulting Java object string (e.g. java.io.ByteArrayInputStream@4e25154f) as a hex literal — leading to the “invalid hex format” exception.

Here’s the problematic section (from the unmodified v13.2.0 driver):

case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.BLOB: {
if (data instanceof byte[]) {
    /*
    * if the binary data comes in as a byte array through setBytes through Bulk Copy for Batch Insert
    * API, don't turn the binary array into a string.
    */
    return data;
} else {
    // Strip off 0x if present.
    String binData = data.toString().trim();
    if (binData.startsWith("0x") || binData.startsWith("0X")) {
        return binData.substring(2);
    } else {
        return binData;
    }
}

This causes any InputStream (used via setBinaryStream()) to be treated as a string instead of binary data.

Expected behavior

PreparedStatement.setBinaryStream() should insert binary data correctly when bulk copy for batch insert is enabled, just like when setBytes() is used.
The driver should handle InputStream as binary input in convertValue().

Actual behavior

When using setBinaryStream() with bulk copy for batch insert enabled, the driver throws the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The string is not in a valid hex format.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:250)
    at com.microsoft.sqlserver.jdbc.ParameterUtils.hexToBin(ParameterUtils.java:21)
    at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeColumnToTdsWriter(SQLServerBulkCopy.java:2619)
    at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeColumn(SQLServerBulkCopy.java:3307)
    at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeBatchData(SQLServerBulkCopy.java:3886)
    at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1703)
    ...

Any other details that can be helpful

Proposed fix:
Modify the convertValue() method to handle InputStream objects in the same way as byte[] for binary types.
Current condition:

if (data instanceof byte[]) {
    return data;
}

Proposed change:

if (data instanceof byte[] || data instanceof InputStream) {
    return data;
}

After applying this fix, inserting binary data using setBinaryStream() works correctly under bulk copy batch insert mode.

The fix aligns setBinaryStream() with existing handling of setBytes() for binary data.

This behaviour was verified by modifying and testing the driver locally , the issue no longer reproduces.

JDBC trace logs

Driver trace logs are not attached here since the issue can be reproduced consistently using the provided test case.
However, if required, I can generate and attach detailed JDBC driver trace logs for further debugging.

[machavan]

Thanks @pranav-j-zstch1447 for reporting this. We are analyzing if the proposed fix is good for all scenarios.

[muskan124947]

The issue is addressed in Add support for InputStream data type using setBinaryStream() for bulk copy for batch insert API by muskan124947