Re: Using bash with MySQL

[Matthew Seaman]

carmel_ny wrote:

I am in the process of writting a script that will use MySQL as a back
end. For the most part, I have gotten things to work correctly. I am
having one problem though.

Advertising

Assume a data base:

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

This output is produced:

declare
-a
MSRBL_LIST

Obviously, I want the output on one line for each field. I have tried
enclosing the variables with both single and double quote marks;
however, that does not work. Fields that do not contain spaces are
displayed correctly.

Obviously, I am doing something really stupid here. I hope someone can
assist me. I probably should ask this on the MySQL forum; however, I
was hoping that someone here might be able to supply a remedy.

This loop is where it all goes horribly wrong:

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

In Posix shell, the intended functionality would be more usually coded like
this:

IFS=$( echo ) for ds in $DECLARE_STATEMENTS ; do
  echo $ds
done

where $DECLARE_STATEMENTS is split on any characters present in $IFS --
the input field separators, here set to be just a newline character.  (You
don't have to use echo to do that; you can just put a literal newline between
single quotes, but it's hard to tell all the different forms of whitespace
apart if you're reading code snippets in an e-mail...)

I suspect similar IFS trickery would work with bash, but I'm not familiar
with the array syntax stuff it uses.  /bin/sh is perfectly capable for shell
programming and positively svelte when compared to bash and it's on every
FreeBSD machine ever installed, so why bother with anything else?

        Cheers,

        Matthew

--
Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                 Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey     Ramsgate
                                                 Kent, CT11 9PW

signature.asc
Description: OpenPGP digital signature