Making a command script

If the script you have to create is just to be executed as-is by people having access to the server and if the data to be put into the DB are delivered to you (so nothing dynamic), you would have to make a bash script like this :

#!/bin/bash



if [ "x$1" == "x" ]; then

   echo "Usage: $0 <SQL commands file>"

   exit 1

fi



if [ ! -f "$1" ]; then

   echo "The file specified in parameter ($1) does not exist or is not readable"

   exit 2

fi



USER=myUser

PASS=myPassword

DB=myDB

SERVER=ipMysql



mysql -u$USER -p$PASS -h$ipMysql $DB < $1    

Copy all the above in one test file that you can call dataUpload.sh and then make it executable : sudo chmod +x dataUpload.sh.

What does this script do?

1. Check that you give a mandatory parameter : the path to a text file containing SQL command to update data in the DB (see below)


2. Check that the file given in parameter is accessible by the script


3. Set some variable with the credential to connect to the DB (attention that this is a quick approach here not taking some security aspect into consideration : for instance the password will be visible in clear text in the script and in the process list - ps -ef or ps auxwww when the script will run. You may look for a more secure approach)


4. Load the SQL commands of the SQL file into the DB using the command line mysql client.

A SQL command file

This SQL command file is just a text file containing all the SQL command to INSERT or UPDATE data you would have done yourself if you could access the server. One command by line, like in this example:

UPDATE myDb.member SET member_creditDueDate='2025-09-21 12:00:00';

UPDATE myDb.member SET member_credutDueDate='2025-10-11 00:00:00';

and so on.

If you write a file, lets say, called 20140620_dbupdate.sql, then the person who has access to the DB will just have to execute dataUpload.sh 20140620_dbupdate.sql at the command line prompt.

By splitting the shell and SQL commands, you can:

1. easily test the logic that loads data into the DB


2. pass only once the executable script


3. for later update, just focus on building a SQL commands file

More to do

This is a first quick approach to solve your problem, to be exhaustive I would need to cover :

• Securing the password


• Making use of SQL transactions


• More error detection and reporting in the script