SP is showing 674 error inside a trigger

Posted in 2011

Topics: Stored Procedures & SPL, Server Administration, Security, Permissions & Auditing, Data Types & Schema Design, Triggers, Constraints & Referential Integrity

Sams George — Wed, 12 Oct 2011 20:21:54 — source: IIUG Forums & Mailing Lists

Dear All, I have a following DB schema. create table "informix".a (emp_number varchar(6) not null ); CREATE PROCEDURE "informix".d() REFERENCING NEW AS new OLD AS old FOR "informix".a; IF (INSERTING) THEN INSERT INTO b ( emp_number, emp_number_old,cts_info ) values ( NEW.emp_number, NULL,proc1()) END IF END PROCEDURe; create trigger "informix".c insert on "informix".a referencing new as post for each row ( execute procedure "informix".d() with trigger references ) ; create table "informix".b emp_number_old varchar(200),emp_number varchar(200),cts_info varchar(200)); I have another SP. drop procedure proc1; CREATE procedure proc1(appName varchar(128)) RETURNing char(200); DEFINE GLOBAL gvar char(200) DEFAULT ""; LET gvar = "xyz " || appName; RETURN gvar ; END procedure; I can execute it succesfully. echo 'execute procedure proc1("XYZ")'|dbaccess ctsinfo 2>/dev/null expression) xyz XYZ I am getting error 674 When I run the following SQL , to store the above value to a auditing table. echo 'insert into informix.a(emp_number) values("0000009")'|dbaccess ctsinfo 674: Routine (proc1) can not be resolved. Error in line 2 Pl. help me to resolve the issue. Thanks --bcaec520ecd327670f04af1fc61a

↪ replying to Sams George

Link, David A — Wed, 12 Oct 2011 21:07:07 — source: IIUG Forums & Mailing Lists

Sam, Don't you have to call proc1 with the variable? Without that it does not match the signature. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Sams George Sent: Wednesday, October 12, 2011 3:22 PM To: ids@iiug.org Subject: SP is showing 674 error inside a trigger [25184] Dear All, I have a following DB schema. create table "informix".a (emp_number varchar(6) not null ); CREATE PROCEDURE "informix".d() REFERENCING NEW AS new OLD AS old FOR "informix".a; IF (INSERTING) THEN INSERT INTO b ( emp_number, emp_number_old,cts_info ) values ( NEW.emp_number, NULL,proc1()) END IF END PROCEDURe; create trigger "informix".c insert on "informix".a referencing new as post for each row ( execute procedure "informix".d() with trigger references ) ; create table "informix".b emp_number_old varchar(200),emp_number varchar(200),cts_info varchar(200)); I have another SP. drop procedure proc1; CREATE procedure proc1(appName varchar(128)) RETURNing char(200); DEFINE GLOBAL gvar char(200) DEFAULT ""; LET gvar = "xyz " || appName; RETURN gvar ; END procedure; I can execute it succesfully. echo 'execute procedure proc1("XYZ")'|dbaccess ctsinfo 2>/dev/null expression) xyz XYZ I am getting error 674 When I run the following SQL , to store the above value to a auditing table. echo 'insert into informix.a(emp_number) values("0000009")'|dbaccess ctsinfo 674: Routine (proc1) can not be resolved. Error in line 2 Pl. help me to resolve the issue. Thanks --bcaec520ecd327670f04af1fc61a ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Sams George

Link, David A — Wed, 12 Oct 2011 21:10:10 — source: IIUG Forums & Mailing Lists

Sorry, I should have said in your trigger definition. INSERT INTO b ( emp_number, emp_number_old,cts_info ) values ( NEW.emp_number,NULL,proc1()) you need some value or variable in this proc1 call. -----Original Message----- From: Link, David A Sent: Wednesday, October 12, 2011 4:00 PM To: ids@iiug.org Subject: RE: SP is showing 674 error inside a trigger [25184] Sam, Don't you have to call proc1 with the variable? Without that it does not match the signature. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Sams George Sent: Wednesday, October 12, 2011 3:22 PM To: ids@iiug.org Subject: SP is showing 674 error inside a trigger [25184] Dear All, I have a following DB schema. create table "informix".a (emp_number varchar(6) not null ); CREATE PROCEDURE "informix".d() REFERENCING NEW AS new OLD AS old FOR "informix".a; IF (INSERTING) THEN INSERT INTO b ( emp_number, emp_number_old,cts_info ) values ( NEW.emp_number, NULL,proc1()) END IF END PROCEDURe; create trigger "informix".c insert on "informix".a referencing new as post for each row ( execute procedure "informix".d() with trigger references ) ; create table "informix".b emp_number_old varchar(200),emp_number varchar(200),cts_info varchar(200)); I have another SP. drop procedure proc1; CREATE procedure proc1(appName varchar(128)) RETURNing char(200); DEFINE GLOBAL gvar char(200) DEFAULT ""; LET gvar = "xyz " || appName; RETURN gvar ; END procedure; I can execute it succesfully. echo 'execute procedure proc1("XYZ")'|dbaccess ctsinfo 2>/dev/null expression) xyz XYZ I am getting error 674 When I run the following SQL , to store the above value to a auditing table. echo 'insert into informix.a(emp_number) values("0000009")'|dbaccess ctsinfo 674: Routine (proc1) can not be resolved. Error in line 2 Pl. help me to resolve the issue. Thanks --bcaec520ecd327670f04af1fc61a ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Link, David A

Sams George — Thu, 13 Oct 2011 02:23:28 — source: IIUG Forums & Mailing Lists

Thanks David, I want to pass and store the value of "XYZ" in table b( e.g - Auditing table) via this trigger. Regards On Thu, Oct 13, 2011 at 2:40 AM, Link, David A <DALink@west.com> wrote: > Sorry, I should have said in your trigger definition. > > INSERT INTO b (> > emp_number, emp_number_old,cts_info ) values ( NEW.emp_number,NULL,proc1()) > > you need some value or variable in this proc1 call. > > -----Original Message----- > From: Link, David A > Sent: Wednesday, October 12, 2011 4:00 PM > To: ids@iiug.org > Subject: RE: SP is showing 674 error inside a trigger [25184] > > Sam, > > Don't you have to call proc1 with the variable? Without that it does not > match > the signature. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Sams > George > Sent: Wednesday, October 12, 2011 3:22 PM > To: ids@iiug.org > Subject: SP is showing 674 error inside a trigger [25184] > > Dear All, > > I have a following DB schema. > > create table "informix".a (emp_number varchar(6) not null ); > > CREATE PROCEDURE "informix".d() > > REFERENCING NEW AS new OLD AS old FOR "informix".a; > > IF (INSERTING) > > THEN > > INSERT INTO b (> > emp_number, emp_number_old,cts_info ) values ( NEW.emp_number, > NULL,proc1()) > > END IF > END PROCEDURe; > > create trigger "informix".c insert on > > "informix".a referencing new as post > > for each row ( execute procedure "informix".d() > > with trigger references ) > ; > > create table "informix".b emp_number_old varchar(200),emp_number > varchar(200),cts_info varchar(200)); > > I have another SP. > > drop procedure proc1; > CREATE procedure proc1(appName varchar(128)) RETURNing char(200);> > DEFINE GLOBAL gvar char(200) DEFAULT ""; > > LET gvar = "xyz " || appName; > > RETURN gvar ; > > END procedure; > > I can execute it succesfully. > echo 'execute procedure proc1("XYZ")'|dbaccess ctsinfo 2>/dev/null > expression) xyz XYZ > > I am getting error 674 When I run the following SQL , to store the above > value > to a auditing table. > echo 'insert into informix.a(emp_number) values("0000009")'|dbaccess > ctsinfo > > 674: Routine (proc1) can not be resolved. > Error in line 2> > Pl. help me to resolve the issue. > Thanks > > --bcaec520ecd327670f04af1fc61a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec520e985f5308004af24d2ae

Glossary

dbaccess