db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3370) Derby should not support commit/rollback inside a SQL-invoked external function.
Date Thu, 31 Jan 2008 17:47:11 GMT
Derby should not support commit/rollback inside a SQL-invoked external function.

                 Key: DERBY-3370
                 URL: https://issues.apache.org/jira/browse/DERBY-3370
             Project: Derby
          Issue Type: Bug
          Components: SQL
            Reporter: Mamta A. Satoor

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
The P above is the program identified by the external name of R, where R is in an external

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.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message