How do I get a user input and apply it in a sql statement in bash?

Question

I have two scripts. One is named sqlscript.sql and the other is named script.sh I have all of the queries needed written in my sql script. They are just a bunch of update statements. For example:

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

I'm running the .sql script IN the .sh script. When the .sh script runs, I want it to prompt the user for a server name and take that user input and replace it in serverName in the sql statements.

I'm brand new to both bash scripting and this website, so I hope I'm making sense asking this question. I'm using PuTTY if that makes a difference at all.

Answer 1

I would probably use a variable

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

You can split the sqlscript.sql into

set-val.sql

    set @val 'serverName'

and the actual update statements. Then you can recreate the set-val.sql from your user input:

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

and then you forward both files to mysql:

cat set-val.sql sqlscript.sql | mysql 

You should probably use this only for internal things, it seems a little fragile.

Answer 2

Suppose you use MySQL, try something like:

# 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

So in this example, you embed the sql script into the .sh so that you don't have to maintain two files.

Answer 3

I'm going let you figure out how to pass a shell parameter into your sql command, but here's an incredibly cool way to query the user for the server name. It might even be POSIX compliant.

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