using SELECT sql statement in shell script

hcclnoodles · #1 (**permalink**) 06-17-2009

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

Code:

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

Code:

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

vidyadhar85 · #2 (**permalink**) 06-17-2009

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

Code:

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`

hcclnoodles · #3 (**permalink**) 06-17-2009

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

Code:

# 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

vidyadhar85 · #4 (**permalink**) 06-17-2009

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

hcclnoodles · #5 (**permalink**) 06-18-2009

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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
::select statement return value with correct field size::	ryanW	Shell Programming and Scripting	10	04-23-2009 02:38 AM
Oracle Select IN statement	benefactr	UNIX and Linux Applications	1	03-26-2009 04:15 PM
Supress ' quotes in a select statement inside Shell Script	mahabunta	Shell Programming and Scripting	1	12-14-2006 06:29 PM
Want to use the output of Select statement in Unix script	akhilgoel9	Windows & DOS: Issues & Discussions	4	05-27-2005 09:52 AM
Pipe SQL select statement results to script	houtakker	UNIX for Dummies Questions & Answers	6	10-31-2003 01:20 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by