Database-Side JDBC Procedures Using Non-Nested Connections

A database-side JDBC procedure can create a new connection instead of using a nested connection. Statements executed in the procedure will be part of a different transaction, and so can issue commits and rollbacks.

Such procedures can connect to a database different from the one to which the parent SQL statement that called it is connected. The procedure does not use the same transaction or Connection. It establishes a new Connection and transaction.

Note:

If database-side JDBC procedures do not use nested connections, this means that they are operating outside of the normal DBMS transaction control, so it is not good practice to use them indiscriminately.

If a procedure does not return a value, Derby can execute the procedure using the SQL CALL command. You can invoke the procedure in an SQL statement such as the following:

CALL MYPROC()

Note:

You cannot roll back this statement, because commits occur within the procedure itself. Procedures that use nested connections, on the other hand, are not permitted to commit or roll back and can therefore be rolled back after the calling statement.

You can also use the CALL command to execute a procedure that does return a value, but you will not be able to access the value.