Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    21

    Unanswered: Using dbaccess inside if-then statement.

    Hey all,

    First off, here's what I'm using...

    IBM Informix Dynamic Server Version 11.70.FC1X3
    Red Hat Enterprise Linux Server release 5.6 (Tikanga)

    What I'm doing is creating a shell script to generate an ad-hoc report to distribute via email.

    Now for my issue. I want to know if I can nest the dbaccess command inside an if-then statement.

    Here's what my code currently looks like. I know my 'if' statement works because when I remove the entire dbaccess command / query and throw in some copy and echo commands, those work just fine.

    if [ "date +%m%d" = "$VARFILE2" ]
    then
    dbaccess atb 2>> $LOGFILE <<ENDSQL
    UNLOAD TO '$RPTDIR/report1.tmp' DELIMITER ''
    SELECT ' ' || sol_id || ' ' || sol_date || ' ' || sol_trans
    FROM informix.database
    ORDER BY sol_id, sol_date, sol_trans;

    ENDSQL
    fi


    Running the dbaccess command /query by itself in a separate part of the shell script also works fine, so I know the query works.

    If I run the entire command as a one-liner from a prompt...

    linuxserver> if [ "date +%m%d" = "date +%m%d" ]; then dbaccess atb 2>> $LOGFILE <<ENDSQL UNLOAD TO '$RPTDIR/report1.tmp' DELIMITER '' SELECT ' ' || sol_id || ' ' || sol_date || ' ' || sol_trans FROM informix.database ORDER BY sol_store, sol_date, sol_trans; ENDSQL; done

    I just get dropped to the this prompt...

    >

    I'm missing something and I bet it's simple.

  2. #2
    Join Date
    May 2008
    Posts
    21
    Figured it out...

    This one liner works from a command line. I just had to relocate where I use dbaccess when nesting it inside an if-then statement. I was also able to get test code to work inside my shell script as well. I'll post the final code once I'm done.

    if [ "date +%m%d" = "date +%m%d" ]; then echo 'SELECT DISTINCT sol_id FROM informix.database WHERE sol_date = TODAY - 1' | dbaccess atb; fi

  3. #3
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    When you use the <<ENDSQL syntax, the ENDSQL statement has to be on a line by itself, starting in column 1.

  4. #4
    Join Date
    May 2008
    Posts
    21
    Here's the final code. Works like a charm. I had to change some single quotes to double quotes for the delim usage and where I'm appending extra spaces.

    if [ "`date +%m%d`" = "$VARFILE2" ]
    then
    echo 'UNLOAD TO '$RPTDIR/report1.tmp' DELIMITER ""
    SELECT " " || sol_id || " " || sol_date || " " || sol_trans || " "
    FROM informix.database
    ORDER BY sol_id, sol_date, sol_trans' | dbaccess atb
    fi

Posting Permissions

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