Alessandro Blasetti Alessandro Blasetti - 1 year ago 62
MySQL Question

Executing SQL queries using a file in special format

I am trying to write a script that reads lines from a file containing:

  • a filename of an SQL script, and

  • some SQL queries on the same line

and launches a query.

There is a
file in the following format:

query.sql; set @var:='prod';

is a file with SQL queries, e.g.:

select *
from db
where system=@var

I am trying to write a Bash script that executes the queries like this:

mysql -uroot -proot -e
"set @var:='prod';
select *
from db
where system=@var;"

Here is what I have tried so far:

while read line;
do app=$(echo $line | awk '{for (i=2; i <= NF; i++) printf FS$i; print NL }';
cat `echo $line | awk -F\; '{print $1}'`; echo ";");
mysql -uroot -proot -e "`echo $app`"
done < list.txt

But I am having an SQL error, because the shell does not escape well the
character in the

Debugging my code I obtain:

$echo $app
$set @var:='prod'; select a list.txt mysql query1.sql query.sql from db where system=@var ;

How can I adjust the code in order to have

$echo $app
$set @var:='prod'; select * from db where system=@var ;


Further details

There is a
in the
command, because the file
may contain multiple queries, e.g.:

query.sql; set @var:='prod'; @a=asd; @b=zxc, @...=...;

Answer Source

I don't think you need to read the SQL files line by line. Assuming that there are no semicolons in the filenames, you can construct the query with the following AWK command:

awk -F\; '{
 for (i = 2; i <= NF; i++) {
   if ($i != "") printf("%s;", $i);
 printf("source %s;\n", $1)
}' list.txt | mysql -uroot -proot

The command uses ; as a field separator. The for loop prints all fields starting from the second. The last printf function builds a query sourcing the SQL file.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download