Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Reading data from Informix tables using BASH scripts

    Posted Thu March 26, 2020 09:57 AM
    Can anyone guide me on the best way to retrieve data from informix tables into BASH script variables.

    I have experimented with dbaccess but find the data needs 'trimming' before it's usable.

    ------------------------------
    Stuart Stephens
    ------------------------------

    #Informix


  • 2.  RE: Reading data from Informix tables using BASH scripts

    Posted Thu March 26, 2020 10:16 AM
    You can use Jonathan Leffler's SQLCMD package for this. The package includes a utility called sqlserver that sits in memory connected to the database and waits for requests from the sqlclient utility which submits an SQL and echos the results to stdout, so you can:

    sqlserver -d mydatabase

    then at any time afterwards:

    TABNAME=$( sqlclient "SELECT FIRST 1 tabname FROM systables WHERE tabid > 120;"
    echo $TABNAME
    mytable2

    You can download the sqlcmd package from the IIUG Software Repository (www.iiug.org/software). It is an easy package to build and includes man pages.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.






    Original Message


  • 3.  RE: Reading data from Informix tables using BASH scripts

    Posted Thu March 26, 2020 10:18 AM
    I've done it a gazillion different ways depending on what I'm trying to get out.  If I have multiple value to retrieve, I'll dump them to a file then read the file. 

    DBDELIMITER=""
    export DBDELIMITER
    dbaccess sysmaster - 2>/dev/null <<EOF 
    unload to /tmp/databases.out 
    select name
      from sysdatabases
     where name not like 'sys%';
    EOF

     If I'm just trying to get one thing out:

    a=`echo "output to pipe cat without headings select breed from breed where id=1" | dbaccess -a bogee 2>/dev/null | grep -v ^$`

    informix$ echo $a
    All American

    Of course that's awkward to type in each time, so I use a function do_sql
    do_sql()
    {
    echo "output to pipe cat without headings" $2 | dbaccess $1 2>/dev/null | grep -v ^$  
    }

    a=`do_sql bogee "select breed from breed where id=1"`

    informix$ echo $a
    All American


    Have fun.

    j.




    Original Message


  • 4.  RE: Reading data from Informix tables using BASH scripts

    Posted Thu March 26, 2020 10:36 AM
    @Stuart Stephens
    If you want a form like csv I think it's better to use unload.
    If the unloaded file contains characters such as Enter or Tab, you may need to remove it.

    $ cat unload.sh
DBNAME=$1
TABNAME=$2
dbaccess -e ${DBNAME} <<EOF
unload to ${TABNAME}.txt select * from ${TABNAME};
EOF

$ sh unload.sh stores_demo customer

Database selected.

unload to customer.txt select * from customer;
28 row(s) unloaded.​

    ​

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 5.  RE: Reading data from Informix tables using BASH scripts

    Posted Fri March 27, 2020 08:32 AM
    In case previous answers didn't satisfy your needs/taste ...:

    I think you can use the "read" command to read a line of input (e.g. from an unload file) into several shell variables, word-by-word.
    Here, words are separated by spaces. I.e. if you have spaces in your column values, you could escape them with a '\'.

    Comsider this example:

    % cat bla.sh
    #!/bin/sh

    read x1 x2 x3

    echo "x1: ${x1}"
    echo "x2: ${x2}"
    echo "x3: ${x3}"

    exit
    %

    % cat bla.in
    Here\ we go\ again on\ Friday
    %

    % cat bla.in | bla.sh
    x1: Here we
    x2: go again
    x3: on Friday
    %

    So, using a 'traditional' Informix unload file that contains '|' as column separators,
    I would first replace all ' ' with '\ ', then all '|' with ' ', then use "read" similar to the above example.

    If you don't like a (temporary) unload file, first created with dbaccess and "UNLOAD TO ... SELECT ...",
    then you can spend additional time on creating a named pipe and using an external table for the data,
    then read the data from that pipe for further processing. But I think, this will involve some synchronization
    between pipe reader (your shell script) and pipe writer (dbaccess using the external table): the reader
    must start reading before the writer starts writing ...

    Regards, Martin

    --
    Martin Fuerderer
    Informix Development Germany
    HCL Technologies Germany GmbH
    Frankfurter Ring 17
    80807 Munich, Germany
    http://www.hcltech.com/de

    ::DISCLAIMER::
    The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.



    Original Message


Related Content