bulk copy: please support DATE / DATETIME datatype

[JDBC-0]

Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it

The current driver (V 9.4.0) does not support bulk copy (better: silently does not use bulk copy) for the described reason:

"Due to the limitations of Bulk Copy API, MONEY, SMALLMONEY, DATE, DATETIME, DATETIMEOFFSET, SMALLDATETIME, TIME, GEOMETRY, and GEOGRAPHY data types, are currently not supported for this feature."

For this reason, the enabled "useBulkCopyForBatchInsert=true" feature has no effect because almost all of our tables have Date, Datetime, Datetime2 columns.

Describe the preferred solution

The URL parameter "useBulkCopyForBatchInsert=true" should also use interanlly bulk copy instead of the prepared INSERT batch statements if the table has DATE or DATETIME/DATETIME2 columns.

Describe alternatives you've considered

We have wrote our own bulk copy solution by writing CSV files and setting the DATE/DATEIMTIME values to the correct strings, but this solution is slower that than standard prepared INSERT batches.

Additional context

Reference Documentations/Specifications

https://docs.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver15

Reference Implementation

[lilgreenbird]

hi @JDBC-0
thank you for your input, we are aware of this limitation it is in our backlog, it will be considered and prioritized along with other bug fixes and features when we plan for the new semester.

[JDBC-0]

The documentation on https://docs.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver15 just says what's not allowed:

" Because of the limitations of Bulk Copy API, MONEY, SMALLMONEY, DATE, DATETIME, DATETIMEOFFSET, SMALLDATETIME, TIME, GEOMETRY, and GEOGRAPHY data types, are currently not supported for this feature."

but not what types are supported! Would it be possible to change our table columns from DATE / DATETIME to DATETIME2 for bulk copy to work? Or is DATETIME2 just missing in the list of unsupported datatybes?

[JDBC-0]

So, could you please update the documentation and tell which datatypes are supported by bulk copy - e.g. DATETIME2? Thanks.

[JDBC-0]

By try and error, I found out that DATETIME2 seems to be supported (at least there is some bulk copy exception when DATE and DATETIME columns were replaced by DATETIME2).

[tlopesPT]

@lilgreenbird @Jeffery-Wasty Any idea if this is getting traction soon?

The limitation on DATETIMEOFFSET, forcing us to use DATETIME2, is quite a few steps backward.

[Jeffery-Wasty]

@tlopesPT I'll bring this up with the team during our triage early next week.

[lilgreenbird]

hi @tlopesPT,

This issue as been marked as an enhancement, it will be triaged along with other feature requests and bug fixes when we do planning for the next semester. We encourage you to fill out our Survey as we do take that into consideration, in addition to any upvotes and comments received for the issues.

Also, the JDBC driver is open source we welcome community contribution so anyone can submit a PR for review. We will review and run everything in our test environment and include that in the driver release if everything passes.

Thanks

[tlopesPT]

Hi @lilgreenbird,
Thanks for the update.
Please note the current upvotes on this enhancement. I've also filled out a survey.

Unfortunately, the effort it would take for us to contribute this would greatly impact our timeline in delivering a potential MSSQL integration. So for the time being we're going to have to stick with DATETIME2, despite the potential issues that this introduces to our customers, by omitting the timezone.