OT: Getting MySQL fields with embedded spaces into array

[Gerard Seibert-2]

I apologize for asking this here; however, I cannot seem to locate an
answer anywhere else.

I am writing a script that will use MySQL for a back end. I access MySQL
and store the results in arrays like this:

//snippet//

database: MyDataBase
table: MyTable
field: defaults

Now, I have populated the 'defaults' fields with the declare
statements that I will use in the script. They are entered similar to
this:
 
        declare -a MSRBL_LIST

Now, I issue this from my bash script:

SQL_USER=user # MySQL user
SQL_PASSWORD=secret # MySQL password
DB=MyDataBase # MySQL data base name
HOST=127.0.0.1                  # Server to connect to
NO_COLUMN_NAME="--skip-column-names"
COM_LINE="-u${SQL_USER} -p${SQL_PASSWORD} -h ${HOST} ${NO_COLUMN_NAME}"

table=MyTable

DECLARE_STATEMENTS=($(mysql ${COM_LINE} -i -e"use ${DB}; SELECT defaults FROM "${table}" WHERE 1;"))

for (( i=0;i<${#DECLARE_STATEMENTS[*]};i++)); do
echo  ${DECLARE_STATEMENTS[i]}
done

//end snippet//
 
This output is produced:

declare
-a
MSRBL_LIST

Obviously, that is not what I want. I have tried setting:

        IFS=$( echo )

Prior to the invocation of the "DECLARE_STATEMENTS" statement; however,
that produced another error message and MySQL would not start.

I have been exploring different hacks to make this work. Perhaps
writing to a file and then using 'READ' to put the data into an array.
I was hoping that someone might have a working solution that I could
try. This problem only happens when the data stored in a MySQL field
contains embedded spaces or tabs. At least that is all that I am aware
of.

Thanks!

--
Gerard
gerard@...

|::::=======
|::::=======
|===========
|===========
|

It isn't easy being the parent of a six-year-old.  However, it's a
pretty small price to pay for having somebody around the house who
understands computers.

[Greg Wooledge]

On Wed, Oct 28, 2009 at 06:04:00AM -0400, Gerard wrote:
> COM_LINE="-u${SQL_USER} -p${SQL_PASSWORD} -h ${HOST} ${NO_COLUMN_NAME}"
> table=MyTable
> DECLARE_STATEMENTS=($(mysql ${COM_LINE} -i -e"use ${DB}; SELECT defaults FROM "${table}" WHERE 1;"))

You're populating an array with each word (not each line) of the output
of your mysql command.

> This output is produced:
>
> declare
> -a
> MSRBL_LIST
>
> Obviously, that is not what I want.

(Not obvious to us.  Maybe so to you. ;-) )

> I have tried setting:
> IFS=$( echo )

$() removes all trailing newlines from the output of the command that
it executes.  You're setting IFS to an empty string.  If you want to
set IFS to a newline, use this:

IFS=$'\n'

Or this:

IFS='
'

> I have been exploring different hacks to make this work. Perhaps
> writing to a file and then using 'READ' to put the data into an array.

'read' is the most flexible way, though you don't need a temporary file
to do this.  I have some more documentation on this approach here:
http://mywiki.wooledge.org/BashFAQ/005

[Gerard Seibert-2]

On Wed, 28 Oct 2009 08:38:07 -0400
Greg Wooledge <wooledg@...> replied:

> > I have tried setting:
> > IFS=$( echo )  
>
> $() removes all trailing newlines from the output of the command that
> it executes.  You're setting IFS to an empty string.  If you want to
> set IFS to a newline, use this:
>
> IFS=$'\n'
>
> Or this:
>
> IFS='
> '

Are you sure? Using: IFS=$(echo) seems to set IFS to a newline here.
 
> > I have been exploring different hacks to make this work. Perhaps
> > writing to a file and then using 'READ' to put the data into an
> > array.  
>
> 'read' is the most flexible way, though you don't need a temporary
> file to do this.  I have some more documentation on this approach
> here: http://mywiki.wooledge.org/BashFAQ/005

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.

Using a few suggestions from your page, I created this code snippet. It
works as I expect it to.

//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';")

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

## Place the elements into a file
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"

//end snippet//

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.

--
Gerard
gerard@...

|::::=======
|::::=======
|===========
|===========
|

Q:How much does it cost to ride the Unibus?
A:2 bits.

[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.

[Chris F.A. Johnson-6]

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)