Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: dbaccess command line script

    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

  2. #2
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17
    Please try manually open dbaccess and try to select the database.
    Is it works?

    Must be "Database connected"

  3. #3
    Join Date
    Aug 2006
    Posts
    3
    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.

  4. #4
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17
    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".
    Last edited by intarsplienis; 08-25-06 at 05:19.

  5. #5
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    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

  6. #6
    Join Date
    Aug 2006
    Posts
    3
    > 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.

  7. #7
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •