Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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 DBCOMMANDengine_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
0 
Hi!!
2 more questions!!
This shell script, run perfectly with commands informix on tables for DBsystem
example:
select distinct 'Hi' from systablesbut, 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 = -1WHY?
string conection:
$INTERFACE_CMD -e $DBNAMEI need connect with user/pass, this is posible?
Regards,
Oscar.
0
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"
MSGBased 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 optionalDBCOMMAND="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.
0
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 = -1I appreciate your help.
Regards,
Oscar.
0
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.
0
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.
0
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.
0
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.csv4)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.
0
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.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
