Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3370

Derby should not support commit/rollback inside a SQL-invoked external function.


    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s:
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
    • Issue & fix info:
      Release Note Needed


      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.




            • Assignee:
              mamtas Mamta A. Satoor
            • Votes:
              0 Vote for this issue
              1 Start watching this issue


              • Created: