Database-Side JDBC Procedures and Nested Connections

Most database-side JDBC Procedures need to share the same transaction space as the statements that called them for the following reasons:

• to avoid blocking and deadlocks
• to ensure that any updates done from within the method are atomic with the outer transaction

In order to use the same transaction, the procedure must use the same connection as the parent SQL statement in which the method was executed. Connections re-used in this way are called nested connections.

Use the connection URL jdbc:default:connection to re-use the current Connection

The Database Connection URL jdbc:default:connection allows a Java method to get the Connection of the SQL statement that called it. This is the standard (SQL standard, Part 13 SQL Routines and Java) mechanism to obtain the nested connection object. The method would get a Connection like this:

Connection conn = DriverManager.getConnection(
    "jdbc:default:connection");

Loading a JDBC driver in a database-side routine is not required.

In order to preserve transactional atomicity, database-side JDBC procedures that use nested connections:

• cannot issue a commit or rollback, unless called within a CALL or VALUES statement.

  Commits are permitted when the procedure is called within a CALL or VALUES statement.

• cannot change connection attributes such as auto-commit.
• cannot modify the data in a table used by the parent statement that called the procedure, using INSERT, UPDATE, or DELETE. For example, if a SELECT statement using the T table calls the changeTables procedure, changeTables cannot modify data in the T table.
• cannot drop a table used by the statement that called the procedure.
• cannot be in a class whose static initializer executes DDL statements.

In addition, the Connection object that represents the nested connection always has its auto-commit mode set to false.