Batch insert on key violation

[Vinicius Carvalho]

Hi folks, I have a use case for h2, where I am inserting several records in batch. 

Some times key collisions may occur, and I was hoping that instead of getting an exception I could only fail the records that had key violations.

Instead if at least one fail, my method fails (I am using jdbi as a wrapper, so not clear if they are the cause)

What is the sgdb default behavior in this scenario?

Thanks

[Evgenij Ryazanov]

Hello!

When you use JDBC directly, H2 throws BatchUpdateException in case of failure, but only after execution of all commands in the batch (other drivers may stop after a first failure, but every driver must always continue processing or always stop processing). getUpdateCounts() and modern getLargeUpdateCounts() methods of this exception return an array with update counts or statuses.

For example, attempt to insert values 1, 1, 2, and 1 into a unique column throws this exception with [1, -3, 1, -3] result. It means that first and third insertions were successful, but second and fourth weren't.

If there were no failures, executeBatch() and modern executeLargeBatch() methods return the same array with update counts.

When you use third-party wrappers on top of JDBC you need to check their documentation, because their behavior may be different from your expectations.

Also you can use a MERGE command for conditional insertions instead of INSERT to avoid any exceptions here.