Open typhoon2k opened 2 months ago
@Jeffery-Wasty, is it a good idea in general (parse SQLServerBulkCopyOptions
from connection string)? Or is there some other way how to get non-default SQLServerBulkCopyOptions
when using useBulkCopyForBatchInsert
?
I'm a bit confused about this request. Why are you not able to set SQLServerBulkCopyOptions
through code, such as in this example?
We are using ETL tools (for example, Apache NiFi) that are database agnostic and are integrated with databases using standard JDBC API only:
Statement statement = connection.createStatement();
for (String sqlStatement : sqlStatements) {
statement.addBatch(sqlStatement);
}
statement.executeBatch();
So, there's no way for us to set or pass custom SQLServerBulkCopyOptions.
Now, when executeBatch
method is executed in MSSQL JDBC driver with useBulkCopyForBatchInsert=true
, then only queryTimeout is propagated to SQLServerBulkCopyOptions
(see here):
bcOperation = new SQLServerBulkCopy(connection);
SQLServerBulkCopyOptions option = new SQLServerBulkCopyOptions();
option.setBulkCopyTimeout(queryTimeout);
bcOperation.setBulkCopyOptions(option);
That means that this approach (enabling Bulk Copy via useBulkCopyForBatchInsert
in connection string) is limited to scenarios that match default values of SQLServerBulkCopyOptions
(for example, destination table can't have constraints).
I see 2 options here:
SQLServerPreparedStatement
- allow to pass other SQLServerBulkCopyOptions
properties via connection string (or somehow else).SQLServerBulkCopyOptions
in general - in constructor set initial values from (new) connection/connection string properties (with fallback to current defaults, if properties were not passed via connection/connection string).I see, thank you for the explanation. We'll add this as a feature request and discuss it during our next semester planning.
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
Using
useBulkCopyForBatchInsert
is super convinient, when we need to enable bulk insert while using 3rd party tools integrated via standard JDBC API (prepared statements). Unfortunately we faced a situation, where target tables have some constraints. The only option in this case is to use non-standardSQLServerBulkCopy
+SQLServerBulkCopyOptions
APIs that is not available for us.Describe the preferred solution
It would be great if
SQLServerPreparedStatement
could parseSQLServerBulkCopyOptions
properties from connection string.Describe alternatives you've considered
Request 3rd party tool developers to create separate implementation for MS SQL server databases that would be integrated with low-level bulk copy API.
Additional context
N/A
Reference Documentations/Specifications
N/A
Reference Implementation
N/A