SQL簡易実行つーる

　環境制約やセキュリティ等の都合で、DB2やOracleのClientを導入できないが、shellscriptからSQLを実行したい場合に。ResultSetはCSV形式で標準出力。DB接続パスワード暗号化機能付き。

SQLExecuter.7z [eclipse project]

#!/bin/sh

## exec sql
##
## @author hoge
## @version 2018.06.07

## config (required)

## config (option)

## do not modify below

set -e
#set -ex

BASENAME=`basename $0`

usage() {
  cat <<_EOT_ 1>&2
usage:
 $BASENAME {threshold} (single|grid)
_EOT_
  exit 1
}

if [ "$1" = "" ]; then
  usage
  exit 1
fi
THRESHOLD=${1}
FUNCNAME=${2}

cd `dirname $0`/SQLExecuter

## functions

single_sample() {
  CNT=`echo "select count(*) from CSTMST with ur;" | /usr/java8_64/jre/bin/java -jar SQLExecuter.jar in: | sed 's/"//g'`
  echo $CNT
  [ $CNT -gt $THRESHOLD ] && exit 9 || exit 0
}

grid_sample() {

  RESULT=`/usr/java8_64/jre/bin/java -jar SQLExecuter.jar in: << EOS
select
 nksm_sbt_kbn,
 count(nksm_sbt_kbn) as cnt
 from EVNKKSM
 group by nksm_sbt_kbn
 with ur;
EOS
`

  echo "$RESULT"

  for i in `echo "$RESULT"`
  do
    SBT=`echo $i | cut -d ',' -f 1 | sed 's/"//g'`
    CNT=`echo $i | cut -d ',' -f 2 | sed 's/"//g'`
    if [ $CNT -gt $THRESHOLD ]; then
      echo Count of $SBT is over $THRESHOLD. 1>&2
      exit 9
    fi
  done
}

${FUNCNAME}_sample