Visit Our UNIX and Linux User Community


for each value in an array, execute select statement


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting for each value in an array, execute select statement
# 1  
Old 08-17-2010
for each value in an array, execute select statement

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.
Code:
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?
Moderator's Comments:
Mod Comment Use code tags please, ty.

Last edited by vbe; 08-17-2010 at 08:58 AM..
# 2  
Old 08-17-2010
Unindent your "EOF`" to the left margin - the spaces infront of it are confusing it.
# 3  
Old 08-17-2010
I unidented "EOF'". I removed all the spaces. Still I get the same result. Any other suggestion please?
# 4  
Old 08-17-2010
You could try creating a shell script called "xsql" with something like:
Code:
#!/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  
Old 08-17-2010
Thanks citalor for your suggestion. I applied your suggestion like following.

Code:
#!/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?

Last edited by Scott; 08-17-2010 at 10:48 AM.. Reason: Code tags, please...
# 6  
Old 08-17-2010
Try changing xsql to:
Code:
#!/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 ;-)
# 7  
Old 08-18-2010
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.

Previous Thread | Next Thread
Test Your Knowledge in Computers #857
Difficulty: Easy
React cannot be used as a base in the development of single-page web or mobile applications.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing passing and executing select statement in loop

Hi, i want to do the following: Grep the following kind of strings for the 15digit ID which is stored in filename1: "14:06:51.396 INFO BMCREMEDYSD INPUT-ACTION Failed to retrieve Remedy Incident Modification record: 000000000039047 org.apache.axis2.AxisFault: Read timed out - complete... (9 Replies)
Discussion started by: Khushbu
9 Replies

2. Shell Programming and Scripting

SQLPLUS command with more than 1 select statement

Hi all, I'm using below code processId=`sqlplus -s ${sysuser}/${syspwd} <<CHK_PROCESS whenever sqlerror exit sql.sqlcode; set head off feedback off echo off pages 0 SELECT PROCESS_ID FROM LSHADMIN.DATA_DOMAIN WHERE DOMAIN_NAME = '${tabname}' ... (8 Replies)
Discussion started by: Pratiksha Mehra
8 Replies

3. Shell Programming and Scripting

Problem with select statement

Hi I have run out of ideas as to why this select doesn't work in a script I am writing. The script sources a file of common functions and I am trying to use a select statement within one of the functions - PS3="Try? " select X in CONT EXIT; do if ] ... (4 Replies)
Discussion started by: steadyonabix
4 Replies

4. Shell Programming and Scripting

How to execute a no of SELECT COUNT(*) statements using a loop

HI Unix Gurus, I have a number of SELECT count(*) statements in an input file and I want to execute it using a shell script but one by one using loop in script.... How can I do this..... (7 Replies)
Discussion started by: ustechie
7 Replies

5. Shell Programming and Scripting

Select variable within a if statement

i want to select a variable created and use it in a if statement, but not getting the desired results LINE='device for 0101a01: lpd://172.25.41.111:515' prt=`echo $LINE | awk '{print $3 }' | cut -c 1-7` echo $prt My if statement to select just what i want.. IFS=$":" while read prt... (11 Replies)
Discussion started by: ggoliath
11 Replies

6. Shell Programming and Scripting

How can i assign an select statement into a variable?

I am trying to assign an select statement into a variable. Can someone hel me with this. example : a='select * from dual' echo $a should give me select * from dual But this is not working. I trying with \ before * and quotes too. (1 Reply)
Discussion started by: rdhanek
1 Replies

7. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

8. Shell Programming and Scripting

::select statement return value with correct field size::

Hi Everyone, I am facing a problem regarding the select from sybase, the return with the incorrect size. For example, field is NAME(20). After i selected from sybase, the result is nicky. after i assign it to another declaration variable, it will be in actual name "nicky" , what i need... (10 Replies)
Discussion started by: ryanW
10 Replies

9. UNIX and Linux Applications

Oracle Select IN statement

If I recall, when I used informix I could do a sql statement like: SELECT Value from Table WHERE ID in (100,200,300); How do I do this in Oracle? I believe I am using Oracle 10 if that matters. Thanks. (1 Reply)
Discussion started by: benefactr
1 Replies

10. Windows & DOS: Issues & Discussions

Want to use the output of Select statement in Unix script

Hi, I have a UNIX script which calls SQL Select statement: Now i want to use the output of that select statement within my UNIX script so as to call different shell script depending upon the output of the select statement. Can anyone help me in this regard. TIA Akhil Goel (4 Replies)
Discussion started by: akhilgoel9
4 Replies

Featured Tech Videos