Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 86005 invoked from network); 2 Sep 2005 07:53:04 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 2 Sep 2005 07:53:04 -0000 Received: (qmail 22616 invoked by uid 500); 2 Sep 2005 07:53:01 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 22578 invoked by uid 500); 2 Sep 2005 07:53:01 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 22565 invoked by uid 99); 2 Sep 2005 07:53:00 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Sep 2005 00:53:00 -0700 X-ASF-Spam-Status: No, hits=0.9 required=10.0 tests=DNS_FROM_RFC_ABUSE,HTML_40_50,HTML_MESSAGE,SPF_HELO_FAIL,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of joachim.stumpf@de.ibm.com designates 195.212.29.151 as permitted sender) Received: from [195.212.29.151] (HELO mtagate2.de.ibm.com) (195.212.29.151) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Sep 2005 00:53:15 -0700 Received: from d12nrmr1607.megacenter.de.ibm.com (d12nrmr1607.megacenter.de.ibm.com [9.149.167.49]) by mtagate2.de.ibm.com (8.12.10/8.12.10) with ESMTP id j827qvd7204292 for ; Fri, 2 Sep 2005 07:52:57 GMT Received: from d12av02.megacenter.de.ibm.com (d12av02.megacenter.de.ibm.com [9.149.165.228]) by d12nrmr1607.megacenter.de.ibm.com (8.12.10/NCO/VERS6.7) with ESMTP id j827qvha139854 for ; Fri, 2 Sep 2005 09:52:57 +0200 Received: from d12av02.megacenter.de.ibm.com (loopback [127.0.0.1]) by d12av02.megacenter.de.ibm.com (8.12.11/8.13.3) with ESMTP id j827qvEA018442 for ; Fri, 2 Sep 2005 09:52:57 +0200 Received: from d12ml065.megacenter.de.ibm.com (d12ml065.megacenter.de.ibm.com [9.149.167.11]) by d12av02.megacenter.de.ibm.com (8.12.11/8.12.11) with ESMTP id j827qvtI018439 for ; Fri, 2 Sep 2005 09:52:57 +0200 In-Reply-To: <43179364.907@Sourcery.Org> Importance: Normal MIME-Version: 1.0 Sensitivity: To: "Derby Discussion" Subject: Re: Derby and Triggers X-Mailer: Lotus Notes Release 6.0.2CF1 June 9, 2003 Message-ID: From: Joachim G Stumpf Date: Fri, 2 Sep 2005 09:52:50 +0200 X-MIMETrack: Serialize by Router on D12ML065/12/M/IBM(Release 6.53HF247 | January 6, 2005) at 02/09/2005 09:52:56, Serialize complete at 02/09/2005 09:52:56 Content-Type: multipart/alternative; boundary="=_alternative 002B4B7FC1257070_=" X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This is a multipart message in MIME format. --=_alternative 002B4B7FC1257070_= Content-Type: text/plain; charset="US-ASCII" Hi Jean , Hi Satheesh, what i really want to know is the available syntax how to change data or add data in the actual insert. This is why i need the syntax to do this. Here are my tests . as you can see nearly everything is working , which leads me to the conclusion that there must be another way to set the values for the current insert statement. CREATE TABLE "ATV_BASE" ( "AT_ID_" NUMERIC(10, 0) NOT NULL, "V_ID_" NUMERIC(10, 0) NOT NULL, .... "N_DATE" DATE DEFAULT '1970-01-01' NOT NULL, "XN_BID_" NUMERIC(10, 0) DEFAULT 0 NOT NULL, "CHG_DATE_" DATE DEFAULT '1970-01-01' NOT NULL, "XCHG_BID_" NUMERIC(10, 0) DEFAULT 0 NOT NULL, .. PRIMARY KEY ("AT_ID_", "V_ID_") ); ij> create trigger atv_base1 no cascade before insert on ATV_BASE referencing new as new for each row mode db2sql set new.n_date_ = CURRENT_DATE; /*here i want to set a date . I will do it in a default value*/ ERROR 42X01: Syntax error: Encountered "set" at line 6, column 3. ij> create trigger atv_base2 no cascade before insert on ATV_BASE referencing new as new for each row mode db2sql set new.xn_bid_ = (select id_ from btab where bez_ = user); /*dependant on the user value which is char i want to get a number out of a separate table which i need for the current insert otherwise insert,which caused the trigger will fail*/ ERROR 42X01: Syntax error: Encountered "set" at line 6, column 3. What i would like to have is to use this in one trigger. mfg Joachim Stumpf DB2 Technical presales support Tel.: (+49) -7034-15-3276 Fax: (+49)-7034-15-3400 Internet: stumpfj@de.ibm.com Mobil: (+49)-172-733 9453 Developersite: http://www.ibm.com/software/data/developer Forum: http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19 Please respond to "Derby Discussion" To: Derby Discussion cc: Subject: Re: Derby and Triggers Hi Jean, You are correct about Derby triggers not being able to execute a procedure. Since I have seen this requested many times before and since this is the only way to have multiple SQL statements in a trigger (that modify data), I have filed Derby-551 enhancement request. I believe Derby can be easily changed to support stored procedures in a trigger. Satheesh Jean T. Anderson wrote: Next, a trigger can't execute a procedure, but it can execute a function; however, functions are "read only" (can't update data in the database). Here's one very simple example that shows the syntax for a trigger that invokes a function implemented in Java: http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3c42A0E031.60003@bristowhill.com%3e Often when I'm looking for an example on how to do something, I look at the functional tests: http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ If none of this information helps, please feel free to post more questions. I'm interested in this area and will try to assemble a more helpful writeup on the topic that consolidates the information in all these various posts. -jean Joachim G Stumpf wrote: Hi, i have to convert SQL Syntax to DERBY from Interbase. I use Derby 10 and customer uses 10.1 . So i downloaded Alpha version of reference Doku. Now i have to define a trigger Original CREATE TRIGGER "ATV_tr1" FOR "tab1" ACTIVE BEFORE INSERT POSITION 0 as declare variable bId integer; begin select id_ from tab2 where bez_ = user into :bId; if ( bId IS NULL ) then bId = 0; new.xn_b_id_ = bId; new.xad_b_id_ = bId; new.neu_date_ = 'now'; new.aend_date_ = 'now'; end ; I found db2 syntax which is similar to Derby. create trigger atv_basis no cascade before insert on ADM_TR_VORGABE_BASIS referencing new as new for each row mode db2sql begin atomic set new.neu_datum_ = CURRENT_DATE; set new.aend_datum_ = CURRENT_DATE; end; This isn't working too. Can somebody help me out? mfg Joachim Stumpf DB2 Technical presales support Tel.: (+49) -7034-15-3276 Fax: (+49)-7034-15-3400 Internet: stumpfj@de.ibm.com Mobil: (+49)-172-733 9453 Developersite: http://www.ibm.com/software/data/developer Forum: http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19 --=_alternative 002B4B7FC1257070_= Content-Type: text/html; charset="US-ASCII"
Hi Jean , Hi Satheesh,
what i really want to know is the available syntax how to change data or add data in the actual insert.
This is why i need the syntax to do this.
Here are my tests . as you can see nearly everything is working , which leads me to the conclusion that there must be another way to set the values for the current insert statement.

CREATE TABLE "ATV_BASE"
( "AT_ID_"        NUMERIC(10, 0) NOT NULL,
  "V_ID_"        NUMERIC(10, 0) NOT NULL,
 ....
  "N_DATE"        DATE DEFAULT '1970-01-01' NOT NULL,
  "XN_BID_"        NUMERIC(10, 0) DEFAULT 0 NOT NULL,
  "CHG_DATE_"        DATE DEFAULT '1970-01-01' NOT NULL,
  "XCHG_BID_"        NUMERIC(10, 0) DEFAULT 0 NOT NULL,
 ..
PRIMARY KEY ("AT_ID_", "V_ID_")
);

ij> create trigger atv_base1
  no cascade before
  insert on ATV_BASE
  referencing new as new
  for each row mode db2sql
  set new.n_date_ = CURRENT_DATE; /*here i want to set a date . I will do it in a default value*/
ERROR 42X01: Syntax error: Encountered "set" at line 6, column 3.
ij> create trigger atv_base2
  no cascade before
  insert on ATV_BASE
  referencing new as new
  for each row mode db2sql
  set new.xn_bid_ = (select id_ from btab where bez_ = user); /*dependant on the user value which is char i want to get a number out of a separate table which i need for the current insert otherwise insert,which caused the trigger will fail*/  
ERROR 42X01: Syntax error: Encountered "set" at line 6, column 3.
What i would like to have is to use this in one trigger.

mfg
Joachim Stumpf   DB2 Technical presales support
Tel.: (+49) -7034-15-3276     Fax:   (+49)-7034-15-3400
Internet: stumpfj@de.ibm.com    Mobil: (+49)-172-733 9453
Developersite: http://www.ibm.com/software/data/developer
Forum: http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

Please respond to "Derby Discussion" <derby-user@db.apache.org>

To:        Derby Discussion <derby-user@db.apache.org>
cc:        
Subject:        Re: Derby and Triggers


Hi Jean,

You are correct about Derby triggers not being able to execute a procedure. Since I have seen this requested many times before and since this is the only way to have multiple SQL statements in a trigger (that modify data), I have filed Derby-551 enhancement request. I believe Derby can be easily changed to support stored procedures in a trigger.

Satheesh

Jean T. Anderson wrote:

Next, a trigger can't execute a procedure, but it can execute a function; however, functions are "read only" (can't update data in the database). Here's one very simple example that shows the syntax for a trigger that invokes a function implemented in Java:

http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3c42A0E031.60003@bristowhill.com%3e

Often when I'm looking for an example on how to do something, I look at the functional tests:

http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/

If none of this information helps, please feel free to post more questions.  I'm interested in this area and will try to assemble a more helpful writeup on the topic that consolidates the information in all these various posts.

-jean



Joachim G Stumpf wrote:

Hi,
i have to convert SQL Syntax to DERBY from Interbase.
I use Derby 10 and customer uses 10.1 . So i downloaded Alpha version of reference Doku.

Now i have to define a trigger

Original
CREATE TRIGGER "ATV_tr1" FOR "tab1"
ACTIVE BEFORE INSERT POSITION 0
as
declare variable bId integer;
begin
       select id_        from tab2 where bez_ = user into :bId;
       if ( bId IS NULL ) then bId = 0;
       new.xn_b_id_ = bId;
       new.xad_b_id_ = bId;
       new.neu_date_    = 'now';
       new.aend_date_   = 'now';
end
;

I found db2 syntax which is similar to Derby.

create trigger atv_basis
 no cascade before
 insert on ADM_TR_VORGABE_BASIS
 referencing new as new
 for each row mode db2sql
 begin atomic
          set new.neu_datum_    = CURRENT_DATE;
       set new.aend_datum_   = CURRENT_DATE;

end;

This isn't working too.
Can somebody help me out?


mfg
Joachim Stumpf   DB2 Technical presales support
Tel.: (+49) -7034-15-3276     Fax:   (+49)-7034-15-3400
Internet: stumpfj@de.ibm.com    Mobil: (+49)-172-733 9453

Developersite: http://www.ibm.com/software/data/developer
Forum: http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19



--=_alternative 002B4B7FC1257070_=--