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

[David Dawson]

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

[Bill Karwin]

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.

[David Dawson]

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