Committing database changes

There are several approaches to defining when ADBC should commit changes. Although it is possible to use more than one of these, it is usually best to stick to just one:

EPL-controlled committing by calling DBUtil.doSQLCommit (or doSQLCommitAck). This is the recommended approach for applications that update the database, as it gives maximum control of transactions to the EPL application. Do not use this when autoCommit is enabled.

Automatic committing per SQL statement by setting autoCommit=true, which configures the underlying database driver to automatically perform a commit after the completion of each SQL statement (by default, this is disabled). This is useful for simple applications that only perform read-only queries.

Timed committing by setting commitStoreInterval, which configures ADBC to batch up SQL statements over the specified time window into a single commit (the default is 0 which means that timed committing is disabled). Do not use this when autoCommit is enabled.

Note: Whether you are using queries to get data from the database or to put data in, it is essential to use at least one of these commit mechanisms, as queries will not complete until a commit is issued.

There is also a property called StoreBatchSize which allows multiple SQL statements to be batched together into a single call to the database, in order to reduce communication overhead. The batching of statements does not itself result in any extra commits, but does still have some impact on which statements are committed together:

If using EPL-controlled committing, the partial batch that has been received so far will be committed.

If autoCommit is enabled, statements are not committed until the batch size is reached.

If using timed committing, a partial batch is committed when the time expires based on what has been uncommitted to that point.