Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <8884357.1152896057381.JavaMail.jira@brutus>
Date: Fri, 14 Jul 2006 09:54:17 -0700 (PDT)
From: "Deepa Remesh (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-551) Allow invoking java stored procedures
 from inside a trigger. Make CALL a valid statement in the trigger body.
In-Reply-To: <1149853848.1125618312287.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

     [ 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