Creating an SQL file in BASH

Question

I am trying to create an sql script using bash but I keep getting this line after each iteration of my loop

: command not found

This is the case on Ubuntu as well as OSX.

At this stage I am not executing the sql script, I simply trying to create it. What am I missing so that it will not try to "execute" the query?

The queries are fine when tested in phpmyadmin I don't understand why would need to set the $PATH variable if I am not executing the actual query, I am just creating the text file.

The code I use is:

SQL="";

cat people.txt | while read line
do
    PW="my"$line"db";
    DB="test_"$line;

    $SQL=$SQL"CREATE DATABASE \`$DB\`;CREATE USER \`$line\`@\`localhost\`;SET PASSWORD FOR \`$line\`@\`localhost\` = PASSWORD(\"$PW\") ;GRANT ALL PRIVILEGES ON $DB.* TO \`$line\`@\`localhost\` IDENTIFIED BY \"$PW\";";

done

echo $SQL > t1.sql;

The list I am using for my imports:

bob123
john123
jane123

The output I am getting is:

./02_setup_mysqldb.sh: line 14: =CREATE DATABASE `test_bob123`;CREATEUSER `bob123`@`localhost`;SET PASSWORD FOR `bob123`@`localhost` = PASSWORD("mybob123db") ;GRANT ALL PRIVILEGES ON test_bob123.* TO `bob123`@`localhost` IDENTIFIED BY "mybob123db";: command not found
./02_setup_mysqldb.sh: line 14: =CREATE DATABASE `test_john123`;CREATE USER `john123`@`localhost`;SET PASSWORD FOR `john123`@`localhost` = PASSWORD("myjohn123db") ;GRANT ALL PRIVILEGES ON test_john123.* TO `john123`@`localhost` IDENTIFIED BY "myjohn123db";: command not found

Run your code through shellcheck.net; you have other problems besides that fixed in the accepted answer. — chepner
– chepner, Commented May 20, 2017 at 0:26

pilsetnieks · Accepted Answer · 2017-05-19 22:17:24Z

2

There's an error in your variable assignment, it should be:

SQL=$SQL"CREATE DATABASE...

Note the missing $ in front of the variable name - variables don't need $ in front of the name when values are assigned.

edited May 19, 2017 at 22:17

answered May 19, 2017 at 22:14

pilsetnieks

10.4k12 gold badges51 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jwknz Over a year ago

Thank you :-) I was also thinking about varibale scoping, like other programming languages. I will accept it soon (SO time limit)

Jack Over a year ago

There is also an append operation: SQL+="More stuff"

Pedro Rodrigues · Accepted Answer · 2017-05-19 22:31:57Z

Notes:

1.The assignment to SQL variable is incorrect, just change to SQL="$SQL...", just remove the $ character.

2.When you do cat people.txt | while read LINE, you are ignoring the last line, being necessary to have a blank line after the last line.

3.Your script has a large string concatenation in one line, just create variables to make it more readable.

Finally, code:

#!/bin/bash

while read line
do
    PW="my${line}db"
    DB="test_$line"

    create_database="CREATE DATABASE \`$DB\`;\n"
    create_user="CREATE USER \`$line\`@\`localhost\`;\n"
    change_password="SET PASSWORD FOR \`$line\`@\`localhost\` = PASSWORD(\"$PW\");\n"
    grant_privileges="GRANT ALL PRIVILEGES ON $DB.* TO \`$line\`@\`localhost\` IDENTIFIED BY \"$PW\";\n"

    SQL="${SQL}${create_database}${create_user}${change_password}${grant_privileges}"
done < people.txt

echo -e ${SQL} > t1.sql

Collectives™ on Stack Overflow

Creating an SQL file in BASH

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related