You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Satheesh Bandaram (JIRA)" <de...@db.apache.org> on 2005/09/02 01:45:12 UTC
[jira] Created: (DERBY-551) Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
-------------------------------------------------------------------------------------------------------------
Key: DERBY-551
URL: http://issues.apache.org/jira/browse/DERBY-551
Project: Derby
Type: New Feature
Components: SQL
Versions: 10.1.1.0
Environment: All platforms
Reporter: Satheesh Bandaram
Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12421196 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
The changes using the relability mechansim seem fine, though the constant PROCEDURE_CALL_ILLEGAL seems worng. I would read that to mean all procedure calls are illegal, which is not the case. Clear naming of fields, methods etc. really helps people understand the code and reduce bugs due to misunderstanding.
I'm a little confused by the changes to InternalTriggerExecutionContext.validateStatement for the before trigger and DML section.
The patch reverts the code to its old state, what is the purpose of this changes and the resulting check left? Also the comment
that you just made in the previous patch was useful, but you have replaced it with a comment with less value. It seems to me
knowning that INSERT/UPDATE/DELETE statements are blocked by the parser is still useful information to have in this method.
- // No INSERT/UPDATE/DELETE for a before trigger. Parser does not allow
- // these DML statements in a trigger's action statement in a before
- // trigger. Currently, parser does not disallow creation of before
- // triggers calling procedures that modify SQL data. This runtime check
- // is needed to not allow execution of these DML statements by procedures
- // within a before trigger context.
+ /*
+ ** No INSERT/UPDATE/DELETE on trigger table
+ ** for a before trigger.
+ */
else if (triggerd.isBeforeTrigger() &&
constantAction instanceof WriteCursorConstantAction)
{
- throw StandardException.newException(SQLState.LANG_NO_DML_IN_TRIGGER, triggerd.getName(), targetTableName);
+ if (constantAction.modifiesTableId(targetTableId))
+ {
+ throw StandardException.newException(SQLState.LANG_NO_DML_IN_TRIGGER, triggerd.getName(), targetTableName);
+ }
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Attachment: derby-551draft2.diff
derby-551-draft2.status
Thanks Dan for looking at the patch.
I was working on a new patch and I am uploading it as I think it may address Dan's concerns. 'derby-551draft2.diff'' uses the same approach to check the sql allowed in procedures used in before triggers. In this patch, I moved this check to triggerDefinition(). In the first patch, I was doing the check in routineInvocation(). The patch also includes other minor changes and additional tests. I had run derbyall with these changes and did not see any failures. However, I made a few small changes after that and need to run derbyall again.
I checked the case mentioned by Dan (as issue 1) where we can have the value from a function call as a parameter to the procedure. This works as the function calls get into the parameter list and are evaluated later when the parameters are bound. Again it is based on the assumption of how the nodes are currently evaluated. Also, there could be other cases where this patch may not work. If someone can think of any other scenarios not covered in ProcedureInTrigger_Tests_v1.html, please let me know.
I am just uploading this patch as I have it almost ready. I would appreciate if someone can look at it and see if the check for the sql allowed in procedures is okay. If this is not an acceptable solution, I plan to work on a new patch where the check is left to execution time instead of compile time.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Attachment: derby-551-patch2-v1.diff
Thanks Dan for committing patch1.
Based on Dan's suggestion, I am working on adding a check to the parser for disallowing procedures that modify SQL data in before triggers. However, this patch is not quite ready.
Meantime, I am attaching a follow-up patch 'derby-551-patch2-v1.diff' which adds more comments to InternalTriggerExecutionContext.validateStatement. This patch only adds comments and does not change any code. Please take a look at it and commit if okay.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Derby Info: [Patch Available]
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12420633 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
Patch derby-551-patch1-v1.diff committed revision 421281. Thanks Deepa
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12419777 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
I think there are a couple of issues with the approach to checking for procedures that modify data in a before trigger.
1) The field modifiesSqlData is parser wide, not specific to a single routine. This may cause issues when the call statement is like:
CALL MYPROC(MYFUNC(), MYOTHERFUNC(2));
This may work, but may be making false assumptions about the ordering of evaluating nodes.
Also I don't see the field being reset at any time.
2) There is an existing mechanism for checking which routines are allowed, I would have expected this code to use the same functionality.
After looking into it, I see that it is a runtime check, not a compile time check.
The existing runtime check is to support the fact that SQL statements executed in a Java method are not known when the routine is created (in SQL) since they are executed using dynamic JDBC. So if a routine is declared as READS SQL DATA then when it is executed by Derby its level of 'SQL allowed' is set in the statement context . Then any JDBC statements executed by the Java method are checked against the current level. This includes routines, so a procedure that is declared READS SQL DATA will throw an exception at runtime if its Java method attempts to prepare an INSERT SQL statement, or a CALL statement that executes another SQL procedure that is declared MODIFIES SQL DATA.
See StatementContext.setSQLAllowed(short, boolean);
So we could use the existing mechanism, which means a before trigger with a CALL procedure that is declared MODIFIES SQL DATA statement would succeed but would fail at runtime when fired.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12320915 ]
Rick Hillegas commented on DERBY-551:
-------------------------------------
Cloudscape 3.5 allowed the CALL statement (procedure invocation) inside a trigger. So the plumbing to do this is mostly already in the code. Does anyone know why this feature was dropped from Derby and what problems we should watch out for if we re-enable it?
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Fix Version: 10.2.0.0
Assign To: Deepa Remesh
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Attachment: derby-551-patch3-v1.diff
derby-551-patch3-v1.status
Attaching a patch 'derby-551-patch3-v1.diff' which disallows creation of before triggers which contain calls to procedures that modify SQL data. Changes are:
* Adds a new reliability bit mask(PROCEDURE_CALL_ILLEGAL) to CompilerContext.
* Sets the above reliability for before triggers before binding the actionNode in CreateTrigger Node.
* Checks the above reliability in CallStatementNode. After the called procedure is resolved, the sql allowed by the procedure is checked and if it is "modifies sql data" and if we have set the reliability to PROCEDURE_CALL_ILLEGAL, it means we are calling a procedure that modifies sql data in the action statement of a before trigger. In this case, an exception is thrown and trigger creation fails. When I was making this change, I was not sure if this reliability setting will interfere with any other checks. After going through other places it is used, I think we are making specific checks to reliability and this setting will not interfere with other cases. Also, I did not see failures in any of the tests. Can someone please confirm this usage is correct?
* Reverts the modified check added in InternalTriggerExecutionContext.validateStatement to catch the above case at runtime. With the addition of the above compile time check, there is no need for the check at runtime.
* Adds a new message. Reuses the same SQLSatate as for invalid statement in triggers.
* Modifies procedureInTrigger.sql test to handle this change.
Ran derbyall with the changes and did not see any failures. However, I made few minor changes and re-running derbyall now.
Meantime, I would like to get feedback on this patch mainly to see if the usage of CompilerContext is correct.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Derby Info: [Patch Available]
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12421996 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
Patch derby-551-patch3-v2.diff' Committed revision 423266. Thanks Deepa
Not sure we need the new error message, would have thought the existing one could be used.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551-patch3-v2.diff, derby-551-patch3-v2.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Attachment: derby-551-draft3.diff
derby-551-draft3.status
Attaching a new draft patch "derby-551-draft3.diff" which uses Dan's first suggestion (use the existing mechanism, which means a before trigger with a CALL procedure that is declared MODIFIES SQL DATA statement would succeed but would fail at runtime when fired. )
In this patch, I removed the checks that I had added in the parser to check for type of procedure allowed in a before trigger. Instead, the existing runtime check is used. This was slightly modified to check that insert, update, delete on "any" table is not allowed in a before trigger. The existing check was only looking for insert, update,delete on the "trigger table". If this is acceptable as a first step, I plan to submit a patch for this. And then work on a follow-up patch to move the check to compile time using Dan's second suggestion (use the existing compile time mechansim that defines what is allowed in a context). Please take a look at the new patch. Thanks.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Attachment: derby-551-patch3-v2.diff
derby-551-patch3-v2.status
Attaching a patch 'derby-551-patch3-v2.diff' which addresses Dan's comments.
Summary of patch:
* Adds a new reliability bit mask(MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL) to CompilerContext.
* Sets the above reliability for before triggers before binding the actionNode in CreateTriggerNode.
* Checks the above reliability in CallStatementNode. After the called procedure is resolved, the sql allowed by the procedure is checked and if it is "modifies sql data" and if we have set the reliability to MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL, it means we are calling a procedure that modifies sql data in the action statement of a before trigger. In this case, an exception is thrown and trigger creation fails.
* Removes the check in InternalTriggerExecutionContext.validateStatement which was used to catch the use of DML in before triggers. This is a redundant check as use of DML in before triggers is now caught at compile time. Added a comment to indicate this.
* Adds a new message to indicate procedures that modify sql data are not allowed in before triggers. Reuses the same SQLState as for invalid statement in triggers.
* Modifies the test lang/procedureInTrigger.sql to handle this change.
With this patch, ran derbyall with Sun jdk 1.4.2 on Windows XP. No failures related to the patch. Seeing 7 failures also seen in Tinderbox tests (http://www.multinet.no/~solberg/public/Apache/TinderBox_Derby/Limited/testSummary-422045.html).
This patch will resolve this issue. There are two open items for which I will open new JIRAs:
* New issue to handle case where a procedure called in trigger's action statement can cause recursion. Trigger recursion is being discussed as part of DERBY-1261.
* Sub-task to change documentation to indicate support of this feature.
Please take a look at this patch. Thanks.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551-patch3-v2.diff, derby-551-patch3-v2.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Daniel John Debrunner updated DERBY-551:
----------------------------------------
Derby Info: (was: [Patch Available])
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551-patch3-v2.diff, derby-551-patch3-v2.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12420629 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
Just to clarify, it seems that this patch does not disable MODIFIES SQL DATA procedures in a before trigger, only DDL statements in all triggers and DML actions in a before trigger. Is that correct?
You indicate that the checks might move, once the code has settled i think the comments in InternalTriggerExecutionContext.validateStatement could be enhanced with your knowledge. For example adding commeents to the DDL check that DDL statements as the trigger's action statement are disallowed by the parser and the check is for statements executed by procedures executed within a trigger context. Similar comments for before triggers, making it clear the multiple ways DML is disallowed, e.g. currently the DML is disallowed at compile time.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12421204 ]
Deepa Remesh commented on DERBY-551:
------------------------------------
Thanks Dan for looking at the patch. I will rename the variable from PROCEDURE_CALL_ILLEGAL to MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL which I think captures the case we are trying to block.
About the changes to InternalTriggerExecutionContext.validateStatement, the changes I did in patch1+2 were temporary changes meant to catch the execution of DML statements in before triggers at runtime as these would not be caught by the parser. With the new patch, this case will be caught in the parser. So I reverted this code to the state before my patch. On looking at it again, I think this has been a redundant check in the first place. I will try removing this check from validateStatement.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12419808 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
Another option is to use the existing compile time mechansim that defines what is allowed in a context. This is the CompilerContext.getReliability() infrastructure. Bits are set in the mask that define what operations are allowed (or not allowed, can't remember which way the definitions go).
In this case the relability mode would be set to exclude 'modifies data' routines during compilation of the trigger's action statement, this would need some new bit definitions and checks in the node for routine call.
I am concerned that your approach is limited to a single routine call, which is all that is required now, but does not support future changes where multiple procedures can form an action statement. The more general approach using the existing mechanisms will automatically handle future changes.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12421207 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
Sounds good, looks like the check is redundant. Though I believe the comment is not, it's a good example of a "why" comment, "No need to check for I/U/D here *because* ...".
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh resolved DERBY-551.
--------------------------------
Resolution: Fixed
I was waiting to check if there are any suggestions to avoid addition of an extra message. I think it is okay to keep the new message. Hence marking this issue as resolved. If we get any ideas, we can update this later.
I have opened the following issues for the open items:
* DERBY-1602 - issue to handle case where a procedure called in trigger's action statement can cause recursion.
* DERBY-1551 - issue to change documentation to indicate support of this feature.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551-patch3-v2.diff, derby-551-patch3-v2.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12320932 ]
Daniel John Debrunner commented on DERBY-551:
---------------------------------------------
FYI - Cloudscape 3.5 supported a CALL statement but did not support procedures. Not sure what effect this will have on re-enabling the CALL statement in triggers, apart from the combination is unlikely to have been tested.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Attachment: derby-551-patch1-v1.diff
derby-551-patch1-v1.status
Attaching a patch 'derby-551-patch1-v1.diff' for review/commit. This patch allows invoking procedures in triggered sql statement. It is based on 'derby-551-draft3.diff' Changes are:
* Modifies the parser to allow call statement in the trigger action.
* Changes the validateStatement in InternalTriggerExecutionContext to catch following statements not allowed in triggers:
- DDL statements are not allowed in triggers. This was caught as an assert failure as this would be caught at compile time in case of direct use of DDL statements. Since use of DDL statement inside a procedure will not be caught at compile time, patch changes the assert to a SQLException.
- Insert, update, delete statements are not allowed in a before trigger. On the same lines, a procedure that modifies sql data should not be allowed in a before trigger. This is also caught at runtime. This uses the existing check in InternalTriggerExecutionContext.validateStatement. However, the existing check was limited to the trigger table. This check was modified to check for use of insert,update,delete statements on any table.
* Adds a new test lang/procedureInTrigger.sql to derbylang suite. The test is based on the scenarios in ' ProcedureInTrigger_Tests_v1.html'. Only one case (calling procedures that modify SQL data in before triggers) is handled differently by this patch. Trigger creation will pass but firing will fail. This behaviour will change once we move this check to compile time. So I have not modified the test case document. Also, I have not added the test cases for recursive triggers as I am not sure about the expected behaviour. This is being discussed as part of DERBY-1261 on derby-dev.
* Modifies tests and master files which used to check that call statement cannot be part of trigger action.
Once this issue is resolved, we would need updates to documentation. I will open a sub-task for this later.
Ran derbyall with both sane and insane jars using Sun jdk 1.4.2 on Windows XP. No failures. Please take a look at this patch.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551draft2.diff
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Deepa Remesh updated DERBY-551:
-------------------------------
Attachment: derby-551-draft1.diff
derby-551-draft1.status
ProcedureInTrigger_Tests_v1.html
Attaching a draft patch 'derby-551-draft1.diff' (not for commit) which enables procedures to be called from triggers. This patch is not complete. I am posting it as I would like to get some feedback. Changes made by the patch are:
* Modify the parsing to allow call statement inside trigger.
* Check that procedures that modify SQL data are not allowed in BEFORE triggers. To check this, I used AliasDescriptor in DataDictionary to get the alias info. I have placed this check currently in routineInvocation(). I think this is causing a diff in lang/releaseCompileLocks.sql. There is an entry in the lock_table when there should be none:
TABLE|IS |SYSALIASES |Tablelock |GRANT
Earlier, I had tried to use the RoutineAliasInfo in the MethodCallNode but this had not worked as this object was null at trigger creation time. Any suggestions to do this in a better way?
* A new message is added to indicate the above error. It maps to the same SQL state as that for unsupported statements in triggers.
* Added a new test. I have to add some more test cases to this. I am adding tests based on the scenarios identified in ProcedureInTrigger_Tests_v1.html. Please go through this and let me know if there are scenarios that need to be added/removed.
* Modified two tests by removing statements that were testing that call statements are not allowed in triggers.
I would appreciate feedback on the patch and the test scenarios.
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assignee: Deepa Remesh
> Fix For: 10.2.0.0
> Attachments: ProcedureInTrigger_Tests_v1.html, derby-551-draft1.diff, derby-551-draft1.status
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Deepa Remesh (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=comments#action_12421998 ]
Deepa Remesh commented on DERBY-551:
------------------------------------
Thanks Dan for committing patch3.
I had tried to use the existing message:
42Z9D=''{0}'' statements are not allowed in ''{1}'' triggers.
Somehow I was not able to fit the new scenario in the same message. So I created a new message which uses the same SQLState:
42Z9D.S.1=Procedures that modify SQL data are not allowed in BEFORE triggers.
Does anyone see a way to avoid the new message?
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551-patch3-v1.diff, derby-551-patch3-v1.status, derby-551-patch3-v2.diff, derby-551-patch3-v2.status, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-551) Allow invoking java stored procedures
from inside a trigger. Make CALL a valid statement in the trigger body.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-551?page=all ]
Daniel John Debrunner updated DERBY-551:
----------------------------------------
Derby Info: (was: [Patch Available])
Applied patch derby-551-patch2-v1.diff - Thanks Deepa
> Allow invoking java stored procedures from inside a trigger. Make CALL a valid statement in the trigger body.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-551
> URL: http://issues.apache.org/jira/browse/DERBY-551
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.1.1.0
> Environment: All platforms
> Reporter: Satheesh Bandaram
> Assigned To: Deepa Remesh
> Fix For: 10.2.0.0
>
> Attachments: derby-551-draft1.diff, derby-551-draft1.status, derby-551-draft2.status, derby-551-draft3.diff, derby-551-draft3.status, derby-551-patch1-v1.diff, derby-551-patch1-v1.status, derby-551-patch2-v1.diff, derby-551draft2.diff, ProcedureInTrigger_Tests_v1.html
>
>
> Derby currently doesn't allow CALL statement to be used in a trigger body. It would be great to allow java stored procedure invocation inside a trigger. Since Derby doesn't have SQL procedure language, triggers can only execute a single SQL statement. If we allow stored procedures in triggers, it would be possible to write a trigger that involves more than just one SQL statement. Functions are currently allowed, but they are read-only.
> I believe it is fairly easy to support this enhancement. Need good amount of testing though.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira