Hi!
I need execute a shell script whiting a SP.
Example:
Create procedure my_sp(...)
...
system "/home/myuser/myprogram.sh";
..
end procedure;

myprogram.sh inside...
unload to myarchive delimiter ","
select * from mytable;

This is a posible??

Sorry, my english is BAD!

Regards,
Oscar.

Sorry, but this won't work. This syntax:

unload to myarchive delimiter ","
select * from mytable;

is an informix command - not a unix command. The operating system will not recognize load as a valid command.

You can embed unload into an informix program or a stored proecedure and then call the stored proecedure.

Probably the best way is executing the unload command from dbaccess or isql.

Also, you can execute dbaccess or isql commands from a unix shell script:

#!/bin/ksh
# set to your database name
DBNAME="mydatabasename"; export DBNAME
# use dbaccess or isql
INTERFACE_CMD="dbaccess"; export INTERFACE_CMD
# send to standard output
DBCOMMAND="$INTERFACE_CMD -e $DBNAME"; export DBCOMMAND
engine_up=$(onstat - | grep "Up" | wc -l)
if [ $engine_up -eq 0 ]
then
   echo "Informix Engine must be Up to execute commands."
exit
fi
$DBCOMMAND << MSG
  unload to myarchive delimiter ","
  select * from mytable
MSG

Thanks for your reply.

execute perfect!!!

Regards,

Oscar.

Hi!!

2 more questions!!

This shell script, run perfectly with commands informix on tables for DBsystem

example:
select distinct 'Hi' from systables

but, does not run properly with temporal tables.

example:
select id,name from tmp_myTable;

where tmp_myTable was create:

select * from TABLE
into temp tmp_myTable with no log;
----------
Gives the following error:

exception : looking for handler
SQL error = -668 ISAM error = -1

WHY?

string conection:
$INTERFACE_CMD -e $DBNAME

I need connect with user/pass, this is posible?

Regards,
Oscar.

QUESTION 1)
I am not having any problems. This works for me:

$DBCOMMAND << MSG
SELECT * FROM s_user INTO TEMP my_table WITH NO LOG;
SELECT * FROM my_table WHERE user_id = "nails"
MSG

Based on the -668 error, it looks like you don't have permission to create temporary tables.

QUESTION 2)

Every Unix version of Informix I've worked on, the dbaccess utility does not require a user or password. The Informix DBA controlled access using the Informix GRANT and REVOKE commands. Are you using the Window's version of Informix? If so, perhaps it requires a user and password?

I have absolutely no experience with the Window's version of Informix. Let's assume that dbaccess requires a -U for user option and a -P for password. You'll have to check. Then, consider something like this:

# ABSOLUTELY UNTESTED!!!  eval maybe optional
DBCOMMAND="eval $INTERFACE_CMD -e $DBNAME -U $USER -P $PASSWD"
I have no idea whether the above string will work, and it is up to you to provide $USER and $PASSWD variables.

NAILS:

Thank´s very much.

I can´t understand this problem.

look:

Informix (Unix, i'm not using win):

SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.

select * from tmp_bas1
into temp mytt with no log;

10802 row(s) retrieved into temp table.

I have permission to create temporary tables.

Shell Script:

select * from mytt;

exception : looking for handler
SQL error = -668 ISAM error = -1

I appreciate your help.

Regards,
Oscar.

A temporary table only exists for the life of your dbaccess/isql instance. Once you leave dbaccess, the temp table vanishes.

Post your script, and I'll look at it.

Hi Nails,

This is my shell script,

#!/bin/ksh
#
DBNAME="mydbname"; export DBNAME;

INTERFACE_CMD="dbaccess"; export INTERFACE_CMD;

# send to standard output
DBCOMMAND="$INTERFACE_CMD -e $DBNAME"; export DBCOMMAND;

engine_up=$(onstat - | grep "Up" | wc -l)

if [ $engine_up -eq 0 ]
then
"Informix Engine must be Up to execute commands"
exit
fi

$DBCOMMAND<< MSG
UNLOAD TO ARCHIVE2008.csv DELIMITER ","
SELECT DISTINCT "FIELD1","FIELD2","FIELD3","FIELD4" FROM SYSTABLES
UNION ALL
SELECT DISTINCT * FROM TMPUP;

MSG
exit

Thank´s
Regards,

Oscar.

Since I'm not at all familiar with your data I can only make a few guesses:

1) What is the table TMPUP? Based on the name, is it a temp table? I stated in an earlier post, temp tables only exist while your program - dbaccess in this case - is running. The only was your script will work is if TMPUP is a normal database table or if you create it in the same instance of $DBCOMMAND.

2) What is this???:

SELECT DISTINCT "FIELD1","FIELD2","FIELD3","FIELD4" FROM SYSTABLES

SYSTABLES is an informix system table containing information of the database's tables. FIELD1, FIELD2, etc. are not valid column names for this table.

I can't tell from your post, but are FIELD1, FIELD2, etc suppose to be shell variables?

If they are, you need a dollar sign in front:

$FIELD1, $FIELD2

Sorry, that's all I can tell you.

Hi Nails,

Let me be a little more clear.

1) TMPUP is my temp table.

2)select "HELLO" from systables;

Returns "HELLO"

The table SYSTABLES in Informix is similar to the table DUAL in ORACLE

select "HELLO" from DUAL return: "HELLO"

3)
"FIELD1", "FIELD2", etc, etc... are headed in my archive ARCHIVE2008.csv

4)Then

UNLOAD TO ARCHIVE2008.csv DELIMITER ","
SELECT DISTINCT "FIELD1","FIELD2","FIELD3","FIELD4" FROM SYSTABLES
UNION ALL
SELECT DISTINCT * FROM TMPUP;

return ARCHIVE2008.csv where

ARCHIVE2008.cvs is:

FIELD1,FIELD2,FIELD3,FIELD4
125, PAUL,Smith ,15934
126,ROBERT,Khan ,16545
. . . .
. . . .
. . . .
. . . .

Thank´s NAILS,
Regards,
Oscar.

OK, I understand what you are doing with systables. That should work.

However, what I said about the temporary table still goes.

$DBCOMMAND<< MSG
-- create temp table TMPUP before the unload
UNLOAD TO ARCHIVE2008.csv DELIMITER ","
SELECT DISTINCT "FIELD1","FIELD2","FIELD3","FIELD4" FROM SYSTABLES
UNION ALL
SELECT DISTINCT * FROM TMPUP;

MSG

The TMPUP temp table MUST be created in the same DBCOMMAND instance.