[jira] Commented: (DERBY-3370) Derby should not support commit/rollback inside a SQL-invoked external function.

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

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

Mamta A. Satoor commented on DERBY-3370:
----------------------------------------

>From my research on DERBY-3037, I believe the problem is only when holdability is false for the Statement that is making the function call with commit/rollback inside the function body. 

Today, in Derby, when the commit is issued, we call(in BaseActivation.reset()) close on the Language Resultsets with holdability false. For Language Resultsets associated with functions (those Resultsets are of the type RowResultSet which extend NoPutResultSetImpl), the close method simply marks itself as isOpen=false;(NoPutResultSetImpl.close:line 184). It does not call a close on the Activation associated with it. The closing of the Activation happens when the JDBC Resultset is closed (EmbedResultSet.close:line 575). The close of JDBC Resultset calls close on single use Activations which call finish on the Language Resultset.

With the patch DERBY3037_patch_not_ready_for_commit_v2_diff.txt attached to DERBY_3037, we want the single use Acitvations to close when it's Language Resultset is getting closed. So, when the commit issued, we call (in BaseActivation.reset()) close on the Language Resultsets with holdability false. For Language Resultsets associated with functions (those Resultsets are of the type RowResultSet which extend NoPutResultSetImpl), the close method (with patch DERBY3037_patch_not_ready_for_commit_v2_diff.txt) now not only marks itself as isOpen=false  but it also closes it's Activation if it is a single use Activation. The close of Activation at this point ends up calling finish on Language Resultset. At this point, if the user tries to move around in the JDBC Resultset associated with function call, it will result in problems because the activation associated with it has already been closed.

So, to summarize, I think if we commit the cleanup changes attached to DERBY_3037 as patch DERBY3037_patch_not_ready_for_commit_v2_diff.txt, we will run into problems with function calls with commit/rollback inside them if the function call is associated with a Resultset with holdability false.

> 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 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.

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