CODEWITHSUNDEEP
mysqlsqlbash
jwknz
jwknz

Reputation: 6824

Creating an SQL file in BASH

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

Upvotes: 4

Views: 4427

Answers (2)

Pedro Rodrigues
Pedro Rodrigues

Reputation: 1688

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

Upvotes: 1

pilsetnieks
pilsetnieks

Reputation: 10430

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.

Upvotes: 2

Related Questions