18

I need a bash script that can retrieve MySQL data from a remote data base. Actually I have that done, but what I need it to do now is loop through the records somehow and pass a variable to another bash file. Here's my MySQL call:

mysql -X -u $MyUSER -p$MyPASS -h$MyHOST -D$MyDB -e'SELECT `theme_name`, `guid` FROM `themes` WHERE `theme_purchased`="1" AND `theme_compiled`='0';' > themes.xml
download_themes.sh

It exports the data into an xml file called theme.xml right now, I was just trying to figure out some way to loop through the data. I am trying to avoid PHP and perl and just trying to use bash. Thanks in advance.

Brian Leishman
  • 8,155
  • 11
  • 57
  • 93
  • 4
    Is there some specific reason you want to avoid scripting languages? You won't be able to parse xml properly in bash. You might even have problems unescaping the output. Why not go the easy way and use php / perl / python / lua / java / tcl / javascript / ... or seriously anything apart from bash which simply won't be able to do this properly for many outputs? – viraptor May 07 '11 at 01:01
  • You don't know if there is a way to output as an array and loop through that? I've Googled this can I couldn't find anything. – Brian Leishman May 07 '11 at 01:04

4 Answers4

52

something like:

mysql -e "SELECT `theme_name`, `guid` FROM `themes` WHERE `theme_purchased`='1' AND `theme_compiled`='0'" | while read theme_name guid; do
    # use $theme_name and $guid variables
    echo "theme: $theme_name, guid: $guid"
done

in short: the mysql command outputs record separated by '\n' and fields separated by '\t' when the output is a pipe. the read command reads a line, splits in fields, and puts each on a variable.

if your data has spaces in the fields, you get some problems with the default read splitting. there are some ways around it; but if you're only reading two fields and one of them shouldn't have any spaces (like the guid), then you can put the 'dangerous' field at the end, and read will put everything 'extra' in the last variable.

like this:

mysql -e "SELECT `guid` `theme_name`, FROM `themes` WHERE `theme_purchased`='1' AND `theme_compiled`='0'" | while read guid theme_name; do
    # use $theme_name and $guid variables
    echo "theme: $theme_name, guid: $guid"
done
Javier
  • 60,510
  • 8
  • 78
  • 126
  • It seemed to have worked the first time, but now it gives me this: /winterboard_theme_builder/get_themes.sh: line 9: syntax error near unexpected token `done' /winterboard_theme_builder/get_themes.sh: line 9: `done' – Brian Leishman May 07 '11 at 03:25
  • Actually the do was on the wrong line, but then I get a different error. It says /winterboard_theme_builder/get_themes.sh: line 7: read: `theme_name,': not a valid identifier – Brian Leishman May 07 '11 at 10:41
  • oh, right. no commas between `read` arguments, and a semicolon before `do`. fixed – Javier May 07 '11 at 12:27
  • 1
    I have a problem with this approach. When you have in your loop results when the column cell have 2 word , then the command assign part of first variable to 1 and other part to 2 . Let's say if $theme_name="Theme Name" and $guid="12" in db then this command will echo theme:Theme guid:Name 12 . Any clue how to solve that???? theme: LalaPala, guid: 44.5,40.18,44.52,40.17 theme: San, guid: Francisco   -122.58,37.81,-122.33,37.71 theme: Edirne, guid:   26.51,41.69,26.64,41.64 – MR.GEWA Jul 27 '13 at 17:31
  • as I said in the answer, if only one of the columns can have spaces on it, just put it at the end. the `read` command assigns all 'remaining' results to the last variable. For anything more complex, you could in theory get it working with the exact amount of quoting; but better to use real encodings (XML, CSV, record-style, etc), or any other language that doesn't try to parse every string you handle. – Javier Jul 29 '13 at 18:37
  • You won't be able to set global variables inside that loop – davide Sep 08 '13 at 23:04
9

Rather than outputting XML, may I suggest you simply use the SELECT INTO OUTFILE syntax or mysql --batch --raw to output tab-separated values. You then have much easier access through bash to the rest of the Unix toolchain, like cut and awk to retrieve the fields you need and reuse them with Bash. No other scripting language is necessary and you needn't mess with XML.

mysql --batch --raw -u $MyUSER -p$MyPASS -h$MyHOST -D$MyDB -e'SELECT `theme_name`, `guid` FROM `themes` WHERE `theme_purchased`="1" AND `theme_compiled`='0';' \
| awk '{print "theme: " $1  " guid: " $2}'
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • Love this solution, it should be the accepted answer imho. Much cleaner, no loop, let's me easily configure my output with `awk print`. Thanks! – pgr Jun 24 '18 at 11:24
7

The accepted answer does not work when spaces are in the output. It is an easy fix (IFS=$'\t' -- Note the $ -- it is weird): 


>mysql ... -BNr -e "SELECT 'funny man', 'wonderful' UNION SELECT 'no_space', 'I love spaces';" | while IFS=$'\t' read theme_name guid; do echo "theme: $theme_name guid: $guid"; done
theme: funny man guid: wonderful
theme: no_space guid: I love spaces

You will, of course, want to substitute your own query.

CSTobey
  • 1,491
  • 13
  • 9
2

Pipe '|' to while is dangerous, for the changes inside the loop happen in another subprocess and would not take effect in the current script.

By the way, I hate to turn to the external file solution.

I suggest to use "Process Substitute".

while read field1 field2 field3
do
done < <(mysql -NB -e "$sql")
#     ^
#   space needed
GZ92
  • 151
  • 1
  • 10