Using bash with MySQL

Matthew Seaman m.seaman at
Tue Oct 27 16:18:09 UTC 2009

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.
> 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=                  # Server to connect to
> NO_COLUMN_NAME="--skip-column-names"
> 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
> done
> This output is produced:
> declare
> -a
> 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

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

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

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?



Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                  Flat 3
PGP:     Ramsgate
                                                  Kent, CT11 9PW

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 259 bytes
Desc: OpenPGP digital signature
Url :

More information about the freebsd-questions mailing list