db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kevin Luo" <kevin.cy....@gmail.com>
Subject RE: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Date Fri, 12 Dec 2014 21:43:24 GMT
Well at first I thought it was the reply to my earlier question. It's not.
My first hour in the maillist loop so bear with the newbie's snoopy. In the
following is the copy of my earlier email to the derby faq group:

 

>>> 

 

WriteDdlToDatabase Verbose:

>>..

>>Returning connection org.apache.commons.dbcp.PoolableConnection@fc23cf to
data source.

>>Remaining connections: None

 

Exception:

F:\NBProject\DBTool\dist\build.xml:33:  which is <ddlToDatabase
schemaFile="project-schema.xml" verbosity="debug">

org.apache.ddlutils.io.DataSinkException: java.sql.SQLException: Connection
is closed.

         at
org.apache.ddlutils.io.DataToDatabaseSink.end(DataToDatabaseSink.java:221)

         at
org.apache.ddlutils.task.WriteDataToDatabaseCommand.execute(WriteDataToDatab
aseCommand.java:187)

         at
org.apache.ddlutils.task.DatabaseTaskBase.executeCommands(DatabaseTaskBase.j
ava:341)

         at
org.apache.ddlutils.task.DatabaseTaskBase.execute(DatabaseTaskBase.java:381)

         at
org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)

         at sun.reflect.GeneratedMethodAccessor120.invoke(Unknown Source)

         at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:43)

         at java.lang.reflect.Method.invoke(Method.java:601)

         at
org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)

         at org.apache.tools.ant.Task.perform(Task.java:348)

         at org.apache.tools.ant.Target.execute(Target.java:392)

         at org.apache.tools.ant.Target.performTasks(Target.java:413)

         at
org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)

         at org.apache.tools.ant.Project.executeTarget(Project.java:1368)

         at
org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.j
ava:41)

         at org.apache.tools.ant.Project.executeTargets(Project.java:1251)

         at
org.apache.tools.ant.module.bridge.impl.BridgeImpl.run(BridgeImpl.java:283)

         at
org.apache.tools.ant.module.run.TargetExecutor.run(TargetExecutor.java:541)

         at
org.netbeans.core.execution.RunClassThread.run(RunClassThread.java:153)

Caused by: java.sql.SQLException: Connection is closed.

 

I followed the instructions every steps away written in the derby official
site http://db.apache.org/derby/integrate/db_ddlutils.html. And my derby db
is well configured can export db to xml with no glitches, Can anyone please
shed some lights on where could it possibly go wrong?

Thanks

Kev

 

 

  _____  

From: Mamta Satoor [mailto:msatoor@gmail.com] 
Sent: Saturday, December 13, 2014 4:36 AM
To: Derby Discussion
Subject: Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement

 

I ran the repro script you provided against DB2 and it worked fine so
definitely seems like a problem with Derby. I will go ahead and add a jira
issue for it.

 

On Fri, Dec 12, 2014 at 11:40 AM, Mamta Satoor <msatoor@gmail.com> wrote:

That's right, it was introduced in 10.11. 

 

I looked at the manual for CREATE TRIGGER
http://db.apache.org/derby/docs/10.11/ref/rrefsqlj43125.html and for the
when clause, there needs to be a Boolean expression which is explained at
http://db.apache.org/derby/docs/10.11/ref/rrefsqlj23075.html#rrefsqlj23075.
I am looking there to see if the syntax for Boolean expression in the CREATE
TRIGGER eg you provided is incorrect but from the initial looking, it looks
like you have it correct.

 

On Fri, Dec 12, 2014 at 11:34 AM, Alex <alex3kov@zoho.com> wrote:

Reference Manual says that WHEN clause has no meaning in releases prior to
10.11:
http://db.apache.org/derby/docs/10.11/ref/index.html

 

  _____  


From: Mamta Satoor  <mailto:msatoor@gmail.com> <msatoor@gmail.com>
Sent: Friday, December 12, 2014 10:07PM
To: Derby Discussion  <mailto:derby-user@db.apache.org>
<derby-user@db.apache.org>
Subject: Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement

I get the same behavior. I will give it a try on earlier releases and report
the results

 

Mamta

 

On Fri, Dec 12, 2014 at 1:50 AM, Alex <alex3kov@zoho.com> wrote:

Hello,
In the below example, I expect trigger to fire and update the 'done_date'
column after update of 'status', but it doesn't. The database is a freshly
created 10.11. Is this a bug in derby, or am I doing something wrong?

ij version 10.11 
ij> connect 'jdbc:derby:MyDbTest;create=true'; 
ij> CREATE TABLE t1 (id INTEGER, done_date DATE, status CHAR(1)); 
0 rows inserted/updated/deleted 
ij> CREATE TRIGGER tr1 AFTER UPDATE OF status ON t1 REFERENCING NEW AS
newrow FOR EACH ROW WHEN (newrow.status='d') UPDATE t1 SET
done_date=current_date WHERE id=newrow.id; 
0 rows inserted/updated/deleted 
ij> insert into t1 values (1, null, 'a'); 
1 row inserted/updated/deleted 
ij> SELECT * FROM t1; 
ID         |DONE_DATE |STA& 
--------------------------- 
1          |NULL      |a    
 
1 row selected 
ij> UPDATE t1 SET status='d'; 
1 row inserted/updated/deleted 
ij> SELECT * FROM t1; 
ID         |DONE_DATE |STA& 
--------------------------- 
1          |NULL      |d    
 
1 row selected 
ij> exit; 

-- 
--Regards, Alex

 

--Regards, Alex

 

 


Mime
View raw message