Hello all,

Quick question for you here,

If I am running a piece of SQL, is it possible to run a script from
within the SQL.....in oracle I know you would use the "@" so what
would you use in Informix?

Thanks in advance.

Cheers,

Howard

On Tue, 2007-10-23 at 13:04 +0000, howie.lfc (AT) googlemail (DOT) com wrote:
As far as I know it's not possible to run shell scripts from SQL (if
that's what you mean). The canonical solution is to make one shell
script from which you run SQL scripts and other commands/scripts, or use
a programming language such as Python or Perl.

--
Carsten Haese
http://informixdb.sourceforge.net

howie.lfc (AT) googlemail (DOT) com wrote:
have a look at SQSL (see my sig) it will do that and much more
--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Hi,

not knowing oracle and their "@", I'm not sure what
exactly you are trying to achieve ...:

- running a shell script locally (on client machine)?:

Depends on the client you use as it is interpreting
the SQL. E.g. with dbaccess you can use the '!', like
in the following 3-line SQL script "hello.sql":

select name from sysdatabases;
! echo "Hello, world\!"
select name from sysdatabases;

which you then run with the command:

dbaccess sysmaster hello.sql

- running a shell script on the server machine?:

I think you can do that by using a stored procedure
and the "system ..." command.
Of course the shell script must be present on the
server machine.

Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich, Germany
Information Management

IBM Deutschland GmbH
Chairman of the Supervisory Board: Hans Ulrich Märki
Board of Management: Martin Jetter (Chairman), Rudolf Bauer, Christian
Diedrich, Christoph Grandpierre, Matthias Hartmann, Thomas Fell, Michael
Diemer
Corporate Seat: Stuttgart, Germany; Reg.-Gericht: Amtsgericht Stuttgart,
HRB-Nr.: 14 562 WEEE-Reg.-Nr. DE 99369940

informix-list-bounces (AT) iiug (DOT) org wrote on 23.10.2007 15:04:31:

From the keyboard of "howie.lfc (AT) googlemail (DOT) com"
<howie.lfc (AT) googlemail (DOT) com>:

The "@" that is used to submit some SQL script is, AFAIK, a
feature of the SQL tool, SQLplus or whatever name it has.

There is a SQL tool in Informix, dbaccess, that also can run SQL
scripts. See the respective manual.

HTH
Christian
--
#include <std_disclaimer.h>
/* The opinions stated above are my own and not
necessarily those of my employer. */

Having to live in an Oracle world, just because something can be done,
doesn't mean that its a good idea to do it.
(Oracle temp tables anyone?)

As pointed out already, you asked about running a command from SQL. I
believe you're actually talking about PL/SQL and exec'ing a shell script
from a stored procedure. Already pointed out, use the system command.

If you wanted to call a script from an adhoc sql, that would not be a good
idea. (Try and be paranoid and think about the potential dangers.)

HTH



__________________________________________________ _______________
Capture the missing critters!** Play Search Queries and earn great prizes.
http://club.live.com/search_queries....ltextlink1_oct

On Oct 23, 8:24 am, Carsten Haese <cars... (AT) uniqsys (DOT) com> wrote:
or Ruby ;-)

Right then....

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 *******

howie.lfc (AT) googlemail (DOT) com wrote:
Presumably, there are some quotes lurking around that SYSTEM statement?

Well, that is not often a good choice of user ID, for security reasons.

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 (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-1748 sha384 2007-11-10 00:00:04
387FA2589FACC7426A9BD784A8C28FF85EA3FF7073EF38E765 4F3CE14AE3B53AEF1CD2
7BCE0B3B36E007590124031E2