PreparedStatement.executeBatch() fails for VARBINARY function parameters with bulk copy enabled

[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)

JAVA/JVM version

1.8.0_352

Table schema

CREATE TABLE FunctionInsertTest (
ID int PRIMARY KEY,
Data varbinary(max)
);

Problem description

When using Bulk Copy for Batch Insert with setUseBulkCopyForBatchInsert(true), inserting data into a VARBINARY(MAX) column via PreparedStatement with SQL functions like encryptbykey() fails. The failure occurs when using setString() for the parameter that is passed to the SQL function.

Reproduction Code

import com.microsoft.sqlserver.jdbc.SQLServerConnection;
import java.sql.*;

public class FunctionInsert {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlserver://<HOST>:1433;databaseName=<DB>;trustServerCertificate=true;";
        String insertSQL = "INSERT INTO FunctionInsertTest (ID, Data) VALUES (?, encryptbykey(key_guid('SYMM_KEY'),?));";

        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, 4);
            statement.setString(2, "MySecretKey123");  // <--- causes failure

            statement.addBatch();
            statement.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Expected behavior

PreparedStatement.executeBatch() should work correctly when a String parameter is passed to a SQL Server function or expression that expects VARBINARY input, even when bulk copy for batch insert is enabled. If bulk copy does not support certain data types or functions, it should fallback to the normal batch insert approach to ensure execution succeeds without errors.

Actual behavior

Bulk copy fails with an exception, and the data is not inserted.

Error message/stack trace

java.sql.BatchUpdateException: The string is not in a valid hex format.
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2304)
    at FunctionInsert.main(FunctionInsert.java:33)

Any other details that can be helpful

• The issue does not occur if bulk copy for batch insert is disabled.
• Normal inserts without bulk copy work as expected.
• Related issue - InputStream BulkCopy Issue

JDBC trace logs

Driver trace logs are not attached. The issue can be reproduced consistently with the sample code above.

[pranav-j-zstch1447]

We’re ready to test any potential fix or patch for this issue from our side once it’s available. Please let us know the specific code changes or fixed part so that we can apply them and validate the behaviour.

[muskan124947]

We’ve reproduced the issue and are currently investigating it.
We’ll keep you posted here with any updates.

[muskan124947]

@pranav-j-zstch1447 The issue has been addressed in PR #2845
As documented in the Bulk Copy API limitations, wildcards (?) are the only supported parameters for this functionality.