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 "Kathey Marsden (Created) (JIRA)" <ji...@apache.org> on 2012/01/20 18:11:39 UTC
[jira] [Created] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Provide a way to invalidate stored prepared statements
------------------------------------------------------
Key: DERBY-5578
URL: https://issues.apache.org/jira/browse/DERBY-5578
Project: Derby
Issue Type: Improvement
Components: Services
Reporter: Kathey Marsden
In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5578:
-----------------------------------
Attachment: DERBY5578_patch1_diff.txt
Attaching a patch which adds the new stored procedure which will invalidate all the stored statements. Next execution of the statement will cause it to recompile. I will work next on adding regression tests and upgrade tests.
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5578:
-----------------------------------
Issue & fix info: High Value Fix,Newcomer,Release Note Needed (was: High Value Fix,Newcomer)
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.10.0.0
>
> Attachments: derby-5578-create.sql, DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt, derby-5578-upgrade.sql
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-5578.
------------------------------------
Resolution: Fixed
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.10.0.0
>
> Attachments: DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13288466#comment-13288466 ]
Knut Anders Hatlen commented on DERBY-5578:
-------------------------------------------
The upgrade test should probably go into Changes10_10, since that's the current version of trunk. And maybe the new helper methods in TriggerTest should close the ResultSets. Otherwise, the patch looks good to me.
Nits: Some small typos in the comments in TriggerTest that you might want to fix:
- "Creat the required tables" - creat -> create
- "It's trigger action" - It's -> Its
- "trigger action with have" - with -> will
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-5578:
---------------------------------
Attachment: derby-5578-upgrade.sql
derby-5578-create.sql
Attaching derby-5578-create.sql and derby-5578-upgrade.sql. These are scripts which I used to buddy-test this feature. The first script relies on the wrapper DatabaseMetaData functions attached to DERBY-3973. Here's how you run these scripts:
o First run derby-5578-create.sql using an earlier release like 10.8.2.2. This script creates a trigger and registers functions to wrap the DatabaseMetaData methods.
o Then run derby-5578-upgrade.sql using the 10.10 trunk. This script hard-upgrades the database, adding the new SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS procedure.
The scripts verify that SYSCS_INVALIDATE_STORED_STATEMENTS marks all stored prepared statements as invalid in SYS.SYSSTATEMENTS. The scripts verify that invoking the trigger after the invalidation causes it to be marked as valid and updates its lastcompiled timestamp. Those columns in SYS.SYSSTATEMENTS are similarly updated for a DatabaseMetaData method when you invoke it after the bulk invalidation.
This indicates to me that SYSCS_INVALIDATE_STORED_STATEMENTS behaves as described in the Reference Manual.
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.10.0.0
>
> Attachments: derby-5578-create.sql, DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt, derby-5578-upgrade.sql
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13286234#comment-13286234 ]
Mamta A. Satoor commented on DERBY-5578:
----------------------------------------
While writing one of the tests for this procedure, I saw that, as a dba, I was able to grant the execute permission on this procedure to another user.
grant execute on procedure syscs_util.SYSCS_INVALIDATE_STORED_STATEMENTS to sam;
But a similar grant on sysibm.sqlprocedures by dbo to another user fails
grant execute on procedure sysibm.sqlprocedures to sam;
ERROR 42509: Specified grant or revoke operation is not allowed on object 'SYSIBM.SQLPROCEDURES'.
Looking at the code, I found that SchemaDescriptor.isSchemaWithGrantableRoutines() identifies SQLJ and SYSCS_UTIL as schemas which allows grant on it's routines. And that is why we were able to grant execute on syscs_util.SYSCS_INVALIDATE_STORED_STATEMENTS but not on sysibm.sqlprocedures.
So, the question is should the new procedure for invalidating stored statements have grantable execute permission? If not, then should we move it to some other schema like sysibm which does not allow grant execute permission on it's procedures?
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13292088#comment-13292088 ]
Mamta A. Satoor commented on DERBY-5578:
----------------------------------------
Knut, thanks for reviwing the patch. I have made the changes suggested by you and committed the changes to trunk(10.10)
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.10.0.0
>
> Attachments: DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13285967#comment-13285967 ]
Mamta A. Satoor commented on DERBY-5578:
----------------------------------------
Knut, I think that's a good idea to include STORED in the procedure name to indicate that we are only going to invalidate stored prepared statements. I will make the procedure name change.
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13272754#comment-13272754 ]
Dag H. Wanvik commented on DERBY-5578:
--------------------------------------
For field work-arounds I would imagine DBO access would suffice.
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13286292#comment-13286292 ]
Mamta A. Satoor commented on DERBY-5578:
----------------------------------------
As per my understanding, SYSSTATEMENTS will only has two kinds stored prepared statements - for metadata queries and for trigger action plan. Please let me know if there are other possibilities. I am working on writing tests and want to make sure that I provide test cases covering different kinds of stored statements. Thanks
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13285919#comment-13285919 ]
Knut Anders Hatlen commented on DERBY-5578:
-------------------------------------------
Maybe we should call it something like SYSCS_INVALIDATE_STORED_STATEMENTS, just so that it's clear from the name that it doesn't invalidate ordinary statements?
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13271745#comment-13271745 ]
Mamta A. Satoor commented on DERBY-5578:
----------------------------------------
Was wondering, would only a dba have permissions to invalidate the prepared statements?
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5578:
-----------------------------------
Attachment: DERBY5578_patch2_diff.txt
Attaching patch DERBY5578_patch2_diff.txt which is ready for commit. This patch has required code changes to add a new procedure which will allow users to invalidate all the stored statements inside SYS.SYSSTATEMENTS. At this point, there are two types of stored statements in that system table - statements for metadata calls and statements for trigger action plans. I have also added test cases including the regression tests and upgrade tests. Upgrade tests show that this procedure is available only after hard upgrade. Regression test show how the procedure can be executed only by dba unless dba grants execute permission to other users. Additionally, it has test cases showing statements getting invalidated by the procedure call and subsequent execution of metadata call or trigger causing their corresponding statements to become valid.
Please let me know if there is any feedback on the code changes or any other test cases I should add.
The changes for this jira can only go in as part of a major release since it fiddles around wit system tables. This jira can't be backported to released branches.
If Rick sees a need to cut another 10.9 release jars, we can backport it to 10.9 but otherwise it will need to go into next major release.
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13286383#comment-13286383 ]
Knut Anders Hatlen commented on DERBY-5578:
-------------------------------------------
I don't see any reason why we'd want to deny the DBO's granting the execute permission on this procedure, so +1 to keeping it in SYSCS_UTIL.
I think you're right that only meta-data queries and triggers are stored in SYSSTATEMENTS. It looks like there used to be syntax for CREATE STATEMENT, which allowed user-defined queries to be stored there too, but that syntax is disabled now.
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5578:
-----------------------------------
Fix Version/s: 10.10.0.0
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.10.0.0
>
> Attachments: DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13287691#comment-13287691 ]
Dag H. Wanvik commented on DERBY-5578:
--------------------------------------
> I don't see any reason why we'd want to deny the DBO's granting the execute permission on this procedure, so +1 to keeping it in SYSCS_UTIL.
+1
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Assigned] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor reassigned DERBY-5578:
--------------------------------------
Assignee: Mamta A. Satoor
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5578) Provide a way to invalidate stored
prepared statements
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13485004#comment-13485004 ]
Rick Hillegas commented on DERBY-5578:
--------------------------------------
The following script demonstrates what the Reference Manual says:
o By default, only the DBO can run SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS.
o However, the DBO can grant other users the privilege to run this procedure.
connect 'jdbc:derby:memory:db;create=true;user=test_dbo';
-- turn on NATIVE authentication. won't take effect until you bounce the database.
call syscs_util.syscs_create_user( 'test_dbo', 'test_dbopassword' );
call syscs_util.syscs_create_user( 'fred', 'fredpassword' );
-- bounce the database in order to turn on NATIVE authentication
connect 'jdbc:derby:memory:db;shutdown=true';
connect 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' as dbo;
-- the DBO can run this procedure
call syscs_util.syscs_invalidate_stored_statements();
connect 'jdbc:derby:memory:db;user=fred;password=fredpassword' as fred;
-- should fail because privilege has not been granted
call syscs_util.syscs_invalidate_stored_statements();
set connection dbo;
--grant privilege to fred;
grant execute on procedure syscs_util.syscs_invalidate_stored_statements to fred;
set connection fred;
-- should work now that fred has been granted privilege
call syscs_util.syscs_invalidate_stored_statements();
> Provide a way to invalidate stored prepared statements
> ------------------------------------------------------
>
> Key: DERBY-5578
> URL: https://issues.apache.org/jira/browse/DERBY-5578
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.10.0.0
>
> Attachments: derby-5578-create.sql, DERBY5578_patch1_diff.txt, DERBY5578_patch2_diff.txt, derby-5578-upgrade.sql
>
>
> In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira