[FIX] PreparedStatement.executeBatch() When the Insert Sql Column Name Case Mismatch, Throws BatchUpdateException "Unable to retrieve column metadata." by wooln · Pull Request #2589 · microsoft/mssql-jdbc

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).

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).

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-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


                        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.

- Return false (case-insensitive) if sortOrder is not found

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

Jeffery-Wasty requested review from Ananya2, divang, machavan and muskan124947 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

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

lilgreenbird reviewed Jan 23, 2025

View reviewed changes

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

lilgreenbird reviewed Jan 23, 2025

View reviewed changes

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

lilgreenbird reviewed Jan 23, 2025

View reviewed changes

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

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

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

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

mmimica reviewed Jan 29, 2025

View reviewed changes

Comment thread src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java Outdated

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…

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: handle null SortOrder in getIsCaseSensitive to keep it defensive

4666e11

- Return false (case-insensitive) if sortOrder is not found

wooln closed this Jun 26, 2025

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 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!

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!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

wooln commented Jan 21, 2025 •

edited

Loading