Issue with Batch Insertion using PreparedStatement in SQL Server

Question

Hi,

We are experiencing an issue while executing batch insertion using the preparedStatement.executeBatch() method in our Scala code. The problem is that it is inserting object references instead of actual values for VARCHAR columns. Below are the settings and the code snippet we are using:

Settings:

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

Mssql Driver version: Tested with latest driver

mssql-jdbc-12.6.2.jre11

Issue Description:

When using the above method, we notice that for VARCHAR columns, instead of inserting the actual values, object references are being inserted into the table.

Could you please provide guidance on how to resolve this issue or let us know if there are any specific configurations or code adjustments required?

Following is Code Example:

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)

    }

}
}