2015年1月27日火曜日

さまざまな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

set 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では、ログインしたユーザーのデフォルトスキーマを使用しない場合が多いので、バッチにユーザーが使用するスキーマ名を第5パラメータとして追加し、「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:\MySQL\MySQL Server 5.6\bin\mysql.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:\PostgreSQL\9.2\pg_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%\postgresql\pgpass.conf

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


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

1行目はデフォルトで設定されていますので、2行目に新しく追加します。
サーバー、ポート、データベース、ユーザー名がここに設定した組み合わせの時、最後に記載したパスワードが採用されるようです。


0 件のコメント:

コメントを投稿