Reading data from Informix tables using BASH scripts

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

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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

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 $aAll 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 $aAll American Have fun. j.

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 customerDatabase selected. unload to customer.txt select * from customer;28 row(s) unloaded. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

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/shread 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.shx1: 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 -- ::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.