Perl has a handy concept known as "data sections", which you can define and read as follows:


=data
SQL_FROM_Table: SELECT * FROM Table WHERE field='%d'
SQL_UPDATE_Table: UPDATE Table SET field='%d' WHERE field='%d' 
=end

while ( $pod{'data'}->readline ) {
    // do something with the data line
}

Shell doesn't have a construct like this, so if you have large blocks of data you're forced to use heredocs. Not that heredocs are bad, mind you, just they can become awkward:


# define variables holding our SQL statements
SQL_FROM_Table=`cat <<EOSQL
SELECT * FROM Table WHERE field='%d'
EOSQL
`
SQL_UPDATE_Table=`cat <<EOSQL
UPDATE Table SET field='%d' WHERE field='%d'
EOSQL
`

# populate and use one of them
printf "$SQL_UPDATE_Table" 123 456 | mysql

Yuck, that's hard to read! As it turns out, we can emulate data sections quite nicely in shell by exploiting exit and grep:


#!/bin/sh

# get SQL statement from our "data section"
sql=`grep ^SQL_FROM_Table: $0 | cut -f 2- -d :`

# populate and use that statement
printf "$sql" 123 | mysql

# data section follows below
exit 0
SQL_FROM_Table: SELECT * FROM Table WHERE field='%d'
SQL_UPDATE_Table: UPDATE Table SET field='%d' WHERE field='%d'

The trick is really quite simple to use:

  1. Put all of your "data" after the main exit
  2. Key each line so that you can uniquely identify the information (eg, start each line with a unique identifier)
  3. grep for the key from $0, cutting off the key

In the example above, I've tagged each line with a unique key. Each line is a SQL statement in printf format. All I need do is push the SQL statement through printf, to perform my needed replacements, then I have a fully prepared SQL statement ready to execute.

To make this code even more robust, I can functionalize the whole process:


#!/bin/sh

function runSQL() {
  # get SQL statement from our "data section"
  sql=`grep "^$1:" $0 | cut -f 2- -d :`

  # get rid of $1
  shift

  # populate and use that statement
  printf "$sql" $* | mysql
}

runSQL SQL_FROM_Table 123
runSQL SQL_UPDATE_Table 456 123

# data section follows below
exit 0
SQL_FROM_Table: SELECT * FROM Table WHERE field='%d'
SQL_UPDATE_Table: UPDATE Table SET field='%d' WHERE field='%d'