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 "Daniel John Debrunner (JIRA)" <de...@db.apache.org> on 2006/09/09 01:06:23 UTC
[jira] Commented: (DERBY-1585) derbylang/procedureInTrigger: not
able to create trigger due to an open ResultSet
[ http://issues.apache.org/jira/browse/DERBY-1585?page=comments#action_12433524 ]
Daniel John Debrunner commented on DERBY-1585:
----------------------------------------------
Does this mean that there is a bug?
Either:
When a procedure is called from a trigger any ResultSet's returned are not explictly closed by the trigger calling mechanism?
or
procedures that return result sers should not be allowed in triggers.
Might need to look at the standard to see what is expected here. For the first case the question would be should the
returned dynamic result sets simply be closed, or should each row be fetched and then closed. The fetching of rows
could have functions that produce side effects the application is expecting to occur.
> derbylang/procedureInTrigger: not able to create trigger due to an open ResultSet
> ---------------------------------------------------------------------------------
>
> Key: DERBY-1585
> URL: http://issues.apache.org/jira/browse/DERBY-1585
> Project: Derby
> Issue Type: Bug
> Components: Regression Test Failure, Test
> Affects Versions: 10.2.1.0
> Environment: ------------------ Java Information ------------------
> Java Version: 1.6.0-rc
> Java Vendor: Sun Microsystems Inc.
> Java home: /usr/local/java/jdk1.6.0_b91/jre
> Java classpath: /export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/junit.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/dbprocedures.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jmxremote.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/javadbtests.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/Dots.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/mail.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/activation.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/Perfmon.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jakarta-oro-2.0.8.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/commons-logging.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jagops.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jdmkrt.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/db2jcc_license_c.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jet.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jetbatch.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/db2jcc.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jag.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jagclient.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jagutils.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/GenCfg.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jmxremote_optional.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/jmx.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/hadbjdbc4.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyTesting.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derby.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_zh_TW.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbytools.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_ko_KR.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_zh_CN.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_es.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_de_DE.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_ja_JP.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbynet.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_pt_BR.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyclient.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_fr.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyrun.jar:/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyLocale_it.jar:
> OS name: Linux
> OS architecture: i386
> OS version: 2.6.9-34.ELsmp
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derby.jar] 10.2.0.4 alpha - (423199)
> [/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbytools.jar] 10.2.0.4 alpha - (423199)
> [/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbynet.jar] 10.2.0.4 alpha - (423199)
> [/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyclient.jar] 10.2.0.4 alpha - (423199)
> [/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/db2jcc_license_c.jar] 2.4 - (17)
> [/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/db2jcc.jar] 2.4 - (17)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Current Locale : [English/United States [en_US]]
> Found support for locale: [de_DE]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [es]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [fr]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [it]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [ja_JP]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [ko_KR]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [pt_BR]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [zh_CN]
> version: 10.2.0.4 alpha - (423199)
> Found support for locale: [zh_TW]
> version: 10.2.0.4 alpha - (423199)
> ------------------------------------------------------
> Reporter: Henri van de Scheur
> Assigned To: Deepa Remesh
> Fix For: 10.2.1.0
>
> Attachments: derby1585_v1.diff, derby1585_v1.status
>
>
> ********* Diff file derbyall/derbylang/procedureInTrigger.diff
> *** Start: procedureInTrigger jdk1.6.0-rc derbyall:derbylang 2006-07-19 13:52:20 ***
> 714a715,730
> > ERROR X0X95: Operation 'CREATE TRIGGER' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.
> > ij> --- delete a row. check that trigger is fired - procedure should be called once
> > delete from t1 where i=10;
> > 1 row inserted/updated/deleted
> > ij> --- check delete is successful
> > select * from t1;
> > I |B
> > ---------------------------
> > 5 |two
> > 6 |four
> > 8 |eight
> > ij> drop trigger select_from_trig_table;
> > ERROR 42X94: TRIGGER 'SELECT_FROM_TRIG_TABLE' does not exist.
> > ij> --- use procedures which alter/drop trigger table and some other table
> > create trigger alter_table_trig AFTER delete on t1
> > for each STATEMENT mode db2sql call alter_table_proc();
> 716,732d731
> < ij> --- delete a row. check that trigger is fired - procedure should be called once
> < delete from t1 where i=10;
> < selectRows - 1 arg - 1 rs
> < 1 row inserted/updated/deleted
> < ij> --- check delete is successful
> < select * from t1;
> < I |B
> < ---------------------------
> < 5 |two
> < 6 |four
> < 8 |eight
> < ij> drop trigger select_from_trig_table;
> < 0 rows inserted/updated/deleted
> < ij> --- use procedures which alter/drop trigger table and some other table
> < create trigger alter_table_trig AFTER delete on t1
> < for each STATEMENT mode db2sql call alter_table_proc();
> < 0 rows inserted/updated/deleted
> Test Failed.
> *** End: procedureInTrigger jdk1.6.0-rc derbyall:derbylang 2006-07-19 13:52:34 ***
--
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
Re: [jira] Commented: (DERBY-1585) derbylang/procedureInTrigger: not able to create trigger due to an open ResultSet
Posted by Deepa Remesh <dr...@gmail.com>.
On 9/8/06, Daniel John Debrunner (JIRA) <de...@db.apache.org> wrote:
> [ http://issues.apache.org/jira/browse/DERBY-1585?page=comments#action_12433524 ]
>
> Daniel John Debrunner commented on DERBY-1585:
> ----------------------------------------------
>
> Does this mean that there is a bug?
Thanks Dan for looking at this one. I think this could be a bug.
> Either:
>
> When a procedure is called from a trigger any ResultSet's returned are not explictly closed by the trigger calling mechanism?
>
We do not explicitly close the result set returned by procedure called
from a trigger. The user application cannot access the result set too.
So the result set get closed only when it is garbage-collected. This
may lead to unpredictable behaviour which was the cause of the
intermittent failures. I hadn't thought this was a bug. I thought
there is no point for an user application to create a trigger that
calls a procedure that returns result sets because the application
cannot access the returned result sets. But we do not disallow this. I
myself did this (unintentionally) when I first wrote the test and it
is possible for users to do it which can lead to unpredictable
behaviour.
> or
>
> procedures that return result sers should not be allowed in triggers.
>
> Might need to look at the standard to see what is expected here.
I have not seen anything in the spec which disallows this.
>For the first case the question would be should the
> returned dynamic result sets simply be closed, or should each row be fetched and then closed. The fetching of rows
> could have functions that produce side effects the application is expecting to occur.
I am thinking what the correct behaviour should be. Any
thoughts/pointers would be helpful.
Thanks,
Deepa