Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2008
    Posts
    7

    Unanswered: Connecting to Informix from shell script

    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,
    Last edited by tanmoy.m; 02-20-08 at 04:07.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311

    Cool

    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Feb 2008
    Posts
    7
    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?

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Feb 2008
    Posts
    7
    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

  6. #6
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  7. #7
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  8. #8
    Join Date
    Feb 2008
    Posts
    7
    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?

  9. #9
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  10. #10
    Join Date
    Feb 2008
    Posts
    7
    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?)

  11. #11
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  12. #12
    Join Date
    Feb 2008
    Posts
    7

    Question

    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

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

  13. #13
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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