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

[Chris F.A. Johnson]

On Thu, 29 Oct 2009, Greg Wooledge wrote:

> 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

    Or:

arr+=( "$REPLY" )

>   done < <(your mysql command)

   Or, in bash4:

mapfile -t arr < <(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.

   Besides the methods already shown:

printf -v IFS "\n"


-- 
   Chris F.A. Johnson, webmaster         <http://woodbine-gerrard.com>
   ===================================================================
   Author:
   Shell Scripting Recipes: A Problem-Solution Approach (2005, Apress)
   Pro Bash Programming: Scripting the GNU/Linux Shell (2009, Apress)