By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,691 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,691 IT Pros & Developers. It's quick & easy.

Extracting Field data and putting each field into a shell variable?

P: n/a
I have forgotten a lot about SQL and would like to be (gently) reminded how
to do this:
In a MySQL query on the database (one table with 15 variable length fields,
I want to put each field into a Bash variable so that I can handle each
field as an entity.

The query I have is something like this:
mysql -e "use $database; select field1, field2, field3..., from Table1 where
fieldN like '%something%';"

can I / How do I do something like:
mysql -e "use $database; select field1,..., var1=field1, var2=field2,....;"

do
stuff with var1, var2...,
done

I ask because SQL is subtle and quick to anger and I'm in a bit of a hurry.

Thanks!
--
If you wrestle in the mud with a pig.
you both get dirty, and the pig likes it.

-- Dave Dawson
Jun 19 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
David Dawson wrote:
In a MySQL query on the database (one table with 15 variable length fields,
I want to put each field into a Bash variable so that I can handle each
field as an entity.


I'd use mysql --batch to output the columns tab-separated, and then use
the shell builtin command "read" to assign several variables:

mysql --batch -e 'select col1, col2, col3 from...' | read col1 col2 col3
echo "$col1, $col2, $col3"

If your data contain whitespace, it might confuse the above method. You
might benefit from using the "--vertical" option of the mysql client
tool, to output the SQL columns one per line, instead of tab-separated.

mysql --vertical -e 'select col1, col2, col3 from...' | {
read col1
read col2
read col3
}

Note that the shell can group commands in the current shell by using
curly braces {}, or in a subshell by using parens (). Variables
assigned values in a subshell evaporate when the subshell ends.

See docs at
http://dev.mysql.com/doc/refman/5.0/...d-options.html

Regards,
Bill K.
Jun 19 '06 #2

P: n/a
Bill Karwin wrote:
David Dawson wrote: Note that the shell can group commands in the current shell by using
curly braces {}, or in a subshell by using parens (). Variables
assigned values in a subshell evaporate when the subshell ends.

See docs at
http://dev.mysql.com/doc/refman/5.0/...d-options.html

Regards,
Bill K.

Thanks a lot!
I had intended to re-write this application in Perl, towards which I wrote a
small script to handle this very query with the cpan DBI package and the
Mysql class, but this give me a neat alternative to explore
..--
If you wrestle in the mud with a pig.
you both get dirty, and the pig likes it.

-- Dave Dawson
Jun 20 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.