Friday, July 22, 2011

Passing Parameters To A MYSQL Query Inside A Shell Script

Passing Parameter To A MySQL query Inside A Shell Script.

From time to time, I found myself need to run some adhoc queries to pull data. I found that it was more convenience to create a shell script that could accept the query files as variable and can pass variables to the query.

/* script:get_customer_record.sql */

Select c_id, c_first_name,c_last_name, c_address,
……
,last_modified_date
from customer
where last_modified_date >=@start_date and last_modified_date <= @end_date;

@start_date and @end_date are variables to be passed at run time.

Here is the wrapper shell script:

#!/bin/bash

## script name; mysql_script_runner.sh
## wrapper script to execute mysql script with variables

ARGS=4

if [ $# -ne "$ARGS" ]
then 

 echo "you passed $# parameters"
 echo "Usage: `basename $0` sql_script_file start_date end_date output_file"

exit 
fi 
sql_script=$1 
start_date=$2 
end_date=$3 
output_file=$4 

#run mysql query with paramenters 

/usr/bin/mysql –uuser_id -ppassword –h mysql-host -A -e "set @start_date=${start_date}; set @end_date=${end_date}; source ${sql_script};" >${data_file};

exit

# end of script.


To execute the script from command line, I can do something like this:

 # mysql_script_runner.sh get_customer_record.sql ‘2011-06-01’ ‘2011-06-12 23:59:59’ cust_rec.dat

I can also set it up to run as a cron job with some small changes.

References:


Related Note:

3 comments:

Anonymous said...

Hi Buddy..

May I know how can I use @Var in LOAD DATA LOCAL INFILE command?

my @Var =/home/rc-user/AutoJob/Sample.txt

Sample.txt contains all the data which I want to load.

Please note that (@Var) DOES NOT HAVE (') - Single Quotes at the beginning or end.

when I tried,
LOAD DATA LOCAL INFILE @Var, it gave me below error message:

ERROR 1064 (42000) at line 2 in file: '/home/rc-user/AutoJob/generic_load.sql': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@Var
INTO TABLE demo_auto_load
FIELDS TERMINATED BY ';' LINES TERMINAT' at line 1
very well

Unknown said...

one downside thing of this technique that it can not be used with DROP table and DROP user statements.
User variables can appear only where expressions are allowed, not where constants or literal identifiers must be provided.

check: https://www.safaribooksonline.com/library/view/mysql-cookbook-2nd/059652708X/ch01s27.html

was stuck with errors when i tried to pass arguments from bash script to a .sql script to be used in drop user (i get the username to drop from bash script). the only solution i found was to do whatever it is required to be done in bash script then call the .sql script.

Jan said...

I needed this for creating new (readonly)users which has the same limitation unfortunately. I ended up making a shell script which generates .sql scripts by replacing some template variables by using sed.
${MYSQL_ROOT_HOST} is a environment var set by Docker:

#!/bin/bash
declare -a arr=("username1" "username2")

## now loop through the above array
for i in "${arr[@]}"
do
echo " Username: $i"
sed "s/{username}/$i/g; s/{host}/${MYSQL_ROOT_HOST}/g" /tmp/create_users_for_database.sql > /tmp/1.sql
mysql -h "localhost" -u "root" "-ptest" "mysql" < /tmp/1.sql
done


My template create_users_for_database.sql script:
create user '{username}_read'@'{host}' identified by '{username}_read';
GRANT SELECT ON *.* TO '{username}_read'@'{host}';