I'm hoping someone here can help me, because I'm stuck. I've read all I could find online, in forums and in the help docs, but can't seem to get command line execution for Informix working correctly.

Let me prefix this by saying I'm new to Informix, but work with Oracle & Sql Server daily. I work with a software company that is currently working on supporting Informix in our software package. All of our databases are in a Windows server enviroment. For the scope of this testing I'm using the 90-day trial version of IDS 10.0

During our update/release cycles we require our customers to run SQL batch files to update their databases which we provide. Our goal is to simplify the process as much as possible - so for Oracle we just have them run 1 command which executes all of the SQL script files, ie:
>sqlplus <username>/<password>@<database> @run_updates.sql

run_updates.sql is a series of statements like "@o060815a.sql", which are ran in turn (simplified, we have a bunch of error handling in place).

For Sql Server it's a batch file that sets enviroment vars, and then uses osql to execute each of the SQL script files.


Which brings me to Informix. Deploying 30-70 SQL script files and requiring their DBA to execute them is not an option. I need to be able to provide a single batch file that can run all of the SQL script files (add tables/fields, delete them, update data, etc).

I'm in the beginning test phase of this process and am stuck already. I've tried making a single batch file for this process:

run_updates1.bat
------------------
c:
cd \database\informix
ol_rjw
cd \database\sql files
\database\informix\bin\dbaccess - -

but it doesn't process the lines after ol_rjw I set the environment. So I tried setting the env manually, and then running the script

run_updates2.bat
------------------
c:
cd \database\sql files
\database\informix\bin\dbaccess v66 connfile.sql


v66 would be the database, connfile.sql contains my connection info:

CONNECT USER <username> USING <password>


But I just can't get the connection to work. And this is just the first step of it, what I really want to happen is have a series of SQL script files be executed.

So - with this long winded explination.. can anyone help me?
Thanks in advance,
Rich

Please try manually open dbaccess and try to select the database.
Is it works?

Must be "Database connected"

Yes, it connects. I set the enviroment, cd \bin, run dbaccess, connect to database, and I can view the tables that I added there.

Interactively I have no problems, I just cannot find a way to do it via batch files / scripts.

append call prior ol_rjw, watch below:

run_updates1.bat
------------------
c:
cd \database\informix
call ol_rjw
cd \database\sql files
\database\informix\bin\dbaccess - -


In evirionment file ol_rjw also add this:
set DBACCNOIGN=1

it will stop execution of sql code if any error will occur, because by default code will continue to execute with errors - it may produce dangerous "spaghetti".

here is what I use

set up the .bat script


set INFORMIXDIR=D:\informix
set INFORMIXSERVER=servername
set ONCONFIG=ONCONFIG.servername
set PATH=D:\informix\bin;%PATH%;
set INFORMIXSQLHOSTS=\\TECSYS
set DBTEMP=D:\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
set PATH=C:\PerlScheduler\Scripts;%PATH%;
mode con codepage select=1252
calc_act_hrs.sh


and here is the script, in your case you can put all of your sql's in to one script

#!/bin/ksh
#This script will calculate actual hours
d:\\informix\\bin\\dbaccess cdi < run_cal_act_hrs.sql >> d:\\informix\logs\calc_act_hrs.log

hope this helps

> append call prior ol_rjw

Thank you, that did it.

I have the connection working properly now I think. In a file named connfile.sql I add my connection string:

CONNECT to 'v66@ol_rjw' user 'xxxx' using 'yyyy';

If I put a command on the next line it is executed like I want. However it's not practical to require our customers to modify all of the SQL files to handle this.

Is there a way from dbaccess command line to execute a sql file?

For example, if I do:
>dbaccess - -

and enter my connection info, I can run sql statements. But I'd like to execute the commands that are in a file named test.sql.

I thought that using the dbaccess commands that you can use to invote menu options ( -qu ) might work, but can't figure it out. I've been pouring over the dbaccess help but it seems really vague on some of these questions.


Artemka - if I read you correctly, what you are saying is that I should be able to create a bat file that sets all of the variables, and then add all of the scripts I want to execute at the end?

ie.
calc_act_hrs.sh
test.sh
somethingelse.sh

Or do you mean to put all of the SQL statements we have into 1 file (very unpractical - some of our scripts are quite large).

Thanks for all your help.

I always did the name of the script in the .bat file and then
multiple dbaccess commands in the .sh script

But you can try puting more then one .sh in the .bat file might work