Alessandro Blasetti Alessandro Blasetti - 13 days ago 5
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
list.txt
file in the following format:

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


where
query.sql
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
query.sql
.

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
loop
in the
awk
command, because the file
list.txt
may contain multiple queries, e.g.:

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

Answer

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.

Comments