Issue with Batch Insertion using PreparedStatement in SQL Server
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=falseconnection.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)
}
}
}
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=falseconnection.setUseBulkCopyForBatchInsert(true)There is no Date type column in the target table. (issue not reproducible with date columns)Mssql Driver version: Tested with latest drivermssql-jdbc-12.6.2.jre11Issue 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 = connvar preparedStatement: PreparedStatement = nulltry {preparedStatement = connection.prepareStatement(query)records.foreach(record => {var idx = 0record.foreach { field =>idx += 1preparedStatement.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)}}} Read More
