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] Commented: (DERBY-3370) Derby should not support commit/rollback inside a SQL-invoked external function.
Date Fri, 01 Feb 2008 08:31:09 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564682#action_12564682

Mamta A. Satoor commented on DERBY-3370:

My analysis in the earlier comment is for a function call inside a SELECT statement. I haven't
looked at what the impact might be on DML statements using a function that has commit/rollback
inside the function.

> 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