[
https://issues.apache.org/jira/browse/DERBY-3037?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564417#action_12564417
]
Mamta A. Satoor commented on DERBY-3037:
----------------------------------------
I did further investigation into SQL specs and following is what seems to apply
for what Derby supports which is SQL-invoked routines which are external
routines written in Java.
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 and SQL-invoked
procedures.
Other databases including Oracle, DB2, Sybase support commit and rollback
inside SQL-invoked procedures so eventhough it is not a SQL standard, it
appears to be a de-facto industry standard. It allows the users to finish one
unit of task completely inside a stand along SQL-invoked procedures and since
procedures do not directly return resultsets, supporting commit and rollback
inside them do not cause a problem.
But that is not true for 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. I will go ahead and enter a Jira entry for that. I think we will need
to reach some kind of resolution for that jira enty before the patch attached
to this jira entry (DERBY3037_patch_not_ready_for_commit_v2_diff.txt) can be
committed. This patch exposes the vulnerability of Derby explained in this
paragraph through lang/nestedCommit.sql.
Please let me know if anyone has any questions/comments regarding this.
I will work on opening a jira entry for commit/rollback inside SQL-invoked
functions.
> Language ResultSet.finish() is called even when the ResultSet is going to be
> re-used.
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3037
> URL: https://issues.apache.org/jira/browse/DERBY-3037
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Daniel John Debrunner
> Assignee: Mamta A. Satoor
> Fix For: 10.3.2.2, 10.4.0.0
>
> Attachments: DERBY3037_patch_not_ready_for_commit_v2_diff.txt,
> DERBY3037_patch_not_ready_for_commit_v2_stat.txt,
> Derby_3037_AlterTableConstantActionChanges_v1_diff.txt,
> Derby_3037_AlterTableConstantActionChanges_v1_stat.txt, DERBY_3304_Repro.java
>
>
> DERBY-827 (correctly) changed the lifetime of the language ResultSet tree to
> be the lifetime of the activation, but did not fix up the correct calls to
> ResultSet.close() and ResultSet.finish().
> A language ResultSet's lifetime should be driven by the activation, so
> activation.close() should call finish() on its ResultSet.
> EmbedResultSet should call close on its language ResultSet (theResults field)
> when the JDBC ResultSet is closed, it should not be calling finish() on its
> ResultSet.
> See comments in DERBY-827 for some more details and issues.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
