バッチファイル

バッチファイルからSQL Serverに接続してみた

 

バッチファイルとは、Windows環境で実行できるコマンド列を記述したテキストファイルで、バッチファイルを使うと、例えばデータベースに接続して複数のSQL文を実行することができる。

今回は、バッチファイルからSQL Serverに接続し、複数のSQL文を実行してみたので、そのサンプルプログラムを共有する。

前提条件

下記記事のように、A5M2を利用してSQL Serverに接続できていること。

A5M2を利用して各DBに接続してみたA5M2(A5:SQL Mk-2)は、SQL文の入力支援やER図作成などの機能を備えていて、Oracle、MySQL、PostgreSQ...

サンプルプログラムの作成

サンプルプログラムの構成は以下の通りで、今回全て新規で作成している。
サンプルプログラムの構成

setting.iniの内容は以下の通りで、データベースの接続先などを定義している。

;共通設定ファイル

;DBサーバー名
serverName=localhost
;ユーザー名
userName=USER01
;パスワード
userPass=USER01
;DB名
dbName=master
;スキーマ名
schemaName=dbo
;様々な文字を含むパラメータ
param=!"#$%&'()=-~^|\`@{[;]}+<>*/:_?\

select_max_id.sqlの内容は以下の通りで、user_dataテーブルのid最大値を取得するSQLを記載している。

-- 「n行処理されました」を表示しないようにする
SET NOCOUNT ON;

-- user_dataテーブルのid最大値を取得する
SELECT ISNULL(MAX(id), 0) FROM $(schema).user_data;

insert_update.sqlの内容は以下の通りで、user_dataテーブルにデータ追加・データ更新するSQLを記載している。

-- 「n行処理されました」を表示しないようにする
SET NOCOUNT ON;

-- user_dataテーブルに1行追加する
INSERT INTO $(schema).user_data 
    ( id, name, birth_year, birth_month, birth_day, sex, memo )
VALUES ( CONVERT(int, $(maxId)), N'テスト　プリン', 2014, 3, 22, '1', NULL );

-- user_dataテーブルのメモを更新する
-- 1つのSQLだけでなく、複数のSQLも、1つのsqlcmdコマンドで実行可能となる
UPDATE $(schema).user_data SET memo=N'idが最大値です' WHERE id = CONVERT(int, $(maxId));
UPDATE $(schema).user_data SET memo=NULL WHERE id <> CONVERT(int, $(maxId));

call_sqlcmd.batの内容は以下の通りで、setting.iniの内容を読み込み、前述のSQLを呼び出している。

@echo off

rem 設定ファイル(setting.ini)の設定値を読み込む
for /f "tokens=1,* delims==" %%i in (setting.ini) do (
    set %%i=%%j
)

rem 設定ファイル内の各設定値を出力
rem 様々な文字を含むパラメータ(param)も問題なく出力している
echo *** 設定ファイルから取得したパラメータ値 ***
echo serverName: %serverName%
echo userName: %userName%
echo userPass: %userPass%
echo dbName: %dbName%
echo schemaName: %schemaName%
echo param: %param%
echo.

rem user_dataテーブルのid最大値を取得する
rem sqlcmd -S (ホスト名) -U (ユーザー名) -P (パスワード) -d (データベース名) 
rem   -i (実行するSQLファイルパス) -v (パラメータ)
rem その際、スキーマ名をパラメータに渡す
rem 以下のコマンドは1行で記載する必要がある
set retVal="sqlcmd -S %serverName% -U %userName% -P %userPass% -d %dbName% 
               -i select_max_id.sql -v schema=%schemaName%"

rem select文の実行結果を変数「nowMaxId」に格納
for /f %%i in ('%retVal%') do ( 
    set nowMaxId=%%i
)

echo *** user_dataテーブルのid最大値 ***
echo nowMaxId: %nowMaxId%
echo.

rem nowMaxIdに1を加算した値をmaxIdに設定する
set /a maxId=%nowMaxId%+1

echo *** user_dataテーブルのid最大値+1 ***
echo maxId: %maxId%
echo.

echo *** user_dataテーブルの追加・更新 ***
echo user_dataテーブルの追加・更新開始
rem sqlcmd -S (ホスト名) -U (ユーザー名) -P (パスワード) -d (データベース名) 
rem   -i (実行するSQLファイルパス) -v (パラメータ)
rem その際、スキーマ名・追加するデータのID(maxId)をパラメータに渡す
rem 以下のコマンドは1行で記載する必要がある
sqlcmd -S %serverName% -U %userName% -P %userPass% -d %dbName% 
       -i insert_update.sql -v schema=%schemaName% maxId=%maxId%
echo user_dataテーブルの追加・更新完了



【PR】「Filmora」は初心者でも本格的な動画編集ができる大変便利なツールだった「Filmora」は初心者でも使いやすい動画編集ツールで、テンプレートとして利用できるテキスト・動画・音楽などが充実していると共に、複数...

サンプルプログラムの実行結果

サンプルプログラムの実行結果は、以下の通り。

1) バッチファイルを実行前のデータは、以下の通り。

SELECT * FROM dbo.user_data ORDER BY id ASC
サンプルプログラムの実行結果_1

2) コマンドプロンプトを起動し、バッチファイルが存在するディレクトリ(C:\tmp)に移動し、各ファイルの存在を確認する。
サンプルプログラムの実行結果_2

3) バッチファイル(call_sqlcmd.bat)を実行する。実行すると、コマンドプロンプトには以下の値が表示される。
サンプルプログラムの実行結果_3

4) バッチファイルを1回実行後のデータは以下の通りで、SQLに記載通りに、データを1件追加しmemoの値が更新されていることが確認できる。

SELECT * FROM dbo.user_data ORDER BY id ASC
サンプルプログラムの実行結果_4

5) バッチファイル(call_sqlcmd.bat)を再度実行する。実行すると、コマンドプロンプトには以下の値が表示される。
サンプルプログラムの実行結果_5

6) バッチファイルを2回実行後のデータは、以下の通りで、SQLに記載通りに、データを1件追加しmemoの値が更新されていることが確認できる。

SELECT * FROM dbo.user_data ORDER BY id ASC
サンプルプログラムの実行結果_6

要点まとめ

  • バッチファイルとは、Windows環境で実行できるコマンド列を記述したテキストファイルで、バッチファイルを使うと、例えばデータベースに接続して複数のSQL文を実行することができる。
RELATED POST