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-3037) Language ResultSet.finish() is called even when the ResultSet is going to be re-used.
Date Thu, 31 Jan 2008 17:33:07 GMT

    [ 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
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 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:
>            Reporter: Daniel John Debrunner
>            Assignee: Mamta A. Satoor
>             Fix For:,
>         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-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.

View raw message