Re: Stored Procedure Problem

On Nov 12, 2:34 am, "howie....@googlemail.com" <howie....@googlemail.com> wrote: > 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; Syntactically, you seem to be wanting to build up a command string consisting of a fixed portion "/root/trigger_tccom010.sql otccom9100m000" and the value of the procedure parameter - customerno. You use the || operator to concatenate strings: LET cmd = "/root/trigger... 000" || customerno; Which user owns the /root directory? It looks like it might be root - the superuser. May I respectfully suggest that you review the security of your system. User root should not be involved in the running of Informix DBMS except by ensuring that the system as a whole runs smoothly and provides sufficient resources and sufficient protection. In general, user informix should be used to administer the DBMS instance(s) as a whole. Neither root nor informix should (ideally) administer a specific application database within an instance. In my book, it is slightly more acceptable for user informix to do the administration - simply because a lot of sites actually do so and there were historically no suggestions that this was not a good idea. -=JL=-