[FEATURE REQUEST] Set Bulk Copy options via connection string

[typhoon2k]

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-standard SQLServerBulkCopy+ SQLServerBulkCopyOptions APIs that is not available for us.

Describe the preferred solution

It would be great if SQLServerPreparedStatement could parse SQLServerBulkCopyOptions 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

[typhoon2k]

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

[Jeffery-Wasty]

I'm a bit confused about this request. Why are you not able to set SQLServerBulkCopyOptions through code, such as in this example?

[typhoon2k]

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:

1. Enhance just SQLServerPreparedStatement - allow to pass other SQLServerBulkCopyOptions properties via connection string (or somehow else).
2. Enhance 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).

[Jeffery-Wasty]

I see, thank you for the explanation. We'll add this as a feature request and discuss it during our next semester planning.