script variable within a sql query

Shell Programming and Scripting
1

I created a script to read a csv file with four columns. The script also saved values of each col in a arry.
In the script, i connected to db try to run a query to pull out data baisc on the values from the csv file.

select Num from tableName
where Sec_Num in ('${isin[@]}') /*isin[@] is an arry which its data are from extracted from the csv file.

could you please tell me what wrong with this query. When I run the script,it is unable to pull out any data from db. Do you have any suggestion?

Thanks You!

2

First off, a variable in single quotes won't get expanded (in fact this is what single quotes are for - protect variables from expansion). So probably your SQL script wont see the content of $isin, but the string "${isin[@]}", which, of course, won't be found in the database. To correct this write your SQL statement into an intermediate file and then execute this file. Like here:

exec 3>/tmp/tmpSQL.sql
print -u3 "select Num from tableName"
print -u3 "where Sec_Num in (\'${isin[@]}\')"
exec 3>&-

db2 -tvf /tmp/tmpSQL.sql
# rm -f /tmp/tmpSQL.sql

Once the script is running you can delete the intermediate file immediately, but for debugging purposes it is good to have a look into into it, which is why i put a comment in front of "rm".

Second (possible) point: I dont know all too much about SQL, but i think a list of values has to be comma-separated. If this is not the case, than this point is moot, but in case this is true the variable expansion "${isin[@]}" would be wrong: the shell expands the array to a space-separated list instead of a comma-separated one. Expand the list prior to using it and put commata in between:

typeset -i iCnt=2
typeset    chList="${isin[1]}"

while [ $iCnt -le ${#isin[@]} ] ; do
     chList="${chList}, ${isin[$iCnt]}"
     (( iCnt += 1 ))
done

You would have to use the variable "$chList" instead of the array in the first code snippet of course.

Hope this helps.

bakunin