Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    1

    Run SQL Query on Informix database from Unix-Shell

    Hi,

    I've an Client System ( HP-Unix ) and I've Informix Client Software installed on that.
    The Informix Client is successfully setup with Informix Database Server.

    I'm looking for a way to run an SQL Query on the Database server from the Client system.

    Actually I want it in Unix Shell Script or something like that .... that make me able to run it from somewhere else ...


    How it is possible ?

    Regards,
    Last edited by Anti_Evil; 06-19-07 at 05:33.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    You can
    - write a esql/c program... but i never do that...
    - Use a dbaccess (in a shell program)
    some examples:
    echo "select * from table_X" | dbaccess myDB ;
    dbaccess myDB Myselect.sql ;
    dbaccess myDB <<EOF
    select * from table_X
    EOF

    Read the dbaccess manual...
    http://www-306.ibm.com/software/data...y/ids_100.html
    ________________________________________
    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
    Sep 2002
    Location
    Valencia, California USA
    Posts
    7
    I wrote from korn shell the following:

    echo "database mydb; select * from mytable" | isql -qr -

  4. #4
    Join Date
    Oct 2006
    Posts
    2
    What is the syntax for passing parameters from a command line dbaccess
    command running a sql file -- dbaccess (database name) file.sql (parameter
    values)

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Running queries on Unix databases in Unix

    Now retired, I spent 25 years developing and administering Informix databases on various Unix platforms. My favoured approach was to use Informix/4GL to write a program, then compile it.
    The general format of a Unix command was

    fglgo <programname>.4gi <list of arguments> | <another process>
    or
    fglgo <programname>.4gi <list of arguments> >> <output file or screen>

    This gives you the chance to check stuff and display an error message. You could write a general purpose SQL query handler and provide the actual query as a string argument.

  6. #6
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, I often use a shellscript that passes a query as a parameter on the commandline. The shellscript is:
    Code:
    #!/bin/bash
    
    OUT="/dev/null"
    
    while getopts ":e" OPTION; do
        case $OPTION in
        e)  OUT="/dev/$(ps -p $PPID | awk 'NR==2{print $2}')";;
        *)  echo "$(basename $0): invalid option ${1}"
            echo "Use \`$(basename $0)' -e for showing error output"
            exit 1
        esac
    done
    
    shift $((OPTIND -1))
    
    if [ $# -ne 1 ]; then
        echo "$(basename $0): invalid number of arguments"
        echo "Usage: $(basename $0) [-e] \"SQLSTATEMENT\""
        exit 1
    fi
    {
    dbaccess databasename <<SQL
    $*
    SQL
    } 2>"$OUT"
    As you can see it's written for a (Suse) Linux system and possibly some shell commands in it are different from the HP-UX equivalents, but I'm not familiar with the latter.
    Anyway for this you need to have dbaccess installed on the client which I believe is when Ifx client software is installed. In the script you must replace the databasename with the actual databasename@hostname and you can use it on the commandline like:
    Code:
    db -e "SELECT colname FROM tablename WHERE code = 'ABCD'"
    with error output to the terminal and like:
    Code:
    db "SELECT colname FROM tablename WHERE code = \"ABCD\""
    without error output and with escaped double quotes within the SQL string.

    Maybe it's useful, I imitated it from the DB2 commandline utility with the same name. Regards,
    Hans

    BTW. Within the SQL string you can use regular shell variables as parameters when invoking from another script or in a for-loop on the command line like:
    Code:
    #!/usr/bin/ksh
    db "SELECT colname FROM ${1} WHERE code = '${2}'"
    Last edited by Tyveleyn; 07-06-09 at 18:20.

Posting Permissions

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