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:
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
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,
mode con codepage select=1252
and here is the script, in your case you can put all of your sql's in to one script
#This script will calculate actual hours
d:\\informix\\bin\\dbaccess cdi < run_cal_act_hrs.sql >> d:\\informix\logs\calc_act_hrs.log
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?
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).