Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
10.2.2.0
-
None
-
Normal
-
Release Note Needed
Description
SQL foundation spec section 10.4<routine invocation> GR 8)f)ii)6)B) says
"If, before the completion of the execution of P, an attempt is made to execute an SQLtransaction statement that is not <savepoint statement> or <release savepoint statement>, or is a <rollback statement> that does not specify a <savepoint clause>, then an exception condition is raised: external routine exception — prohibited SQL-statement attempted."
The P above is the program identified by the external name of R, where R is in an external routine.
The Part 13 of the SQL spec (which is specific to behavior of SQL-invoked routines which are external and written in Java) does not include any modification to the general rule above. (The place to check in Part 13 would be Section 8.3 <routine invocation> Page 34 and couple pages after that.)
Based on these 2 specifications, Derby is not following SQL specification by allowing commit and rollbacks inside SQL-invoked functions.
A SQL-invoked function for instance can be called from a SELECT statement and SELECT statement has resultset associated with it. If the SQL-invoked function does a commit inside it, what should happen to the resultset associated with SELECT statement if the resultset set is created with holdability false? Because of this, I do not think Derby should support commit and rollback inside of a SQL-invoked function.
This behavior was discovered while researching on DERBY-3037. More information can be found in comments for DERBY-3037 starting with Dan's comment on Jan 22nd 2008.