Re: OT: Getting MySQL fields with embedded spaces into array

[Greg Wooledge]

On Thu, Oct 29, 2009 at 11:49:11AM -0400, Gerard wrote:
> Are you sure? Using: IFS=$(echo) seems to set IFS to a newline here.

imadev:~$ IFS=$(echo)
imadev:~$ printf "%s" "$IFS" | od -t x1
0000000
imadev:~$ printf "\n" | od -t x1
0000000    a
0000001
imadev:~$ echo ${#IFS}
0
imadev:~$ unset IFS

Also, this section of the manual is quite clear:

      Bash performs the expansion by executing command and replacing the
      command substitution with the standard output of the command, with any
      trailing newlines deleted.

> I got some great ideas from your page. However, I have not been able
> to figure out how to save the results of the MySQL search, one that
> might include spaces in the data, and inset it into an array without
> creating a temp file and then using read to put it into an array.

Well, there are two different issues here (that I can see -- possibly more).
The first is, "How do I get the results of a data stream into an array?"
This can be done with a temp file or a FIFO, or with what bash calls
"process substitution" which is basically a FIFO that bash creates for
you (although the actual implementation varies across platforms).  It
looks like this:

  while read -r; do
    arr[i++]=$REPLY
  done < <(your mysql command)

This assumes a newline delimiter between array elements (lines of the
data stream).  It's on BashFAQ/005 though you may have missed it.  I'll
take a look at the wording and see if it's unclear, or needs additional
pointers to other pages.

The other issue is "I have database fields that can contain whitespace,
and I want to read them in, while still retaining knowledge of where each
field begins and ends."  This one is much more insidious.

In the general case, if your SQL select statement returns two "varchar"
fields that can contain arbitrary characters, there is no way to know
where the first field ends and the second one begins.

The solution to this is:

 * Only perform SQL select statements that return data of fixed size,
   or a single isolated data field; or
 * Move to a language that can call the database API directly.

> //snippet//
> 
> ## Connect to the SQL server and store the contents of the query in an array
> 
> SIGS=$(mysql ${COM_LINE} -e"use ${DB}; SELECT sig from ${table} WHERE 
> sig_file='0';")

That is not an array.  It's just a string (scalar) variable.

> ## Set IFS = line feed or else the array will not load correctly
> IFS=$(echo)

This actually sets IFS to an empty string, not a newline.

> ## Place the elements into a file
> printf "%s\n" ${SIGS} > "Sigs.tmp"

This is dangerous because you didn't turn of filename completion (globbing)
with "set -f" beforehand.  If one of the words of $SIGS is a *, bash will
expand it to all the filenames in the current directory before handing it
to printf.

Or at least, that's what it would do without IFS having been set to an
empty string.  With IFS set but empty, the value of $SIGS will not be
word-split at all.  So filename completion would be performed if the
*entire* value of $SIGS can be interpreted as a glob that matches one or
more filenames in $PWD.  This is less likely, of course, but it's still
something of a concern.

All your command is really doing (most likely) is printing the contents
of the $SIGS variable, plus a newline, to the Sigs.tmp file.  If IFS
were actually set to a newline, it would be doing something quite
different (each line of $SIGS, subject to filename completions, would be
printed to a separate line [printed followed by a newline]).  And with
the default IFS (unset, or set to "space, tab, newline") it would print
each word of $SIGS, subject to filename completions, on a separate line.
That's what I assumed you wanted (modulo the filename completions),
but now looking over things again, I'm not so sure.

If what you actually wanted was "print the contents of $SIGS to a file,
unmodified, but with a newline on the end", then you should have quoted
"$SIGS" thus:

  printf "%s\n" "$SIGS" > Sigs.tmp

> ## Restore the old IFS setting
> IFS=${OLD_IFS}
> 
> ## Place the elements into an array & clean any variables
> unset i SIGS_ARRAY
> while read -r; do SIGS_ARRAY[i++]=$REPLY; done < "Sigs.tmp"

If you got more than one array element by doing this, then it means
you had newline characters in the actual output of the mysql command.
Newlines were not added by your code.

> The array is now loaded and works in my script. I would love to
> accomplish this without a temporary file; however, I have not found a
> bullet proof method of doing it.

The process substitution would work for that.  Or if you wanted to keep
the SIGS string variable, you can use a "here string":

 while read -r; do ...; done <<< "$SIGS"

The main reason this isn't used in BashFAQ/005 is because capturing a
command substitution in a variable removes the trailing newlines from the
command's output, which may not be a change you'd want.  It's better for
me to stick with techniques that won't alter the data stream in transit,
just in case.  If you don't care about the trailing newlines in your
particular script, then this becomes an option for you.