using SELECT sql statement in shell script

Shell Programming and Scripting
1

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 db-server1 -utest TEST

this works fine and populates the database accordingly

however, I now want to be able to issue a SELECT statement (populating some variables with the results) so that i can continue to use them in the script

for example

if i was to issue

sql="SELECT hostname, model FROM main WHERE serial =$SN"
echo $sql |/usr/sfw/bin/mysql -h db-server1 -utest TEST

and somehow the 'hostname' value I retrieve will be put into say a variable called HN and the 'model' value I retrieve goes into a variable called MD

Does anybody know how I get these query results objects into usable variables like this ??

any help on this would be greatly appreciated as I am completely flummoxed

2

this will get the values in the respective variable..
NOTE:Its better to set
set head off;
set feedback off;
set pages 0;

host=`echo "SELECT hostname FROM main WHERE serial =$SN"|/usr/sfw/bin/mysql -h db-server1 -utest TEST`
model=`echo "SELECT model FROM main WHERE serial =$SN"|/usr/sfw/bin/mysql -h db-server1 -utest TEST`
3

thanks vidyadhar85

Unfortunately, I have hundreds of values ill be pulling down from the db, so technically i would have to have a line of code for each one, which isnt ideal but it works and thats great, thankyou

incidentally, issuing the "set head off" and the others, is that something I have to do on the server itself as a general setting , or do i integrate it into the query somehow ?

i tried

# echo "set head off" | /usr/sfw/bin/mysql -h db-server1 -utest TEST
ERROR 1193 at line 1: Unknown system variable 'head'

which didnt work

4

then its better to take those hundreds of values into a flat file and then use them however you want

5

thankyou , yes ill send it all out to a file (using -E to format the output vertically) then use some logic to pull those values into the script as variables

thank you for your help