Selecting multiple rows into "a shell script varia
A user on IDS 10 knew how to capture a single-row query result into a shell variable (isql piped through awk and backticks) but asked how to handle a SELECT returning many rows. Answers: pipe the isql/awk output into a ksh/bash "while read VAR; do ... done" loop (the poster also used a here-document variant). Art Kagel cautioned that parsing isql/dbaccess screen output breaks when lines exceed 80 characters and wrap, and recommended using UNLOAD TO a delimited file via dbaccess, then looping over that file and splitting fields — avoiding header/blank-line filtering too. The poster accepted this.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 10
I know how to select ONE value from the database into a shell script variable,
for example:
TABNAME=`echo "select tabname from systables where tabid = 1'" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
But how do I do this for a select statement that returns multiple rows ? For
example if the select statement above was:
Select tabname from systables where tabid < 100
Dirk
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Hi Dirk
In ksh or bash you can use a while loop like this to process the rows one at a
time, for example:
echo "select tabname from systables where tabid < 100" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while read TABNAME
do
[ some processing for each $TABNAME ]
done
I don't know what the syntax is for csh as I don't use it.
HTH. Regards
Stuart
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Dirk
Cornel....
Sent: 28 March 2012 11:18
To: ids@iiug.org
Subject: Selecting multiple rows into "a shell script v.... [26581]
IDS 10
I know how to select ONE value from the database into a shell script variable,
for example:
TABNAME=`echo "select tabname from systables where tabid = 1'" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
But how do I do this for a select statement that returns multiple rows ? For
example if the select statement above was:
Select tabname from systables where tabid < 100
Dirk
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
echo 'unload to somefile.unl delimiter " " select one, two, three from
sometable;' | dbaccess mydatabase -
read varone vartwo varthree <somefile.unl
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.
On Wed, Mar 28, 2012 at 6:17 AM, Dirk Cornel.... <moolma_dc@mtn.co.za>wrote:
> IDS 10
>
> I know how to select ONE value from the database into a shell script
> variable,
> for example:
>
> TABNAME=`echo "select tabname from systables where tabid = 1'" | isql eppix
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
>
> But how do I do this for a select statement that returns multiple rows ?
> For
> example if the select statement above was:
>
> Select tabname from systables where tabid < 100>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3ba959d044be04bc4b59ae
Apologies, the syntax seems to have got a bit corrupted by the mail program so
having another go.
echo "select tabname from systables where tabid < 100" \\\\
| isql eppix 2>/dev/null \\\\
| awk 'NF>0 && $0 !~ /key_value/ {print $1}' \\\\
| while read TABNAME
do
[ some processing for each $TABNAME ]
done
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Stuart
Brooks
Sent: 28 March 2012 11:45
To: ids@iiug.org
Subject: RE: Selecting multiple rows into "a shell scri.... [26582]
Hi Dirk
In ksh or bash you can use a while loop like this to process the rows one at a
time, for example:
echo "select tabname from systables where tabid < 100" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while read
2>TABNAME
do
[ some processing for each $TABNAME ]
done
I don't know what the syntax is for csh as I don't use it.
HTH. Regards
Stuart
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Dirk
Cornel....
Sent: 28 March 2012 11:18
To: ids@iiug.org
Subject: Selecting multiple rows into "a shell script v.... [26581]
IDS 10
I know how to select ONE value from the database into a shell script variable,
for example:
TABNAME=`echo "select tabname from systables where tabid = 1'" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
But how do I do this for a select statement that returns multiple rows ? For
example if the select statement above was:
Select tabname from systables where tabid < 100
Dirk
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
D'oh!
Basically pipe your output from your select into "while read TABNAME", then
use "do" to start the loop and "done" to close the loop.
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Stuart
Brooks
Sent: 28 March 2012 11:51
To: ids@iiug.org
Subject: RE: Selecting multiple rows into "a shell scri.... [26584]
Apologies, the syntax seems to have got a bit corrupted by the mail program so
having another go.
echo "select tabname from systables where tabid < 100" \\\\
| isql eppix 2>/dev/null \\\\
| awk 'NF>0 && $0 !~ /key_value/ {print $1}' \\\\ while read TABNAME
do
[ some processing for each $TABNAME ]
done
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Stuart
Brooks
Sent: 28 March 2012 11:45
To: ids@iiug.org
Subject: RE: Selecting multiple rows into "a shell scri.... [26582]
Hi Dirk
In ksh or bash you can use a while loop like this to process the rows one at a
time, for example:
echo "select tabname from systables where tabid < 100" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while read
2>TABNAME
do
[ some processing for each $TABNAME ]
done
I don't know what the syntax is for csh as I don't use it.
HTH. Regards
Stuart
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Dirk
Cornel....
Sent: 28 March 2012 11:18
To: ids@iiug.org
Subject: Selecting multiple rows into "a shell script v.... [26581]
IDS 10
I know how to select ONE value from the database into a shell script variable,
for example:
TABNAME=`echo "select tabname from systables where tabid = 1'" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
But how do I do this for a select statement that returns multiple rows ? For
example if the select statement above was:
Select tabname from systables where tabid < 100
Dirk
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Oops, Stuart is right. I misread your post. I thought you were asking
about multiple values in a single row. However, my method can be combined
with Stuart's to read multiple rows with multiple values in a row:
# Write out the data with a field delimiter that the for loop won't
recognize
echo "unload to /tmp/somefile.$$.unl select one, two, three from
sometable;" | dbaccess mydatabase -
for line in $( cat /tmp/somefile.$$.unl ); do
# Read a line at a time from the unload file
echo $line | awk -F\\\\| '{print $1, $2, $3;}' >/tmp/line$$; # Break
it up into fields
read varone vartwo varthree </tmp/line$$ ; # Read
the fields into variables
echo "varone=$varone vartwo=$vartwo varthree=$varthree";
done
rm /tmp/somefile.$$.unl /tmp/line$$
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.
On Wed, Mar 28, 2012 at 6:45 AM, Stuart Brooks
<stuart.brooks@ardenta.com>wrote:
> Hi Dirk
>
> In ksh or bash you can use a while loop like this to process the rows one
> at a
> time, for example:
>
> echo "select tabname from systables where tabid < 100" | isql eppix
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while read
> TABNAME
> do
>
> [ some processing for each $TABNAME ]
> done
>
> I don't know what the syntax is for csh as I don't use it.
>
> HTH. Regards
>
> Stuart
>
> ---
> Ardenta Ltd is a company registered in England and Wales. Registered
> number:
> 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Dirk
> Cornel....
> Sent: 28 March 2012 11:18
> To: ids@iiug.org
> Subject: Selecting multiple rows into "a shell script v.... [26581]
>
> IDS 10
>
> I know how to select ONE value from the database into a shell script
> variable,
> for example:
>
> TABNAME=`echo "select tabname from systables where tabid = 1'" | isql eppix
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
>
> But how do I do this for a select statement that returns multiple rows ?
> For
> example if the select statement above was:
>
> Select tabname from systables where tabid < 100>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340a85942f0404bc4bc95b
Thanks Stuart, you put me on the right track :-) I Googled about "while read",
and came up with:
!/bin/ksh
while read line
do
[ some processing for each $line ]
done << EOF
`echo "select tabname from systables where tabid < 100" | isql eppix
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $2}'`
EOF
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Stuart Brooks
> Sent: Wednesday, 28 March 2012 12:54 PM
> To: ids@iiug.org
> Subject: RE: Selecting multiple rows into "a shell scri.... [26585]
>
> D'oh!
>
> Basically pipe your output from your select into "while read TABNAME",
> then
> use "do" to start the loop and "done" to close the loop.
>
> ---
> Ardenta Ltd is a company registered in England and Wales. Registered
> number:
> 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Stuart
> Brooks
> Sent: 28 March 2012 11:51
> To: ids@iiug.org
> Subject: RE: Selecting multiple rows into "a shell scri.... [26584]
>
> Apologies, the syntax seems to have got a bit corrupted by the mail
> program so
> having another go.
>
> echo "select tabname from systables where tabid < 100" \\\\
> | isql eppix 2>/dev/null \\\\
> | awk 'NF>0 && $0 !~ /key_value/ {print $1}' \\\\ while read TABNAME
> do
>
> [ some processing for each $TABNAME ]
>
> done
>
> ---
> Ardenta Ltd is a company registered in England and Wales. Registered
> number:
> 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Stuart
> Brooks
> Sent: 28 March 2012 11:45
> To: ids@iiug.org
> Subject: RE: Selecting multiple rows into "a shell scri.... [26582]
>
> Hi Dirk
>
> In ksh or bash you can use a while loop like this to process the rows
> one at a
> time, for example:
>
> echo "select tabname from systables where tabid < 100" | isql eppix
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while read
> 2>TABNAME
> do
>
> [ some processing for each $TABNAME ]
> done
>
> I don't know what the syntax is for csh as I don't use it.
>
> HTH. Regards
>
> Stuart
>
> ---
> Ardenta Ltd is a company registered in England and Wales. Registered
> number:
> 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dirk
> Cornel....
> Sent: 28 March 2012 11:18
> To: ids@iiug.org
> Subject: Selecting multiple rows into "a shell script v.... [26581]
>
> IDS 10
>
> I know how to select ONE value from the database into a shell script
> variable,
> for example:
>
> TABNAME=`echo "select tabname from systables where tabid = 1'" | isql
> eppix
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
>
> But how do I do this for a select statement that returns multiple rows
> ? For
> example if the select statement above was:
>
> Select tabname from systables where tabid < 100>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
OR
echo "select owner, tabname from systables where tabid > 99" | isql dbname
2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1,$2}' | {
while read myline;do
echo $myline ........... (now you can do anything with $myline)
done }
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Stuart Brooks
> Sent: Wednesday, 28 March 2012 12:54 PM
> To: ids@iiug.org
> Subject: RE: Selecting multiple rows into "a shell scri.... [26585]
>
> D'oh!
>
> Basically pipe your output from your select into "while read TABNAME",
> then
> use "do" to start the loop and "done" to close the loop.
>
> ---
> Ardenta Ltd is a company registered in England and Wales. Registered
> number:
> 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Stuart
> Brooks
> Sent: 28 March 2012 11:51
> To: ids@iiug.org
> Subject: RE: Selecting multiple rows into "a shell scri.... [26584]
>
> Apologies, the syntax seems to have got a bit corrupted by the mail
> program so
> having another go.
>
> echo "select tabname from systables where tabid < 100" \\\\
> | isql eppix 2>/dev/null \\\\
> | awk 'NF>0 && $0 !~ /key_value/ {print $1}' \\\\ while read TABNAME
> do
>
> [ some processing for each $TABNAME ]
>
> done
>
> ---
> Ardenta Ltd is a company registered in England and Wales. Registered
> number:
> 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Stuart
> Brooks
> Sent: 28 March 2012 11:45
> To: ids@iiug.org
> Subject: RE: Selecting multiple rows into "a shell scri.... [26582]
>
> Hi Dirk
>
> In ksh or bash you can use a while loop like this to process the rows
> one at a
> time, for example:
>
> echo "select tabname from systables where tabid < 100" | isql eppix
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while read
> 2>TABNAME
> do
>
> [ some processing for each $TABNAME ]
> done
>
> I don't know what the syntax is for csh as I don't use it.
>
> HTH. Regards
>
> Stuart
>
> ---
> Ardenta Ltd is a company registered in England and Wales. Registered
> number:
> 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> Middlesex, TW16 6RT.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dirk
> Cornel....
> Sent: 28 March 2012 11:18
> To: ids@iiug.org
> Subject: Selecting multiple rows into "a shell script v.... [26581]
>
> IDS 10
>
> I know how to select ONE value from the database into a shell script
> variable,
> for example:
>
> TABNAME=`echo "select tabname from systables where tabid = 1'" | isql
> eppix
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
>
> But how do I do this for a select statement that returns multiple rows
> ? For
> example if the select statement above was:
>
> Select tabname from systables where tabid < 100>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
The problem with this and any scheme that depends on the normal screen
output of isql or dbaccess is that if the output is longer than 80
characters (that includes varchar/lvarchar/nvarchar columns that are longer
than 80 bytes max) the output will shift from single line output to
multi-line output which is much harder to parse in shell. Using the UNLOAD
feature is more consistent and you don't have to work to ignore empty lines
and header lines either.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.
On Wed, Mar 28, 2012 at 9:40 AM, Dirk Cornel.... <moolma_dc@mtn.co.za>wrote:
> OR
>
> echo "select owner, tabname from systables where tabid > 99" | isql dbname
> 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1,$2}' | {
> while read myline;do
> echo $myline ........... (now you can do anything with $myline)
> done }
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Stuart Brooks
> > Sent: Wednesday, 28 March 2012 12:54 PM
> > To: ids@iiug.org
> > Subject: RE: Selecting multiple rows into "a shell scri.... [26585]
> >
> > D'oh!
> >
> > Basically pipe your output from your select into "while read TABNAME",
> > then
> > use "do" to start the loop and "done" to close the loop.
> >
> > ---
> > Ardenta Ltd is a company registered in England and Wales. Registered
> > number:
> > 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> > Middlesex, TW16 6RT.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Stuart
> > Brooks
> > Sent: 28 March 2012 11:51
> > To: ids@iiug.org
> > Subject: RE: Selecting multiple rows into "a shell scri.... [26584]
> >
> > Apologies, the syntax seems to have got a bit corrupted by the mail
> > program so
> > having another go.
> >
> > echo "select tabname from systables where tabid < 100" \\\\
> > | isql eppix 2>/dev/null \\\\
> > | awk 'NF>0 && $0 !~ /key_value/ {print $1}' \\\\ while read TABNAME
> > do
> >
> > [ some processing for each $TABNAME ]
> >
> > done
> >
> > ---
> > Ardenta Ltd is a company registered in England and Wales. Registered
> > number:
> > 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> > Middlesex, TW16 6RT.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Stuart
> > Brooks
> > Sent: 28 March 2012 11:45
> > To: ids@iiug.org
> > Subject: RE: Selecting multiple rows into "a shell scri.... [26582]
> >
> > Hi Dirk
> >
> > In ksh or bash you can use a while loop like this to process the rows
> > one at a
> > time, for example:
> >
> > echo "select tabname from systables where tabid < 100" | isql eppix
> > 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while read
> > 2>TABNAME
> > do
> >
> > [ some processing for each $TABNAME ]
> > done
> >
> > I don't know what the syntax is for csh as I don't use it.
> >
> > HTH. Regards
> >
> > Stuart
> >
> > ---
> > Ardenta Ltd is a company registered in England and Wales. Registered
> > number:
> > 4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
> > Middlesex, TW16 6RT.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Dirk
> > Cornel....
> > Sent: 28 March 2012 11:18
> > To: ids@iiug.org
> > Subject: Selecting multiple rows into "a shell script v.... [26581]
> >
> > IDS 10
> >
> > I know how to select ONE value from the database into a shell script
> > variable,
> > for example:
> >
> > TABNAME=`echo "select tabname from systables where tabid = 1'" | isql
> > eppix
> > 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
> >
> > But how do I do this for a select statement that returns multiple rows
> > ? For
> > example if the select statement above was:
> >
> > Select tabname from systables where tabid < 100> >
> > Dirk
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8dbeae6be404bc4df52e
Awesome. Thank you Art.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Wednesday, 28 March 2012 03:56 PM
> To: ids@iiug.org
> Subject: Re: Selecting multiple rows into "a shell scri.... [26589]
>
> The problem with this and any scheme that depends on the normal screen
> output of isql or dbaccess is that if the output is longer than 80
> characters (that includes varchar/lvarchar/nvarchar columns that are
> longer
> than 80 bytes max) the output will shift from single line output to
> multi-line output which is much harder to parse in shell. Using the
> UNLOAD
> feature is more consistent and you don't have to work to ignore empty
> lines
> and header lines either.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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.
> On Wed, Mar 28, 2012 at 9:40 AM, Dirk Cornel....
> <moolma_dc@mtn.co.za>wrote:
>
> > OR
> >
> > echo "select owner, tabname from systables where tabid > 99" | isql
> dbname
> > 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1,$2}' | {
> > while read myline;do
> > echo $myline ........... (now you can do anything with $myline)
> > done }
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > Stuart Brooks
> > > Sent: Wednesday, 28 March 2012 12:54 PM
> > > To: ids@iiug.org
> > > Subject: RE: Selecting multiple rows into "a shell scri.... [26585]
> > >
> > > D'oh!
> > >
> > > Basically pipe your output from your select into "while read
> TABNAME",
> > > then
> > > use "do" to start the loop and "done" to close the loop.
> > >
> > > ---
> > > Ardenta Ltd is a company registered in England and Wales.
> Registered
> > > number:
> > > 4181041. Registered office: Saxon House, Downside, Sunbury on
> Thames,
> > > Middlesex, TW16 6RT.
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > Stuart
> > > Brooks
> > > Sent: 28 March 2012 11:51
> > > To: ids@iiug.org
> > > Subject: RE: Selecting multiple rows into "a shell scri.... [26584]
> > >
> > > Apologies, the syntax seems to have got a bit corrupted by the mail
> > > program so
> > > having another go.
> > >
> > > echo "select tabname from systables where tabid < 100" \\\\
> > > | isql eppix 2>/dev/null \\\\
> > > | awk 'NF>0 && $0 !~ /key_value/ {print $1}' \\\\ while read TABNAME
> > > do
> > >
> > > [ some processing for each $TABNAME ]
> > >
> > > done
> > >
> > > ---
> > > Ardenta Ltd is a company registered in England and Wales.
> Registered
> > > number:
> > > 4181041. Registered office: Saxon House, Downside, Sunbury on
> Thames,
> > > Middlesex, TW16 6RT.
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > Stuart
> > > Brooks
> > > Sent: 28 March 2012 11:45
> > > To: ids@iiug.org
> > > Subject: RE: Selecting multiple rows into "a shell scri.... [26582]
> > >
> > > Hi Dirk
> > >
> > > In ksh or bash you can use a while loop like this to process the
> rows
> > > one at a
> > > time, for example:
> > >
> > > echo "select tabname from systables where tabid < 100" | isql eppix
> > > 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}' | while
> read
> > > 2>TABNAME
> > > do
> > >
> > > [ some processing for each $TABNAME ]
> > > done
> > >
> > > I don't know what the syntax is for csh as I don't use it.
> > >
> > > HTH. Regards
> > >
> > > Stuart
> > >
> > > ---
> > > Ardenta Ltd is a company registered in England and Wales.
> Registered
> > > number:
> > > 4181041. Registered office: Saxon House, Downside, Sunbury on
> Thames,
> > > Middlesex, TW16 6RT.
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > Dirk
> > > Cornel....
> > > Sent: 28 March 2012 11:18
> > > To: ids@iiug.org
> > > Subject: Selecting multiple rows into "a shell script v.... [26581]
> > >
> > > IDS 10
> > >
> > > I know how to select ONE value from the database into a shell
> script
> > > variable,
> > > for example:
> > >
> > > TABNAME=`echo "select tabname from systables where tabid = 1'" |
> isql
> > > eppix
> > > 2>/dev/null | awk 'NF>0 && $0 !~ /key_value/ {print $1}'`
> > >
> > > But how do I do this for a select statement that returns multiple
> rows
> > > ? For
> > > example if the select statement above was:
> > >
> > > Select tabname from systables where tabid < 100> > >
> > > Dirk
> > >
> > > NOTE: This e-mail message is subject to the MTN Group disclaimer
> see
> > > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > >
> > >
> > >
> ***********************************************************************
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> ***********************************************************************
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> ***********************************************************************
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> ***********************************************************************
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6e8dbeae6be404bc4df52e
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx