There are multiple ways to do that depending on the number of rows or columns which you are trying to return. Although you can do this sort of thing with a shell script, if you have a lot of data to pull out you would be much better off using a stored procedure or a language such as perl rather than a shell script. Anyway, here are some examples that should get you started. If you are going to do a bunch of work like this I'd recommend you buying a shell scripting book. My personal favorite is "Korn Shell Programming Tutorial" by Barry Rosenberg . This is by far the best beginning/intermediate shell programming book on the market. It has been in print for almost 20 years and it is still the best one.
One row/one column is trivial
day=`nzsql -Aqt -vON_ERROR_STOP=1 -c "select current_date"`
Multiple rows, one column, small number of rows
tables=`nzsql -Aqt -vON_ERROR_STOP=1 -c "select table_name from _v_odbc_tables2"`
for table in $tables; do
echo $table
done
Multiple columns one row is a bit more work
#!/bin/ksh
nzsql -Aqt -F^ -v ON_ERROR_STOP=1 <<-END > test.txt
select
extract(year from current_date),
extract(month from current_date),
extract(day from current_date);
END
if [[ $? != 0 ]]; then
cat test.txt
print "query failed!"
exit 1
fi
IFS="^"
read year month day < test.txt
echo "year = $year"
echo "month = $month"
echo "day = $day"
Or many columns, many rows
#!/bin/ksh
nzsql -Aqt -F^ -v ON_ERROR_STOP=1 <<-END > test.txt
select
table_name, column_name, type_name
from
_v_odbc_columns2
order by
table_name,
ordinal_position;
END
if [[ $? != 0 ]]; then
cat test.txt
print "query failed!"
exit 1
fi
# left jusitfy and pad to 20 charcters
typeset -L20 cname
typeset -L20 dtype
# The IFS variable tells the shell what delimiter(s) to use to parse values out of
# a string. I set it to ^ here to match the delimiter I set with the -F option of nzsql
IFS="^"
while read tname cname dtype; do
if [[ $last_tname != $tname ]]; then
print "${tname}"
last_tname=${tname}
fi
print " ${cname} ${dtype}"
done < test.txt
