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:
Posts
