# sql variable as array index

**URL:** <https://community.unix.com/t/sql-variable-as-array-index/229679>\
**Category:** UNIX for Advanced & Expert Users\
**Created:** [January 30, 2009, 5:59am UTC](https://community.unix.com/t/sql-variable-as-array-index/229679 "2009-01-30T05:59:58Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sudheer157](https://community.unix.com/letter_avatar/sudheer157/32/5_5575768a8748004e209b776fc1b2916d.png) [@sudheer157](https://community.unix.com/u/sudheer157)\
**Post date:** [January 30, 2009, 5:59am UTC](https://community.unix.com/t/sql-variable-as-array-index/229679/1 "2009-01-30T05:59:58Z")

</div>

hi folks  
i am facing problom while trying to access sql variable as array index ina unix shell script....script goes as below..

#!/bin/ksh  
MAX=3  
for elem in alpha beeta gaama  
do  
arr[$x]=$elem  
((x=x+1))  
Done

SQL\_SERVER='servername'  
/apps/sun5/utils/sqsh -S $SQL\_SERVER -U user -P pwd -b -h \<\<EOF  
set nocount on  
go  
use DBname  
go  
declare @MaxID int  
set @MaxID=0  
print $MAX  
while @MaxID \< $MAX  
begin  
print ${arr[@MaxID]}  
set @MaxID=@MaxID+1  
end  
print 'endof while'  
go  
EOF

Is the script...  
Prob is not able to use @MaxId as array subscript since it is a sql variable and array is used inside sqsh mode i.e sql prompt ...

Any way to access the unix array in side sql prompt based on index....???? Hope i made my self clear.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![fimblo](https://community.unix.com/letter_avatar/fimblo/32/5_5575768a8748004e209b776fc1b2916d.png) [@fimblo](https://community.unix.com/u/fimblo)\
**Post date:** [February 6, 2009, 5:26pm UTC](https://community.unix.com/t/sql-variable-as-array-index/229679/2 "2009-02-06T17:26:36Z")

</div>

This wont work because of scope issues. The array $arr[..] is declared in the shell, so it has to be interpreted by the shell. When you try to send it into sqsh's STDIN it won't be able to expand it since at that time the shell has no idea what value the SQL variable @foo will have.

One way of solving this (very ugly, but I have seen it in the wild) is to write some SQL code which generates some ksh code which (yes I said it was gawdugly) creates SQL code.

Don't do this. It might work but someone is going to have to maintain it. Prolly you.

Instead ask yourself why you are using an array in ksh in the first place. Could you use a file? A table in the DB? Is the data available from the inside of a SQL session?