Re: Running scripts within SQL

howie.lfc@googlemail.com wrote: > 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)); > > **Stored Procedure called from trigger** > > create procedure sp_dev (customerno varchar(10))> > system (/root/trigger_tccom010.sql otccom9100m000 customerno); Presumably, there are some quotes lurking around that SYSTEM statement? > end procedure; > > **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" Well, that is not often a good choice of user ID, for security reasons. > 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 ******* If the executed script exits with a status other than 0, you will get error -668 and the ISAM code will be the actual exit status. You're getting an ISAM error code of -1; most likely, your script is exiting without ensuring that it's exit code is zero. Equivalently, the /baan4c2/bsr/bin/ba6.1 program is returning an error status. You might be able to 'fix' it by adding 'exit 0' after the command. However, you might also just be ignoring an error that you need to pay attention to. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-1748 sha384 2007-11-10 00:00:04 387FA2589FACC7426A9BD784A8C28FF85EA3FF7073EF38E7654F3CE14AE3B53AEF1CD2 7BCE0B3B36E007590124031E2