stored procedure to execute OS command?

[noober]

Can someone post an example of how to use the informix stored procedure that allows you to execute an OS command -and- display the results? Say if I wanted to use dbaccess to execute ls -l in a stored procedure, I know thats kind of dumb but just a simple example, and I wanted to see the results in my query response.
-thanks in advance and merry x-mas!

 

[hiten1972]

Hi noobor

create table customer
(
customer_num serial(101),
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
);

Insert Statement Example:
INSERT INTO customer VALUES (0,'Ravi','Kumar','ABC Ltd', '213, SV Road',
'Santacruz West', 'Mumbai', 'MH', '054', '091-022-1234');
INSERT INTO customer VALUES (0,'mahesh','Kumar','ABC Ltd', '213, SV Road',
'Santacruz West', 'Mumbai', 'MH', '054', '091-022-1234');

If a procedure executes select statement, it can return more than one row.
We can handle such situation by using combination of keywords WITH RESUME
and RETURN.

The following example demonstrates a cursory procedure.

CREATE PROCEDURE read_return_many (lastname CHAR(15))
RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2), CHAR(5);
DEFINE r_lname, r_fname, r_city CHAR(15);
DEFINE r_add CHAR(20);
DEFINE r_state CHAR(2);
DEFINE r_zip CHAR(5);
FOREACH
SELECT fname, lname, address1, city, state, zipcode
INTO r_fname, r_lname, r_add, r_city, r_state, r_zip
FROM customer WHERE lname = lastname
RETURN r_fname, r_lname, r_add, r_city, r_state, r_zip
WITH RESUME;
END FOREACH;
END PROCEDURE;

EXECUTE PROCEDURE read_return_many("Kumar"


Regards
Hiten

 

[vpshriyan]

Hi,

One of the major drawbacks of Informix Stored Procedure is it's inability to perform dynamic SQL and interaction and handling of OS level commands. A SP is parsed & optimized when compiled, preventing the use of dynamic SQL. However, Informix's SYSTEM statement provides an ability to execute any operating system command from within a stored procedure. A word of caution though, of the overhead required to execute an operating system command, the SYSTEM command should be avoided in routines with tight performance requirements.

Here is an effort to create a Stored Procedure that takes an inbound parameter, ie., directory path and the SP lists the contents of passed directory. The SP assumes a base table in exist with the following specification:

create table tabx (sesid smallint, line varchar(255))

Regards,
Shriyan

--source code starts here
create dba procedure list_dir(path varchar(64)) returning varchar(255);

define xdb,target varchar(64);
define xsql varchar(255);
define xline varchar(255);
define xsesid smallint;

--see for existance of work space table
select tabname into target from systables where tabname='tabx';
if target != "tabx" then
raise exception -746, 0, target||" table does not exists.";
return 1;
end if

--Ascertain current database
select trim(odb_dbname) into xdb from sysmaster:sysopendb
where odb_sessionid=dbinfo('sessionid') and odb_iscurrent='Y';

--session related information
let xsesid=dbinfo('sessionid');
let target="/tmp/"||dbinfo('sessionid')||".txt";
let xsql="load from "||target||"1"||" insert into tabx";

--interpreted/formatted commands to the OS to execute
system "ls -lst "||path||" > "||target||"; sed 's/^/" || xsesid || "|'/ " || target || " > " || target||"1; " || 'echo "begin;' || xsql || ';commit"' || '|$INFORMIXDIR/bin/dbaccess ' || xdb || " ; rm " || target || " " || target || "1";

--gather inserted information from the base table row by row
foreach select line
into xline from tabx return xline
with resume;
end foreach;

--delete data relating to the current session
begin
delete from tabx where sesid=xsesid;
end

end procedure;

--source code ends here

 

[noober]

WOW! This forum exceeds expectations, thanks guys!

 

[noober]

Hello again Shriyan,
How did you know about the "system" stored procedure? I cannot find it documented anywhere!

 

[vpshriyan]

noober:

You may have a look at the link below, which points to an official documentation on informix SPL in pdf format. The title of the e-book is Informix Guide to SQL and the 8th chapter deals with creating and Using Stored Procedures.

http://www-3.ibm.com/software/data/informix/pubs/library/interim/4366-pdf.html

Chapter 8:
Creating and Using Stored procedure
Program Flow Control
Function Handling
Running an Operating-System Command from a Procedure

Regards,
Shriyan
"A good companion shortens the longest road."