how to make shell script executing several informix sqls?

[Patten]

Hey,

I want to make a shell script which executes several sql stored in different files after each other.

I made following shell script:

dbaccess DBNAME SQLSCRIPTNAME1
dbaccess DBNAME SQLSCRIPTNAME2

and so on.

I get the error 329: Database not found or no system permissions.

I guess the problem is in my SQLSCRIPTNAME1It looks like: select * from TABLE;

I suppose first of all I have to do the logon. How do I do this? And where to do it: one time before calling the different SQLSCRIPTNAMEx or with every call?

 

[olded]

Patten:

In the example, it looks like you're missing some '$' from the shell variables. Here's an example of how I use dbaccess/isql in shell scripts:

Regards,

Ed

#!/bin/ksh
# this script your database environment and PATH variables
# are set up for informix

# database name
DBNAME="testdb"; export DBNAME

# use dbaccess or isql
INTERFACE_CMD="dbaccess"; export INTERFACE_CMD

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

# send errors and output to files and standard output
DBCOMMAND_OTF="eval $INTERFACE_CMD -e $DBNAME 2>> err.txt |tee /dev/tty >> output.txt";
export DBCOMMAND_OTF

#use a unix 'here' document, send output of sql to file
$DBCOMMAND << MSG
output to temp.txt without headings
SELECT * FROM systables WHERE tabname = &quot;systables&quot;
MSG

# or have your input in a file
# Assume 'file.sql' contains
# SELECT * FROM systables WHERE tabname = &quot;systables&quot;
$DBCOMMAND_OTF < file.sql

 

[vpshriyan]

Hi Patten,

It seems you are not set the required environment variables related to informix database. See the sample below.

Regards,
Shriyan


#!/bin/ksh

# First set informix related environment variables.

# informixdir is the informix software installed directory
INFORMIXDIR=/usr/informix; export INFORMIXDIR

# informixserver is same as set in the config paramenter
# DBSERVERNAME or DBSERVERALIASES in $INFORMIXDIR/etc/onconfig or
# the first field in the $INFORMIXDIR/etc/sqlhosts file
INFORMIXSERVER=test_server; export INFORMIXSERVER

# script file ideally have an extension .sql
# each line of the input file should end with a semicolon(
# output and errors are logged to script1.log file
$INFORMIXDIR/bin/dbaccess -e testdb script1.sql > script1.log 2>&1