for each value in an array, execute select statement

Shell Programming and Scripting
1

Hello All,

I am new to shell scripting. I am working on Solaris O/S, bash script and sybase programming.

I want to loop through multiple values in an array and for each value, I
want to select a row from the database.

following is the code written for it.

output="loop.csv"          #Output file
elements=${#currency_pair[@]} # total number of rows in an array
for((i=0;i<$elements;i++)); do
        echo ${currency_pair[${i}]} #debug statement
        results=`isql -U$DBLOGIN -P$DBPASSWD -D$DBNAME -b -w 2000 -s',' -o$output <<EOF
        select Code,BidRate,AskRate,from FXPoint where   RelativeDateType = 1 and
        (Code = '${currency_pair[${i}]}')
        go
        EOF`
        echo "Results is stored as"
        echo $results
done

but somehow my resultset is empty. I am not getting any error message or exception. it is just empty. Can anybody help me with the solution for this? Is there any other efficient method to do the same?

2

Unindent your "EOF`" to the left margin - the spaces infront of it are confusing it.

3

I unidented "EOF'". I removed all the spaces. Still I get the same result. Any other suggestion please?

4

You could try creating a shell script called "xsql" with something like:

#!/usr/bin/ksh
echo $* > /tmp/foo
cat - >> /tmp/foo

And replace "isql" for "xsql" and check the output in /tmp/foo to make sure the arguments and stdin look correct.

5

Thanks citalor for your suggestion. I applied your suggestion like following.

#!/usr/bin/ksh
echo $* > /tmp/foo
cat - >> /tmp/foo

currency_pair=("${currency_pair[@]}" "RVL EUR-CHF") #array element 1
currency_pair=("${currency_pair[@]}" "RVL EUR-JPY") #array element 2

output="loop.csv"
elements=${#currency_pair[@]}
for((i=0;i<$elements;i++)); do
echo ${currency_pair[${i}]}
results=`xsql -U$DBLOGIN -P$DBPASSWD -D$DBNAME -b -w 2000 -s',' -o$output <<EOF
select Code,BidRate,AskRate,from FXPoint where   RelativeDateType = 1 and
(Code = '${currency_pair[${i}]}')
go
EOF`
echo "Results is stored as"
echo $results
done

But it did not work. When I execute this script. Nothing happens. The screen is blank and even the echo messages are not printed. I have to abort the script using CTRL+z.

Can something else work? Is there anything wrong with the logic?

6

Try changing xsql to:

#!/usr/bin/ksh
echo logging args >&2
echo $* > /tmp/foo
echo logging stdin >&2
cat - >> /tmp/foo
echo finished >&2

I think you will find its getting to the "logging stdin" and then sits there, meaning the <<EOF thing isnt working...just a hunch :wink:

7

Hi,

I am getting the output on screen like following

logging args
logging stdin

but nothing after that. I have to abort the script as before.

We are not even reaching to the problem and its cause with this. Is there some other workaround to solve this.

Please suggest.

8

Yep...this means that the xsql script is stopping on the "cat - >> /tmp/foo".
This means that the xsql script isnt getting any stdin....ie the "here document" isnt working.
Perhaps you could change your script to something like:

        results=`echo "
        	select Code,BidRate,AskRate,from FXPoint where RelativeDateType = 1 and (Code = '${currency_pair[${i}]}')
        	go" | isql -U$DBLOGIN -P$DBPASSWD -D$DBNAME -b -w 2000 -s',' -o$output`

And see if that helps...

9

Hi Citaylor,

i applied your suggestion and got to know that there was silly mistake in the select statement. There is , before from which was causing all the problems.
Thanks for your suggestion :slight_smile: