Category: Bash

Executing Dynamically Generated SQL Queries from a Shell Script

Posted on by rchapin

Following is how to generate dynamic SQL in a shell script and then execute those queries.

Let’s say, for instance, that you have a list of tables that you want to flush regularly during development and don’t want to type in the SQL queries each time.  Moreover, you just want to maintain a list of the table names and add and remove from it when necessary and have your script dynamically generate and execute the delete statements.

For the purposes of this tutorial we are running these commands as root to avoid the additional complication of authentication.

The first thing you need is your text file with the list of your tables:

table_list.txt:

table_a
table_b
table_c

delete_data.sh:

table_list.txt:

table_a
table_b
table_c

delete_data.sh:

#!/bin/bash

for i in `cat tables_list.txt`
do

   mysql -u root dbase_name << END_SQL
   delete from ${i};
END_SQL
#
# It is imperitive that the END_SQL redirect identifier
# be on its own line without any leading or trailing spaces
#

done

The script will run, will make a connection to MySQL and then execute the command with the dynamically generated target table.

This will work with other databases other than MySQL as well.

Splitting a String into an Array with a Custom Delimiter in a Bash Shell Script

Posted on by rchapin

Most high level languages have some sort of String.split([delimiter]) method to create an array of Strings tokenized by a user specified delimiter.  This is a simple way to convert a CSV into an array.

Here is a quick way to do that in a bash shell script:

#!/bin/bash

SOURCE_STRING='foo|blah|moo'
# Save the initial Interal Field Separator
OIFS="$IFS"
# Set the IFS to a custom delimiter
IFS='|'

read -a TOKENS <<< "${SOURCE_STRING}"
for i in "${TOKENS[@]}"
do
  echo "$i"
done

# Reset original IFS
IFS="$OIFS"
© 2005 Ryan Chapin