How to get user input and apply it in sql statement in bash?

Question

I have two scenarios. One of them is called sqlscript.sql, and the other - script.sh. I have all the necessary queries written in my sql script. This is just a bunch of update statements. For instance:

UPDATE xxDev.SYS_PARAMS SET val = 'serverName' WHERE lower(name) = 'enginebaseurl';

I am running a .sql script in a .sh script. When the .sh script starts, I want it to ask the user for the server name and enter this user input and replace it serverNamein the sql statements.

I am new to bash scripts and this website, so I hope that I have the point of asking this question. I use PuTTY, if that matters at all.

Thanks for the advanced.

Answer 1

, ,

set @val 'serverName'
UPDATE xxDev.SYS_PARAMS SET val = @val WHERE lower(name) = 'enginebaseurl';

sqlscript.sql

val.sql

    set @val 'serverName'

. set-val.sql :

echo -n "enter server: "
read server
echo "set @val '$server' > set-val.sql

mysql:

cat set-val.sql sqlscript.sql | mysql 

, , .

Answer 2

, MySQL, - :

# TODO: prompt user for server name and store it into variable serverName
serverName="get from user"

cat <<"EOF" | mysql -u user1 -p passwd -h server1 -P 3306 -D db1
UPDATE xxDev.SYS_PARAMS SET val = '$serverName' WHERE lower(name) = 'enginebaseurl';
EOF

, sql script .sh, .

Answer 3

I'm going to let you know how to pass a shell parameter to your sql command, but here is an incredibly cool way to query a user for a server name. It may even be compatible with POSIX.

#!/bin/sh
echo -n "Hit me with that server name: "; read serverName
echo "${serverName}! Outstanding! Pick up \$200 when you pass Go!"