How to connect to dbaccess in ksh shell

March 2, 2011 at 09:38:25
Specs: Windows XP
Hi,

I'm starting with ksh and I'm a bit lost.
I'm working on an ERP that runs on Oracle or Informix.
I often need to update these databases with a lot of sql scripts.

I wrote a small shell that concatenate these files into a single script that connects to the Oracle database that I determine, and then run the script.

Here the shell (which can be improved, I'm sure):

echo 'User:'
read USER
echo 'Pass:'
read PASS
echo 'DATABASE SID:'
read SID
LIST=`ls -1 *ora.sql`
for i in $LIST
do
echo $i
cat $LIST >oracle.sql
done

sqlplus $USER/$PASS@$SID @oracle.sql

I'd like to do the same thing to update my Informix database, but I do not know what commands to use to log in dbaccess and run the script.

If anyone can help me, it would be nice.

Thank you,
Wilfried.

And sorry for my English.


See More: How to connect to dbaccess in ksh shell

Report •

#1
March 2, 2011 at 10:36:46
Informix works differently from oracle in that it doesn't require a user and password as arguments to the informix sql interface - dbaccess or isql.

The user must be added to the dbaccess and granted the correct permissions. If your SQL is contained in a file.sql, you can probably do this:

# UNTESTED
dbaccess -e <database name> < file.sql

But I choose to do it differently. I embed the sql within a Unix here document. Here is an example:

http://www.computing.net/answers/un...


Report •

#2
March 2, 2011 at 12:04:44
Hi Nails,

Thanks for your quick answer.

I tried dbaccess -e database < file.sql and it don't run.
MKS return this error :
ERROR: Could not initialize the security subsystem. Please ensure that this account has the necessary privileges and ensure INFORMIXSERVER value exists in the registry and environment.

Usually, to launch a dbaccess session, I run this *.cmd :
@echo off
set DBMONEY=,
set DBDATE=DMY4/
set INFORMIXDIR=C:\delta\informix
set INFORMIXSERVER=ol_wilfriedmp
set ONCONFIG=ONCONFIG.ol_wilfriedmp
set PATH=C:\delta\informix\bin;%PATH%
set CLASSPATH=%INFORMIXDIR%\extend\krakatoa\krakatoa.jar;%INFORMIXDIR%\extend\krakatoa\jdbc.jar;%CLASSPATH%
set DBTEMP=C:\delta\informix\infxtmp
set CLIENT_LOCALE=EN_US.CP1252
set DB_LOCALE=EN_US.8859-1
set SERVER_LOCALE=EN_US.CP1252
set DBLANG=EN_US.CP1252
mode con codepage select=1252

And I prompt dbaccess in the command window and I am connected to my local server ol_wilfriedmp with all my dbs.
Maybe I can invoke this batch to start the connection and after I put the command line dbaccess -e ...

Pending, I'm trying to understand your other post.
I hope to succeed and I will inform you of the result.

Thanks,
Wilfried.


Report •

#3
March 2, 2011 at 12:45:48
I see you are using MKS tool kit under windows. I can not quarantee my Unix script will work. I have no experience with Informix running on windows.


Report •

Related Solutions

#4
March 3, 2011 at 01:04:33
Hi Nails,

I don't have finished and tested the script, but I'm sure that the environment variables are not retrieved by MKS.
I must paste Export command lines, as in your other post, to include these variables.

I try that, and maybe I'm lucky.

Thanks for your help,
Wilfried.


Report •

#5
March 3, 2011 at 10:07:41
Hi,

For the moment, I not succeed to write the shell, but I writed a cmd that make the same thing.

@echo off

rem CONCATENE all ifx migration scripts in the directory in one IfxMig.sql single script
cat *ifx.sql >>IfxMig.sql
echo "Concatenate succeed."

rem Load the environment variables and ask the user for local informations
set DBMONEY=,
set DBDATE=DMY4/
set INFORMIXDIR=C:\delta\informix
echo "Enter INFORMIXSERVER (ol_laptopname)"
set /p INFORMIXSERVER=
set ONCONFIG=ONCONFIG.%INFORMIXSERVER%
set PATH=C:\delta\informix\bin;%PATH%
set CLASSPATH=%INFORMIXDIR%\extend\krakatoa\krakatoa.jar;%INFORMIXDIR%\extend\krakatoa\jdbc.jar;%CLASSPATH%
set DBTEMP=C:\delta\informix\infxtmp
set CLIENT_LOCALE=EN_US.CP1252
set DB_LOCALE=EN_US.8859-1
set SERVER_LOCALE=EN_US.CP1252
set DBLANG=EN_US.CP1252
mode con codepage select=1252

rem Ask user to prompt the DBNAME to migrate
echo "Enter DB Name"
set /p DBName=

rem Connect to dbaccess and load the script and print the result to IfxMig.txt
dbaccess %DBNAME% <IfxMig.sql 2>IfxMig.txt
pause

Headstrong like I am, I persists and I'm certain that I will succeed in write this shell... That's how we learn, isn't it ?

If somebody have a suggest to translate this batch in sh language, know that I'm interested.

Bye.


Report •

Ask Question