[NIFI-12010] Handle auto-commit and commit based on driver capabilities in SQL components

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.0.0-M1, 1.24.0
• Component/s: Extensions
• Assignee: Matt Burgess
• Reporter: Matt Burgess
• Created: 29/Aug/23 16:48
• Updated: 12/Sep/23 13:45
• Resolved: 12/Sep/23 13:45

Description

Some JDBC drivers throw an exception when calling setAutoCommit(false), even if the auto-commit setting for the driver is already false. Although unfortunate, I'm not sure it is a violation of the JDBC API. The SQL components in NiFi that use the JDBC API should catch SQLFeatureNotSupportedException and perhaps log a debug message but otherwise proceed.

Same goes for connection.commit() and connection.rollback(), these should not be explicitly called if auto-commit is true for the connection.

Issue Links

• is a clone of: NIFI-11898 Handle auto-commit and commit based on driver capabilities in PutDatabaseRecord
• is related to: NIFI-8605 ExecuteSQLRecord processor consumes a large heap volume when use with PostgreSQL JDBC driver
• links to: GitHub Pull Request #7663
• links to: https://github.com/apache/nifi/pull/7663