1. Derby
  2. DERBY-3370

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


    • Type: Bug Bug
    • Status: Open
    • Priority: Major 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.


        Mamta A. Satoor created issue -
        Dag H. Wanvik made changes -
        Field Original Value New Value
        Issue & fix info [Existing Application Impact] [Release Note Needed]
        Tiago R. Espinha made changes -
        Urgency Normal
        Kathey Marsden made changes -
        Labels derby_triage10_5_2
        Kathey Marsden made changes -
        Affects Version/s [ 12312027 ]
        Gavin made changes -
        Workflow jira [ 12422453 ] Default workflow, editable Closed status [ 12796889 ]


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


            • Created: