dbi Blog

Quering databases by using SQL in shell scripts can make the whole script unreadable. Very often the query is unredable too, due to the variable usage. This situation makes the script-debugging a pain.

Templating simplifies the script by taking out the SQL code from the source code file. J2 is ideally built for that.

Let’s take a simple example:

$> cat sql_in_shell.sh

#!/bin/bash
. oraenv <<< $1

table=$2
col1=$3
val1=$4

sqlplus -s / as sysdba << EOF
select count(*)
from $table
where $col1=$val1
;
exit;
EOF

Ok, this is a simple one, but in real world the queries are much complicated, and variables too…

The execution produces the following output:

$> sql_in_shell.sh MYDBNAME v\$instance instance_name \'MYDBNAME\'

  COUNT(*)
----------
	 1

Doing it like this it is very difficult to find the real query executed by the script…

Let’s use J2 templates.

We put all templates in a template directory and generated sql files in the onrun directory.

$> cat templates/qry.j2
select count(*)
from {{ table }}
where {{ col1 }}={{ val1 }}
;
exit;

The script becomes:

$ cat ./sql_in_shell.sh
#!/bin/bash

#. oraenv <<< $1

echo "$1"

j2_sql="qry.sql"
j2_templ="qry.j2"
export table=$2
export col1=$3
export val1=$4

j2 templates/$j2_templ > onrun/$j2_sql

sqlplus -s / as sysdba @onrun/$j2_sql

The output is the same:

$> sql_in_shell.sh MYDBNAME v\$instance instance_name \'MYDBNAME\'

  COUNT(*)
----------
	 1

Now we have the executed query in the onrun directory:

$ cat onrun/qry.sql
select count(*)
from v$instance
where instance_name='MYDBNAME'
;
exit;

Resources

J2 client can be found here: https://github.com/kolypto/j2cli. The site provides also an excelent documentation.

Ubuntu provides already deb packages and for rpm’s an additional repository must be added following: https://snapcraft.io/install/j2/rhel

Conclusion

Templating brings shell coding in to a more modern world, liberating the code source of difficult to understand variables replacement. More than that, keeping all executed queries, is a best practice and makes the debugging much easier.

Post Views: 2,011