SQLServerBulkCopy expects ISQLServerBulkData implementation to return sorted set

[PavelPenkov]

Driver version

8.2.2.jre11

SQL Server version

Doesn't matter, it fails before making a query

Client Operating System

Doesn't matter

JAVA/JVM version

Amazon Corretto 11, but doesn't matter

Table schema

Doesn't matter

Problem description

1. Expected behaviour: Bulk copy to succeed.
2. Actual behaviour: Bulk copy fails before even sending anything to a server.
3. Error message/stack trace:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Column 5 is invalid.  Please check your column mappings.
	at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.validateColumnMappings(SQLServerBulkCopy.java:1814)
	at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1617)
	at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:619)
	at example.Hello$.delayedEndpoint$example$Hello$1(Hello.scala:102)
	at example.Hello$delayedInit$body.apply(Hello.scala:66)
	at scala.Function0.apply$mcV$sp(Function0.scala:39)
	at scala.Function0.apply$mcV$sp$(Function0.scala:39)
	at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:17)
	at scala.App.$anonfun$main$1(App.scala:73)
	at scala.App.$anonfun$main$1$adapted(App.scala:73)
	at scala.collection.IterableOnceOps.foreach(IterableOnce.scala:553)
	at scala.collection.IterableOnceOps.foreach$(IterableOnce.scala:551)
	at scala.collection.AbstractIterable.foreach(Iterable.scala:920)
	at scala.App.main(App.scala:73)
	at scala.App.main$(App.scala:71)
	at example.Hello$.main(Hello.scala:66)
	at example.Hello.main(Hello.scala)

4. It's pretty obvious by looking at the code here https://github.com/microsoft/mssql-jdbc/blob/dev/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerBulkCopy.java#L1840 that validateColumnMappings method is expecting getColumnOrdinals to returns said ordinals in order. So it fails if an unsorted set is returned by an implementation.

Reproduction code

Could be found here https://gist.github.com/PavelPenkov/99cb94d4b674afca626640aecbe3c50e (in Scala, though)

[ulvii]

Seems like duplicate of #1265

[PavelPenkov]

Well, I find it strange that the code makes some assumptions about the data not stated in a contract (sets are usually unordered) and more importantly fails in the most basic case - when importing all columns in the same order they are declared in CREATE TABLE.

[peterbae]

Thanks @PavelPenkov for reporting this. I agree that this seems strange, and we will soon work on a fix to solve this issue.

[peterbae]

Hi @PavelPenkov, I have a fix ready in PR #1406, and I've attached a zip containing the Java 11 version of the driver that contains this fix. Could you give this a try and let me know if it fixes your problem?

github-1391.zip

[peterbae]

@PavelPenkov have you had a chance to test out the changes?

[ulvii]

Pr #1406 merged.