Connecting to Informix from shell script

tanmoy.m · #1 (**permalink**) 02-20-08, 02:12

Dear All,

I am working on a shell script which presently connects to DB2 and needs to be changed and should connect to Informix. Can you help me converting the following systax?

The shell script looks like:

DB_INSTANCE=$1
USR=$2
PWD=$3
ENV=$4

DB2="/home/db2admin/sqllib/bin/db2"

$DB2 connect to $DB_INSTANCE user $USR using $PWD

$DB2 "call SP_PRECAL_ALERT_EXPIRY('12')"

Regards,

ceinma · #2 (**permalink**) 02-20-08, 06:35

This is possible, you can use the dbaccess or a 3rd party application (SQLCMD for example).

With dbaccess , if you want to use the user password , must create the SQL into a file and execute this file with dbaccess,

if you try execute in another way will get error -32412:

Code:

/tmp> echo " connect to \"db@myserver\" user dummy using ymmud" | dbaccess 
32412: USING clause unsupported. DB-Access will prompt you for a password.
Error in line 1
/tmp> finderr 32412
-32412  USING clause unsupported. DB-Access will prompt you for a password.

DB-Access does not support the USING password clause in a CONNECT ...
USER statement when it violates security. For example, do not type a
password on the screen where it can be seen or include it in a command
file that someone other than the user can read. To maintain security,
DB-Access prompts you to enter the password on the screen and uses echo
suppression to hide it from view.

Code:

INFORMIXSERVER=$1
USR=$2
PWD=$3
ENV=$4
DB=$5

IFMX="/opt/informix/bin/dbaccess"
echo "
connect to \"$DB@$DB_INSTANCE\" user $USR using $PWD ;
execute procedure SP_PRECAL_ALERT_EXPIRY('12') ; 
" > /tmp/sql.tmp.$$
dbaccess - /tmp/sql.tmp.$$  >/dev/null 2>&1
## to debug the SQL
## dbaccess -e - /tmp/sql.tmp.$$
rm -f /tmp/sql.tmp.$$ >/dev/null 2>&1

tanmoy.m · #3 (**permalink**) 02-20-08, 07:09

Not able to understand the following points.

1. The use of "INFORMIXSERVER=$1 " and where it is used?

2. In [ connect to \"$DB@$DB_INSTANCE\" user $USR using $PWD ; ], from where the value of DB_INSTANCE will come from ?

3. Can the same syntax be used from command line?

ceinma · #4 (**permalink**) 02-20-08, 07:34

ooops..
sorry, my bad..

replace the:
connect to \"$DB@$DB_INSTANCE\" user $USR using $PWD ;
to
connect to \"$DB@$INFORMIXSERVER\" user $USR using $PWD ;

and... answering the 3rd question, no you can't use the same syntax.
The variable INFORMIXSERVER must be set...

tanmoy.m · #5 (**permalink**) 02-20-08, 08:06

Thanks a million!!!

One more point...

IFMX="/opt/informix/bin/dbaccess"....what is the use of this line of code? Is to to execute the dbaccess.exe?

Regards

ceinma · #6 (**permalink**) 02-20-08, 08:12

Yes!
Search in your server the path where it is installed.

If you already have the Informix enviroment set, You can use:
IFMX=$INFORMIXDIR/bin/dbaccess

ceinma · #7 (**permalink**) 02-20-08, 08:15

sorry..
one more...

replace:
dbaccess - /tmp/sql.tmp.$$ >/dev/null 2>&1
to
$IFMX - /tmp/sql.tmp.$$ >/dev/null 2>&1

or the variable IFMX is not used... lol...

tanmoy.m · #8 (**permalink**) 02-20-08, 08:22

Please refer the following syntax in DB2.

tmp_alert_msg ='$db2 "create table <table-name>...in userspace1" '

Can I use '$IFMX "create table <table-name>...in userspace1" ' in informix?

IN DB2 "DB20000I" is returned when the table creation is successful. Is there anything equivalent in Informix?

ceinma · #9 (**permalink**) 02-20-08, 08:37

the equivalent when used dbaccess:

1)
echo "CREATE ...;" | dbaccess mydb

2)
dbaccess mydb <<EOL
CREATE TABLE... ;
EOL

3)
echo "CREATE TABLE...;" > myscript.sql
dbaccess mydb myscript.sql

tanmoy.m · #10 (**permalink**) 02-20-08, 10:22

I have executed the following command line argument:

echo execute procedure test_proc() | dbaccess sysmaster

1. How do i get to know the statement was executed successfully or failed? (How can I trap the Error or success code?)

ceinma · #11 (**permalink**) 02-20-08, 11:41

Just a plus...for some explanations :
http://www-1.ibm.com/support/docview...=utf-8&lang=en

About how trap erros, if you are use BASH or KSH Shell , you can use the $? variable, if $? differ 0 (zero) a error occur.

Code:

echo execute procedure test_proc() | dbaccess sysmaster
if [ $? -ne 0 ]; then
  echo "error occur!"
fi

## to more information about $?  execute on your UNIX: man ksh  OR man bash
## to more information about "[ -ne ]"  execute on your UNIX: man test

tanmoy.m · #12 (**permalink**) 02-29-08, 02:28

Hi Cesar,

Thanks for all your replies. I have one more question for you. Please help.

I am trying to call a Stored Procedure from Unix shell script and getting the following error.

254: Too many or too few host variables given.
Error in line 1
Near character position 1

------------------------------------------------------------
My Stored Procedure looks like:

CREATE PROCEDURE SP_PRECAL_ALERT_MESSAGE (
pici_rowcount int
)
RETURNING INT AS pici_rowcount;

------------------------------------------------------------
My shell script looks like:

$INFORMIXDIR/bin/isql -s $DB_INSTANCE <<!EOF
begin work;
execute procedure SP_PRECAL_ALERT_MESSAGE(?);
commit work;
!EOF

------------------------------------------------------------

ceinma · #13 (**permalink**) 02-29-08, 08:26

I guess the error is because of "?" , it's not a valid parameter.

I never used ISQL, don't know if this sintax is valid. I believed is not.

Internet Services

Web Development

Digital Marketing

Consumer Tech