Stored Procedure Problem
Following my previous question posted on the 23rd October.....
IDS 9.40.FC4 HPUX 11.11
We are now using stored procedure but are now experiencing other
problems.....namely we are getting errid 668
What we are running is as follows
An Informix trigger (tccom010.test) has been defined on BaaN table
tccom010. This trigger then calls a stored procedure (sp_dev). The
stored procedure then calls a unix script (/root/
trigger_tccom010.sql)
containing a call to a BaaN object.
**Trigger**
CREATE TRIGGER tccom010.test
INSERT ON 'baan'.ttccom010700
REFERENCING NEW AS tccom010
FOR EACH ROW
(execute procedure sp_dev(tccom010.t_cuno));
commit;
**Stored Procedure called from trigger**
create procedure sp_dev (customerno varchar(10))
system (/root/trigger_tccom010.sql otccom9100m000 customerno);
end procedure;
commit;
**Script called from stored procedure**
(Located at /root/trigger_tccom010.sql)
/baan4c2/bse/bin/ba6.1 $1 $2
We are running this as user "root"
Sample form $BSE/log/log.informix
2007-11-06[09:39:50]:E:root: ******* S T A R T of Error message
*******
2007-11-06[09:39:50]:E:root: Log message called from /view/port.6.1c.
07.04/vobs/tt/servers/INFORMIX_1/inf_error.c: #379 keyword:
EXECUTE_STMT
2007-11-06[09:39:50]:E:root: Pid 3346 Uid 0 Euid 0 Gid 3 Egid 3
2007-11-06[09:39:50]:E:root: user_type S language 2 user_name root
tty
ote locale ISO88591/NULL
2007-11-06[09:39:50]:E:root: Errno 0 bdb_errno 0
2007-11-06[09:39:50]:E:root: Log_mesg: Code -668 ISAM err -1 #rows 0
Lrow 0 Offset 423
2007-11-06[09:39:50]:E:root: ******* E N D of Error message *******
> -----Original Message-----
> From: informix-list-bounces@iiug.org [mailto:informix-list-
> bounces@iiug.org] On Behalf Of howie.lfc@googlemail.com
> Sent: Friday, November 09, 2007 2:51 PM
> To: informix-list@iiug.org
> Subject: Stored Procedure Problem
>
> Following my previous question posted on the 23rd October.....
>
> IDS 9.40.FC4 HPUX 11.11
>
>
> We are now using stored procedure but are now experiencing other
> problems.....namely we are getting errid 668
> What we are running is as follows
>
>
> An Informix trigger (tccom010.test) has been defined on BaaN table
> tccom010. This trigger then calls a stored procedure (sp_dev). The
> stored procedure then calls a unix script (/root/
> trigger_tccom010.sql)
> containing a call to a BaaN object.
>
>
> **Trigger**
>
>
> CREATE TRIGGER tccom010.test>
>
> INSERT ON 'baan'.ttccom010700
>
>
> REFERENCING NEW AS tccom010
>
>
> FOR EACH ROW
>
>
> (execute procedure sp_dev(tccom010.t_cuno));
>
>
> commit;
>
>
> **Stored Procedure called from trigger**
>
>
> create procedure sp_dev (customerno varchar(10))>
>
> system (/root/trigger_tccom010.sql otccom9100m000 customerno);
Doesn't this need some quote marks????
Try this:
DEFINE cmd CHAR(200);
LET cmd = "/root/trigger_tccom010.sql otccom9100m000 ", customerno;
SYSTEM (cmd);
--EEM
>
>
> end procedure;
>
>
> commit;
>
>
> **Script called from stored procedure**
>
>
> (Located at /root/trigger_tccom010.sql)
>
>
> /baan4c2/bse/bin/ba6.1 $1 $2
>
>
> We are running this as user "root"
>
>
> Sample form $BSE/log/log.informix
>
>
> 2007-11-06[09:39:50]:E:root: ******* S T A R T of Error message
> *******
> 2007-11-06[09:39:50]:E:root: Log message called from /view/port.6.1c.
> 07.04/vobs/tt/servers/INFORMIX_1/inf_error.c: #379 keyword:
> EXECUTE_STMT
> 2007-11-06[09:39:50]:E:root: Pid 3346 Uid 0 Euid 0 Gid 3 Egid 3
> 2007-11-06[09:39:50]:E:root: user_type S language 2 user_name root
> tty
> ote locale ISO88591/NULL
> 2007-11-06[09:39:50]:E:root: Errno 0 bdb_errno 0
> 2007-11-06[09:39:50]:E:root: Log_mesg: Code -668 ISAM err -1 #rows 0
> Lrow 0 Offset 423
> 2007-11-06[09:39:50]:E:root: ******* E N D of Error message *******
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
On 9 Nov, 21:18, "Everett Mills" <eemi...@nationalbeef.com> wrote:
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org [mailto:informix-list-
> > boun...@iiug.org] On Behalf Of howie....@googlemail.com
> > Sent: Friday, November 09, 2007 2:51 PM
> > To: informix-l...@iiug.org
> > Subject: Stored Procedure Problem
>
> > Following my previous question posted on the 23rd October.....
>
> > IDS 9.40.FC4 HPUX 11.11
>
> > We are now using stored procedure but are now experiencing other
> > problems.....namely we are getting errid 668
> > What we are running is as follows
>
> > An Informix trigger (tccom010.test) has been defined on BaaN table
> > tccom010. This trigger then calls a stored procedure (sp_dev). The
> > stored procedure then calls a unix script (/root/
> > trigger_tccom010.sql)
> > containing a call to a BaaN object.
>
> > **Trigger**
>
> > CREATE TRIGGER tccom010.test>
> > INSERT ON 'baan'.ttccom010700
>
> > REFERENCING NEW AS tccom010
>
> > FOR EACH ROW
>
> > (execute procedure sp_dev(tccom010.t_cuno));
>
> > commit;
>
> > **Stored Procedure called from trigger**
>
> > create procedure sp_dev (customerno varchar(10))>
> > system (/root/trigger_tccom010.sql otccom9100m000 customerno);
>
> Doesn't this need some quote marks????
>
> Try this:
>
> DEFINE cmd CHAR(200);
>
> LET cmd = "/root/trigger_tccom010.sql otccom9100m000 ", customerno;
>
> SYSTEM (cmd);
>
> --EEM
>
>
>
>
>
> > end procedure;
>
> > commit;
>
> > **Script called from stored procedure**
>
> > (Located at /root/trigger_tccom010.sql)
>
> > /baan4c2/bse/bin/ba6.1 $1 $2
>
> > We are running this as user "root"
>
> > Sample form $BSE/log/log.informix
>
> > 2007-11-06[09:39:50]:E:root: ******* S T A R T of Error message
> > *******
> > 2007-11-06[09:39:50]:E:root: Log message called from /view/port.6.1c.
> > 07.04/vobs/tt/servers/INFORMIX_1/inf_error.c: #379 keyword:
> > EXECUTE_STMT
> > 2007-11-06[09:39:50]:E:root: Pid 3346 Uid 0 Euid 0 Gid 3 Egid 3
> > 2007-11-06[09:39:50]:E:root: user_type S language 2 user_name root
> > tty
> > ote locale ISO88591/NULL
> > 2007-11-06[09:39:50]:E:root: Errno 0 bdb_errno 0
> > 2007-11-06[09:39:50]:E:root: Log_mesg: Code -668 ISAM err -1 #rows 0
> > Lrow 0 Offset 423
> > 2007-11-06[09:39:50]:E:root: ******* E N D of Error message *******
>
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Tried this but it gave a syntax error. Had to put both parameters
within the quotes on the LET command. My latest attempt is as shown
below but this still gives the 668 informix error. Any other ideas?
create procedure sp_dev (customerno varchar(10))
define cmd CHAR(200);
LET cmd="/root/trigger_tccom010.sql otccom9100m000 customerno";
SYSTEM(cmd);
end procedure;
commit;
sounds like your script is failing....
try:
LET cmd=" ( /root/trigger_tccom010.sql otccom9100m000 customerno ) > /
tmp/scripterr.log 2>&1";
make sure /tmp/scripterr.log does not exist....
check what is in /tmp/scripterr.log
fix the problem and change cmd back to what it was.
Superboer.
way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_-
_oerend_hard.html
>
> create procedure sp_dev (customerno varchar(10))>
> define cmd CHAR(200);
>
> LET cmd="/root/trigger_tccom010.sql otccom9100m000 customerno";
>
> SYSTEM(cmd);
>
> end procedure;
>
> commit;
On 9 Nov, 20:51, "howie....@googlemail.com" <howie....@googlemail.com> wrote: <Stuff about his SP> Dunno if this may help but have you tried setting a debug file? Along the lines of SET DEBUG FILE TO '/tmp/ sp_dev.dbg'; TRACE ON; Just after the CREATE PROCEDURE statement. (Remember the semicolons). May give some more clues.......