Issue with Batch Insertion using PreparedStatement in SQL Server

[anjagarg]

Driver version

mssql-jdbc-12.6.2.jre11

SQL Server version

Microsoft SQL Server 2019 (RTM-GDR) (KB5035434) - 15.0.2110.4 (X64) Mar 12 2024 18:25:56 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 22631: )

Client Operating System

Windows 11 Enterprise

JAVA/JVM version

11.0.19

Table schema

CREATE TABLE Issuetest(
[Issuetestsid] varchar NOT NULL,
[Originalsid] varchar NOT NULL,
[Identifier] varchar NULL
) ON [PRIMARY]
GO

Problem description

We are experiencing an issue while executing batch insertion using the preparedStatement.executeBatch() method. The problem is that it is inserting object references instead of actual values for VARCHAR columns.

Expected behavior

Should insert actual values

Actual behavior

Object references are being inserted into the table

Error message/stack trace

No Error.

Any other details that can be helpful

Below are the settings that we did to execute the code

• sendStringParametersAsUnicode=false
• connection.setUseBulkCopyForBatchInsert(true)
• There is no Date type column in the target table. (issue not reproducible with date columns)

Sample Code

private def executeSqlBatch(query: String, records: List[scala.Array[Any]], conn: Connection): Unit = {

var connection = conn

var preparedStatement: PreparedStatement = null

try {

preparedStatement = connection.prepareStatement(query)

records.foreach(record => {

var idx = 0

record.foreach { field =>

idx += 1

preparedStatement.setObject(idx, field)

}

preparedStatement.addBatch()

})

if (records.nonEmpty) {

preparedStatement.executeBatch()

} else {

preparedStatement.execute()

}

if (conn == null && connection != null) {

connection.commit()

}

} catch {

case ex: Exception =>

try {

if (conn == null && connection != null) connection.rollback()

} catch {

case rEx: Exception => logger.error("An exception occurred during rollback:", rEx)

}

throw ex

} finally {

preparedStatement.clearBatch()

preparedStatement.clearParameters()

DbUtils.closeQuietly(preparedStatement)

if (conn == null) {

DbUtils.closeQuietly(connection)

}

}
}

[lilgreenbird]

hi @anjagarg

thanks, we'll take a look and get back to you.

[anjagarg]

Meanwhile, could you please confirm if execute batch insertion is possible for columns of JDBCType DateTime with UseBulkCopyForBatchInsert as true?

[lilgreenbird]

hi @anjagarg

I've been having some issues with your repro after converting it to java and fixing I'm still unable to see your issue. I discovered that everything works as it should if I don't use sendStringParametersAsUnicode=false, if I do use it then I get an error "Received an invalid column length from the bcp client for colid 1.". It seems that it's not able to convert the types. If I specify the JDBC type then it all works ie

        for (int i = 0; i < 100; i++) {
            pstmt.setObject(1, record[0], JDBCType.NVARCHAR);
            pstmt.setObject(2, record[1], JDBCType.NVARCHAR);
            pstmt.setObject(3, record[2], JDBCType.NVARCHAR);

            pstmt.addBatch();
        }

Re using DateTime, it should work are you having trouble with that? You probably need to specify the type in setObject as well

[anjagarg]

Hi, Thank you for your feedback. For datetime column, I have checked that if the target table has column of type DateTime then it throws below error fall back to the original batch insert logic.
'java.lang.IllegalArgumentException: Data type datetime is not supported in bulk copy against Azure Data Warehouse.'

Below is code snippet from sql driver code in SQLServerPreparedStatement class:

switch (jdbctype) {
case microsoft.sql.Types.MONEY:
case microsoft.sql.Types.SMALLMONEY:
case java.sql.Types.DATE:
case microsoft.sql.Types.DATETIME:
case microsoft.sql.Types.DATETIMEOFFSET:
case microsoft.sql.Types.SMALLDATETIME:
case java.sql.Types.TIME:
typeName = ti.getSSTypeName();
form = new MessageFormat(SQLServerException.getErrString("R_BulkTypeNotSupportedDW"));
throw new IllegalArgumentException(form.format(new Object[] {typeName}));

Can you confirm in case DATETIME column exists, will it use Bulk API or not?

[lilgreenbird]

hi @anjagarg

yes that is correct, that is a known limitation. This is explicitly checked here.