さまざまなDBでSQLステートメントをバッチ実行する

（本記事の情報は、記事の公開日時点での情報であり、その正確性、完全性、最新性等内容を保証するものではありません。）

データ交換を行っていると、ツールでは実行できないDB操作をバッチからDBに指示する必要が出て来ます。
その際、SQLはほぼ同じでもSQL発行のやり方はDBごとに異なっています。

今回はバッチからSQLを発行する方法を、主なDBごとにまとめてみました。

今回発行するSQLはTRUNCATEによるテーブルの削除です。SQLが違っても基本的にやり方は同じです。

バッチは「TruncateSQL.bat」とし、パラメータは基本的に以下のようになります。

TruncateSQL テーブル名ユーザー名パスワード接続先

DBによって追加で必要なものや必要ない物がありますが、違いは各DBの説明に記載します。

Oracle

SQL*Plusを使用します。
SQL*Plusの実行ファイルはsqlplus.exeです。

sqlplus ユーザー名/パスワード@接続先 @実行するSQLを記述したファイル名

Oracle ClientまたはServerが正常にインストールされていれば、sqlplus.exeがある場所にPATHが通っているので、コマンド名だけでコマンド実行が可能です。
Oracleの接続先は、tnsnames.oraに記載されたTNSNameになります。

バッチの中で一時ファイルを作成し、それをSQL*Plusに引き渡すことでSQLを実行します。

@echo off
UserName=%2
set PassWord=%3
set ServerName=%4
echo TRUNCATE TABLE %1; > temp.sql
echo exit; >>temp.sql
sqlplus %UserName%/%PassWord%@%ServerName% @temp.sql
del /q temp.sql 2>&1 >NUL

Microsoft SQL Server

sqlcmd.exeを使用します。

sqlcmd -e -U ユーザー名 -P パスワード -S 接続先 -i 実行するSQLを記述したファイル名

SQL Serverが正常にインストールされていれば、sqlcmd.exeがある場所にPATHが通っているので、コマンド名だけでコマンド実行が可能です。
「接続先」は、SQL Serverのサービス名になります。
「-e」は入力されたコマンドを標準出力にエコーバックします。

バッチの中で一時ファイルを作成し、それをsqlcmdに引き渡すことでSQLを実行します。

@echo off
set UserName=%2
set PassWord=%3
set ServerName=%4
set UserSchema=%5
echo use %UserSchema% > temp.sql
echo go 　 >>temp.sql
echo TRUNCATE TABLE %1 >>temp.sql
echo go >>temp.sql
sqlcmd -e -U %UserName% -P %PassWord% -S %ServerName% -i temp.sql
del /q temp.sql 2>&1 >NUL

SQL Serverでは、ログインしたユーザーのデフォルトスキーマを使用しない場合が多いので、バッチにユーザーが使用するスキーマ名を第５パラメータとして追加し、「use %UserSchema%」によって後のSQL文で使用するデフォルトスキーマを明示しています。

Db2

Db2でSQL操作をするためには、db2.exeを使用しますが、db2.exeを使用するためにはDb2を利用する環境の整ったシェルを起動して、その中でdb2.exeを実行する必要があります。
コマンドラインで操作する場合、以下のようになります。

C:>db2cmd
――新しいコマンドウィンドウが開く――
C:>db2
(c) Copyright IBM Corporation 1993,2007
DB2 クライアント 9.7.4 コマンド行プロセッサー
・・・
db2 =>

db2コマンドを、バッチの中で行うには以下のようにします。

@echo off
set UserName=%2
set PassWord=%3
set ServerName=%4
echo connect to %UserName% user %PassWord% using %ServerName%; > temp.sql
echo TRUNCATE TABLE %1 IMMEDIATE; >>temp.sql
echo connect reset; >>temp.sql
db2 -tvf temp.sql
del /q temp.sql 2>&1 >NUL

DB2の場合、このバッチを直接CALLせず、以下のように呼び出します。

db2cmd /i /w /c TruncateSQL.bat user01 pass01 server01

MySQL

mysql.exeを使用します。

mysql --force --user=ユーザー名 --password=パスワード接続先 <実行するSQLを記述したファイル名

MySQLの場合はSQL文はコマンドにファイルを指示するのではなく、「<」によるリダイレクトで標準入力に流し込んでやります。
また、MySQLは正常にインストールされていてもコマンドのパスは通っていないため、MySQLのサーバーがある場所のbinフォルダを絶対パスで指定します。パスに空白文字が含まれる場合はダブルクォートで括るのを忘れないでください。

@echo off
set UserName=%2
set PassWord=%3
set Database=%4
set MySQL="C:MySQLMySQL Server 5.6binmysql.exe"
echo TRUNCATE TABLE %1 > temp.sql
%MySQL% --force --user=%UserName% --password=%PassWord% %Database% < temp.sql
del /q temp.sql 2>&1 >NUL

PostgreSQL

psql.exeを使用します。

psql -U ユーザー名 -w -d データベース名 -h サーバー名 -q -t -f 実行するSQLを記述したファイル名

PostgreSQLの場合はコマンド実行前にpg_env.batを実行することでpsql.exeにパスが通ります。

@echo off
call C:PostgreSQL9.2pg_env.bat
set UserName=%2
set ServerName=%3
set DbName=%4
echo TRUNCATE TABLE %1 > temp.sql
psql -U %UserName% -w -d %DbName% -h %ServerName% -q -t -ftemp.sql
del /q temp.sql 2>&1 >NUL

PostgreSQLもデータベース名の指定が必要なため、パラメータを増やしています。
また、PostgreSQLはコマンドラインからパスワードの指定が不可能なため、以下のファイルをあらかじめ編集しておきます。

%APPDATA%postgresqlpgpass.conf

pgpass.confの内容は例えば以下のようにします。

localhost:5432:*:postgres:postgres
localhost:5432:userdatabase:username:userpassword

１行目はデフォルトで設定されていますので、２行目に新しく追加します。

サーバー、ポート、データベース、ユーザー名がここに設定した組み合わせの時、最後に記載したパスワードが採用されるようです。