[FIX] PreparedStatement.executeBatch() When the Insert Sql Column Name Case Mismatch, Throws BatchUpdateException "Unable to retrieve column metadata." #2589

wooln · 2025-01-21T03:15:28Z

fix #2588 [BUG] PreparedStatement.executeBatch() When the Insert Sql Column Name Case Mismatch, Throws BatchUpdateException "Unable to retrieve column metadata."

wooln · 2025-01-21T03:18:25Z

@microsoft-github-policy-service agree

lilgreenbird · 2025-01-23T00:40:04Z

/azp run

azure-pipelines · 2025-01-23T00:40:18Z

Azure Pipelines successfully started running 3 pipeline(s).

src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java

lilgreenbird

fix imports and test cleanup

lilgreenbird · 2025-01-23T19:28:34Z

/azp run

azure-pipelines · 2025-01-23T19:28:50Z

Azure Pipelines successfully started running 3 pipeline(s).

David-Engel

Something to consider:

Does your database use a case-sensitive (like SQL_Latin1_General_CP1_CS_AS) or case-insensitive collation (like SQL_Latin1_General_CP1_CI_AS)?

What happens if you are using a case-sensitive collation and a table like this?

create table [Test1] ([C1] int, [c1] varchar)

lilgreenbird · 2025-01-24T01:25:30Z

/azp run

azure-pipelines · 2025-01-24T01:25:44Z

Azure Pipelines successfully started running 3 pipeline(s).

src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java

src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java

wooln · 2025-01-31T11:27:51Z

Something to consider:

Does your database use a case-sensitive (like SQL_Latin1_General_CP1_CS_AS) or case-insensitive collation (like SQL_Latin1_General_CP1_CI_AS)?

What happens if you are using a case-sensitive collation and a table like this?

create table [Test1] ([C1] int, [c1] varchar)

Good idea. I covered the unit tests that use both columns c1 and C1, and an exception occurred, indicating that no one has ever covered CS before. I'll try to fix it by the way.

com.microsoft.sqlserver.jdbc.SQLServerException: Duplicate column names are not allowed.

	at com.microsoft.sqlserver.jdbc.SQLServerBulkRecord.checkDuplicateColumnName(SQLServerBulkRecord.java:157)
	at com.microsoft.sqlserver.jdbc.SQLServerBulkBatchInsertRecord.addColumnMetadataInternal(SQLServerBulkBatchInsertRecord.java:323)
	at com.microsoft.sqlserver.jdbc.SQLServerBulkRecord.addColumnMetadata(SQLServerBulkRecord.java:83)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2232)
	at com.microsoft.sqlserver.jdbc.unit.statement.BatchExecutionTest.testExecuteBatchColumnCaseMismatch_CS(BatchExecutionTest.java:632)

wooln · 2025-03-14T02:19:52Z

@lilgreenbird trigger /azp run, pls.

azure-pipelines · 2025-03-14T02:20:14Z

Commenter does not have sufficient privileges for PR 2589 in repo microsoft/mssql-jdbc

wooln · 2025-03-21T03:19:06Z

@Jeffery-Wasty , who can continue?

@lilgreenbird trigger /azp run, pls.

Jeffery-Wasty · 2025-03-21T16:04:07Z

/azp run

azure-pipelines · 2025-03-21T16:04:22Z

Azure Pipelines successfully started running 3 pipeline(s).

wooln · 2025-03-24T06:08:34Z

@Jeffery-Wasty ,pipelines error. is it necessary to rebase main? the pipeline does in the code repository.

@azure-pipelines azure-pipelines Build log #L14
An unexpected error occurred while trying to download the package. Exit code(1) and error({"@t":"2025-03-21T16:08:27.5659272Z","@m":"An error occurred on the service. VS800075: The project with id 'vstfs:///Classification/TeamProject/436dae6e-dac6-40f4-8c2d-d1fb7a8f09f7' does not exist, or you do not have permission to access it.","@i":"daf054c7","@l":"Error","SourceContext":"ArtifactTool.Program","UtcTimestamp":"2025-03-21 16:08:27.565Z"})

Jeffery-Wasty · 2025-03-24T15:58:31Z

@wooln This needs to be resolved on our end.

machavan · 2025-03-24T16:36:54Z

/azp run

azure-pipelines · 2025-03-24T16:37:09Z

Azure Pipelines successfully started running 3 pipeline(s).

wooln · 2025-03-27T07:24:11Z

@wooln This needs to be resolved on our end.

@Jeffery-Wasty ,Oops, CI is still in error.

machavan · 2025-03-27T08:37:50Z

/azp run

azure-pipelines · 2025-03-27T08:38:07Z

Azure Pipelines successfully started running 3 pipeline(s).

wooln · 2025-03-27T14:38:35Z

@Jeffery-Wasty @machavan , CI is stilllll in error.

- Add test case for matching column case in batch execution - Add test case for mismatched column case in batch execution, expected SQLServerException

- Update column index search to be case-insensitive in SQLServerPreparedStatement - Remove failing test case in BatchExecutionTest that was checking for specific exception

…inally block

- Add getIsCaseSensitive() method to SQLCollation class - Update SQLServerPreparedStatement to use case sensitivity information - Improve performance by avoiding unnecessary case-insensitive comparisons

- Add support for case-sensitive column names in SQLServerBulkBatchInsertRecord - Update SQLServerBulkRecord to handle case sensitivity in column metadata - Modify SQLServerPreparedStatement to pass case sensitivity information to batch records - Add tests for case-sensitive and case-insensitive column name handling in batch execution

…port in BatchExecutionTest

…abase in unit test.

wooln · 2025-04-24T03:27:35Z

@machavan, Needs review.

I've rebased main. What else do I need to do?

machavan · 2025-04-24T04:32:15Z

Hi @wooln, We will review and get back on this PR.

Thanks

machavan · 2025-04-29T15:18:01Z

src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java

@@ -2193,7 +2193,7 @@ public int[] executeBatch() throws SQLServerException, BatchUpdateException, SQL
                        }

                        SQLServerBulkBatchInsertRecord batchRecord = new SQLServerBulkBatchInsertRecord(
-                                batchParamValues, bcOperationColumnList, bcOperationValueList, null);
+                                batchParamValues, bcOperationColumnList, bcOperationValueList, null, connection.getDatabaseCollation().getIsCaseSensitive());


is databaseCollation always non-null and populated in Connection object ?( without involving an explicit round-trip)

At least in this place, the connection has opened and the collation has obtained.

To be prudent, the database in the connection string and the table operated on in the Statement may not necessarily be the same database (because the table name in SQL can have a database name prefix). I don't know how to deal with it.

machavan · 2025-04-29T15:18:25Z

src/main/java/com/microsoft/sqlserver/jdbc/SQLCollation.java

+     * @return
+     */
+    boolean getIsCaseSensitive() {
+        return sortOrderIndex.get(this.sortId).name.contains("_CS_");


is this a reliable way to check case sensitivity?

I couldn't find any other methods from Microsoft's documentation.

machavan requested review from Jeffery-Wasty, tkyc and lilgreenbird January 21, 2025 05:42

Jeffery-Wasty requested review from divang, Ananya2, muskan124947 and machavan January 21, 2025 18:45

wooln mentioned this pull request Jan 22, 2025

[QUESTION] SQLServerBulk Copy: Unable to retrieve column metadata. #2483

Closed

lilgreenbird reviewed Jan 23, 2025

View reviewed changes

src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java Show resolved Hide resolved

lilgreenbird reviewed Jan 23, 2025

View reviewed changes

src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java Show resolved Hide resolved

lilgreenbird reviewed Jan 23, 2025

View reviewed changes

src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java Outdated Show resolved Hide resolved

lilgreenbird reviewed Jan 23, 2025

View reviewed changes

src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java Outdated Show resolved Hide resolved

lilgreenbird previously requested changes Jan 23, 2025

View reviewed changes

wooln force-pushed the bugfix-execute-batch-case-sensitive branch from 719687f to 902ac88 Compare January 23, 2025 04:56

David-Engel reviewed Jan 23, 2025

View reviewed changes

lilgreenbird reviewed Jan 24, 2025

View reviewed changes

src/test/java/com/microsoft/sqlserver/jdbc/unit/statement/BatchExecutionTest.java Show resolved Hide resolved

Jeffery-Wasty requested a review from lilgreenbird January 25, 2025 22:15

mmimica reviewed Jan 29, 2025

View reviewed changes

src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java Outdated Show resolved Hide resolved

Jeffery-Wasty added this to the 12.11.0 milestone Feb 20, 2025

wooln requested a review from mmimica February 27, 2025 01:50

Jeffery-Wasty assigned machavan Mar 21, 2025

wooln added 10 commits April 24, 2025 11:21

test: add tests for column case sensitivity in batch execution

3c3d235

- Add test case for matching column case in batch execution - Add test case for mismatched column case in batch execution, expected SQLServerException

fix(batch): improve column metadata handling for batch execution

c9db5db

- Update column index search to be case-insensitive in SQLServerPreparedStatement - Remove failing test case in BatchExecutionTest that was checking for specific exception

test(BatchExecutionTest): Add cleanup for caseSensitiveTable in the f…

05ddc70

…inally block

test: remove unused test case for column case matching

800d57c

feat(collation): add case sensitivity check for database collation

e786c7c

- Add getIsCaseSensitive() method to SQLCollation class - Update SQLServerPreparedStatement to use case sensitivity information - Improve performance by avoiding unnecessary case-insensitive comparisons

refactor: Replace wildcard import with specific SQLServerException im…

995e7af

…port in BatchExecutionTest

revert: throw BatchUpdateException

4b012be

perf: remove redundant column index calculation

74b4653

test(BatchExecutionTest): Add case sensitive and case insensitive dat…

Loading
Loading status checks…

0a77577

…abase in unit test.

wooln force-pushed the bugfix-execute-batch-case-sensitive branch from 6408cc4 to 0a77577 Compare April 24, 2025 03:22

machavan reviewed Apr 29, 2025

View reviewed changes

[FIX] PreparedStatement.executeBatch() When the Insert Sql Column Name Case Mismatch, Throws BatchUpdateException "Unable to retrieve column metadata." #2589

Are you sure you want to change the base?

[FIX] PreparedStatement.executeBatch() When the Insert Sql Column Name Case Mismatch, Throws BatchUpdateException "Unable to retrieve column metadata." #2589

Conversation

wooln commented Jan 21, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

wooln commented Jan 21, 2025

Uh oh!

lilgreenbird commented Jan 23, 2025

Uh oh!

azure-pipelines bot commented Jan 23, 2025

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

lilgreenbird left a comment

Choose a reason for hiding this comment

Uh oh!

lilgreenbird commented Jan 23, 2025

Uh oh!

azure-pipelines bot commented Jan 23, 2025

Uh oh!

David-Engel left a comment

Choose a reason for hiding this comment

Uh oh!

lilgreenbird commented Jan 24, 2025

Uh oh!

azure-pipelines bot commented Jan 24, 2025

Uh oh!

Uh oh!

Uh oh!

wooln commented Jan 31, 2025

Uh oh!

wooln commented Mar 14, 2025

Uh oh!

azure-pipelines bot commented Mar 14, 2025

Uh oh!

wooln commented Mar 21, 2025

Uh oh!

Jeffery-Wasty commented Mar 21, 2025

Uh oh!

azure-pipelines bot commented Mar 21, 2025

Uh oh!

wooln commented Mar 24, 2025

Uh oh!

Jeffery-Wasty commented Mar 24, 2025

Uh oh!

machavan commented Mar 24, 2025

Uh oh!

azure-pipelines bot commented Mar 24, 2025

Uh oh!

wooln commented Mar 27, 2025

Uh oh!

machavan commented Mar 27, 2025

Uh oh!

azure-pipelines bot commented Mar 27, 2025

Uh oh!

wooln commented Mar 27, 2025

Uh oh!

Uh oh!

wooln commented Apr 24, 2025

Uh oh!

machavan commented Apr 24, 2025

Uh oh!

machavan Apr 29, 2025

Choose a reason for hiding this comment

Uh oh!

wooln Apr 30, 2025

Choose a reason for hiding this comment

Uh oh!

machavan Apr 29, 2025

Choose a reason for hiding this comment

Uh oh!

wooln Apr 30, 2025

Choose a reason for hiding this comment

Uh oh!

wooln commented Jan 21, 2025 •

edited

Loading