Using bash with MySQL

[carmel_ny]

On Tue, 27 Oct 2009 16:17:55 +0000
Matthew Seaman <m.seaman at infracaninophile.co.uk> replied:

>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=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?

Matthew, unfortunately, that is not the problem. However, you post
pointed me in the right direction.

Notice this line: (should all be on one line)

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

I am saving the output of the MySQL search in an array. Unfortunately,
the array is assuming that each space in the returned search is a new
element. I have not found out a way to prevent this. If you have any
suggestions, I would appreciate them.

I have tried putting: IFS=$( echo ) before the 'DECLARE_STATEMENTS'
call; however, that produces this error:

./scamp-sql: line 128: syntax error near unexpected token `)'
./scamp-sql: line 128: `DECLARE_STATEMENTS=$(mysql ${COM_LINE} -i -e"use ${DB}; SELECT defaults FROM ${table} WHERE '1';"))'

I know the principal is correct because I tried this code snippet:

IFS=$( echo )
a="1 2 3"
b=($a)
echo ${b[0]}

1 2 3

CONCAT would not benefit me either since the 'space' would still exist
in the returned search query. I might have to devise some hack to
combine the three elements into one. Fortunately, there are three
parts to every element. Unfortunately, there are a lot of them.

-- 
Jerry
gesbbb at yahoo.com

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

Sweater, n.:
A garment worn by a child when its mother feels chilly.