はじめに
PowerShellではスクリプトファイル(.psm1)に関数を定義をして、スクリプトモジュールとして読み込むことで、定義した関数をコマンドレットとして呼び出すことができます。
その入門として Enter-OdbcSession
というコマンドレットを作成します。イメージは Enter-PSSession
です。つまり、Enter-OdbcSession
を入力するとプロンプトが出力されて入力待ちとなる、というようなものです。
Enter-PSSession
ではリモート上のPowerShellを操作するものですが、Enter-OdbcSession
はデータソースにODBC接続をしてSQLコマンドを入力したりできるようなものを想定しています。
プロンプトを作る
以下のようなスクリプトファイルを作成します。
function Enter-OdbcSession{
[CmdletBinding()]
param (
[Parameter(Mandatory)]
[String]$DSN # データソース名
)
begin {
}
process{
while($true){
# プロンプト
$InputString = Read-Host "[$DSN]"
}
}
end{
}
}
説明の前に実際に実行してみましょう。スクリプトをImport-Module
で読み込んで Enter-OdbcSession
と入力します。
PS D:\OdbcSession> Import-Module D:\OdbcSession\OdbcSession.psm1
PS D:\OdbcSession> Enter-OdbcSession -DSN TEST
[TEST]: aaa
[TEST]:
PS D:\OdbcSession>
プロンプト[TEST]:
が出力されました。まだコマンドを定義してませんので、たとえば aaa
と入力しても何も起きません。[CTRL] + C
で終了します。
以上のスクリプトは次のブロックから成り立ちます。
param
begin
process
end
param()
は入力パラメーターを定めています。param()
の直前に [CmdletBinding()]
を書いています。これにより Enter-OdbcSession
にコマンドレットとしての属性が加わりますが、ここではあまり気にしなくてもよいです。
param()
に[String]$DSN
を加えることによりDSN
というパラメーターが定義されました。
また [Parameter(Mandatory)]
のようにMandatory
と指定することでDSN
は必須パラメーターとなります。上の実行例ではEnter-OdbcSession -DSN TEST
と入力しました。パラメータDSN
にTEST
を指定したわけです。必須パラメーターにあえて何も指定せずに実行してみましょう。
PS D:\OdbcSession> Enter-OdbcSession
コマンド パイプライン位置 1 のコマンドレット Enter-OdbcSession
次のパラメーターに値を指定してください:
DSN:
DSN:
というプロンプトが出力されてパラメータを入力するように言われます。ここで TEST
と入力すると先に進み定義したプロンプト[TEST]:
が出力されます。
実行されるのは begin{}
、process{}
、end{}
の順番です。パイプラインで他のコマンドと組み合わせたときに気を付けるべきところですが、今回はあまり気にしなくてよいです。
process{}
に while($true){}
というループ文を用意して繰り返しRead-Host "[$DSN]"
を実行するようになっています。
この部分がプロンプトを定めています。Read-Host
により入力待ちを作り出し、入力結果を$InputString
に返しています。
コマンドを定義する
コマンドを定めます。ここでは exit
コマンドとclear
コマンドを定義しましょう。exit
プロンプトを終了するコマンド、clear
は画面をクリアするコマンドです。また定義していないコマンドが入力されたときにメッセージを出力するように実装します。process{}
を次のように編集します。
...
process{
while($true){
# プロンプト
$InputString = Read-Host "[$DSN]"
# 終了
if($InputString -eq "exit"){
break
}
# クリアスクリーン
if($InputString -eq "clear"){
Clear-Host
continue
}
# 認識していないコマンド
"$InputString はコマンドとして認識されません。" | Out-Host
continue
}
}
...
まず実行してみましょう。そのためにモジュールを読み込み直す必要があります。先ほど一度読み込んでいるので PowerShell で Get-Module
と入力すると
PS D:\OdbcSession> Get-Module
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 3.1.0.0 Microsoft.PowerShell.Management {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...}
Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object...}
Script 0.0 OdbcSession Enter-OdbcSession
Script 2.1.0 PSReadline {Get-PSReadLineKeyHandler, Get-PSReadLineOption, Remove-PSReadLineKeyHandler, Set-PSReadLineKeyHandler...}
Manifest 1.0.0.0 Wdac {Add-OdbcDsn, Disable-OdbcPerfCounter, Disable-WdacBidTrace, Enable-OdbcPerfCounter...}
となります。読み込まれているモジュールが一覧になっており、OdbcSession
が読み込まれているのが分かります。これを一度 Remove-Module
で削除します。
PS D:\OdbcSession> Remove-Module OdbcSession
削除されたのでこの状態では Enter-OdbcSession
はコマンドとして認識されていません。改めてImport-Module
をすることで最新のスクリプトがモジュールとして読み込まれます。その上で定義したexit
とclear
を入力してみましょう。
PS D:\OdbcSession> Enter-OdbcSession -DSN TEST
[TEST]: exit
PS D:\OdbcSession>
exit
と入力したことで if($InputString -eq "exit")
条件に当てはまりwhile
を中断するbreak
が実行されました。続いてあえて定めていないコマンドを入力してみましょう。
[TEST]: error
error はコマンドとして認識されません。
[TEST]:
error
はどのif()
条件にも当てはまらずwhile($true){}
の最後まで到達して"$InputString はコマンドとして認識されません。"
が出力されました。
続いてclear
と入力します。するとClear-Host
が実行されますのでスクリーンがクリアされてスクリーン全体が次のようになるはずです。
[TEST]:
Clear-Host
の後にcontinue
を書いているのはwhile($true){}
の最後まで到達させずに途中で次の繰り返しに移るためです。
ODBC接続をする
いよいよODBC接続をします。ここではODBCユーザーデータソースにTESTという名前のデータソースを用意しました。
接続文字列は"DSN=TEST"
で接続可能なようにしてあります。この部分については環境によって異なると思いますので読み替えてください。
ODBC接続するために以下のクラスのオブジェクトを用意します。
-
OdbcConnection クラス
- コンストラクター
- OdbcConnection(String)
- プロパティ
- Database
- メソッド
- Open()
- Close()
- GetSchema()
- コンストラクター
OdbcConnection オブジェクトは接続文字列により指定したデータソースについて接続することができます。これを使って begin{}
と end{}
を次のように編集します。
...
begin {
# ODBC接続
$Con = New-Object System.Data.Odbc.OdbcConnection("DSN=$DSN")
# 接続を開く
try{
$Con.Open()
} catch{
$_.Exception.InnerException[0].Message | Out-Host
break
}
}
...
end{
# 接続を閉じる
try{
$Con.Close()
} catch{
$_.Exception.InnerException[0].Message | Out-Host
break
}
}
...
実行してみます。あえて用意していないERROR
というDSNを指定してみます。
PS D:\OdbcSession> Enter-OdbcSession -DSN ERROR
ERROR [IM002] [Microsoft][ODBC Driver Manager] データ ソース名および指定された既定のドライバーが見つかりません。
PS D:\OdbcSession>
以上はtry{}catch{}
によってOpen()
での例外からODBCエラーを出力しています。続いて用意していたTEST
というDSNを指定してみましょう。
PS D:\OdbcSession> Enter-OdbcSession -DSN TEST
[TEST]:
プロンプトが出力されました。Open()
で例外が起きなかったということです。
接続情報を出力する
OdbcConnection オブジェクトから database
というコマンドとtables
というコマンドを作ります。
...
process{
while($true){
...
# データベース名
if($InputString -eq "database"){
$Con.Database | Out-Host
continue
}
# テーブル一覧
if($InputString -eq "tables"){
$Con.GetSchema("Tables") | Select-Object TABLE_SCHEM, TABLE_NAME | Out-Host
continue
}
...
}
}
...
実行してみます。
[TEST]: database
master
[TEST]: tables
TABLE_SCHEM TABLE_NAME
----------- ----------
dbo MSreplication_options
dbo spt_fallback_db
dbo spt_fallback_dev
dbo spt_fallback_usg
dbo spt_monitor
dbo TEST
sys trace_xe_action_map
sys trace_xe_event_map
コマンド database
でデータベース名 master
が出力され、コマンド tables
でテーブル一覧が出力されました。以上はOdbcConnectionオブジェクトのプロパティdatabase
とメソッドgetSchema()
により実装されています。
SELECT文を実行する
ODBC接続はできましたのでSQL文を実行できるようにしましょう。まずはSELECT文からです。SELECT文は実行に加えて実行結果を出力する機能も与えます。そのために以下のクラスが必要です。
-
OdbcDataAdapter クラス
- コンストラクター
- OdbcDataAdapter(String, OdbcConnection)
- メソッド
- Fill(DataSet)
- コンストラクター
-
DataSet クラス
- コンストラクター
- DataSet()
- プロパティ
- Tables
- コンストラクター
OdbcDataAdapter クラスはSELECT文を実行して結果を受け取ります。受け取ったデータはDataSetオブジェクトに格納します。
...
process{
while($true){
...
# SELECT文
if($InputString -like "select*"){
# ODBCデータアダプタ
$Adapter = New-Object System.Data.Odbc.OdbcDataAdapter($InputString, $Con)
# データセット
$DataSet = New-Object System.Data.DataSet
# SELECT文を実行する
try{
$Adapter.Fill($DataSet) | Out-Host
# SELECT結果を出力する
$DataSet.Tables[0] | Format-Table | Out-Host
} catch{
$_.Exception.InnerException[0].Message | Out-Host
}
continue
}
...
}
}
...
実行してみましょう。
[TEST]: select * from dbo.TEST
1
TEST
----
TEST
あらかじめ用意しておいたTEST
というテーブルの内容が出力されました。SELECT文の直後に出力されている1
は$Adapter.Fill($DataSet)
が返す値で実行結果の件数を表しています。
続いてあえて誤ったSELECT文を実行してみます。
[TEST]: select * from dbo.ERROR
ERROR [42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]オブジェクト名 'dbo.ERROR' が無効です。
正しくエラーが出力されました。Fill($DataSet)
のときの例外からODBCエラーを出力しています。
INSERT、DELETE、UPDATE文を実行する
SELECT文が実装できましたので続いてデータを操作するためのINSERT、DELETE、UPDATE文をコマンドとして実装します。以下のクラスが必要です。
-
OdbcCommand クラス
- コンストラクター
- OdbcCommand(String, OdbcConnection)
- メソッド
- ExecuteNonQuery()
- コンストラクター
OdbcCommand クラスは INSERT、DELETE、UPDATE文などのSQL文を実行します。SELECT文のように結果を出力する必要がないのでより簡単に書けます。
...
process{
while($true){
...
# INSERT、DELETE、UPDATE文
if($InputString -like "insert*" -or $InputString -like "delete*" -or $InputString -like "update*"){
# ODBCコマンド
$Cmd = New-Object System.Data.Odbc.OdbcCommand($InputString, $Con)
# INSERT、DELETE、UPDATE文を実行する
try{
$Cmd.ExecuteNonQuery() | Out-Host
} catch{
$_.Exception.InnerException[0].Message | Out-Host
}
continue
}
...
}
}
...
実行してみましょう。TEST
テーブルに対して追加、削除、更新をします。
[TEST]: select * from dbo.TEST
1
TEST
----
TEST
[TEST]: insert into dbo.TEST values('INSERT')
1
[TEST]: select * from dbo.TEST
2
TEST
----
TEST
INSERT
[TEST]: delete from dbo.TEST where TEST = 'INSERT'
1
[TEST]: select * from dbo.TEST
1
TEST
----
TEST
[TEST]: update dbo.TEST set TEST = 'UPDATE'
1
[TEST]: select * from dbo.TEST
1
TEST
----
UPDATE
たしかに追加、削除、更新に成功しています。
その他に実装できそうなコマンド
以上でSQLの基本的なコマンドは実装できました。その他にも次のようなコマンドの実装が考えられます。
-
csv
...Export-Csv
によりSELECT結果をCSV出力する -
clip
...Set-Clipboard
によりSELECT結果をクリップボードにコピーする -
sql
...Get-Content
によりSQLファイルから実行する -
grid
...Out-GridView
によりSELECT結果をグリッドで出力する -
list
...Format-List
によりリスト形式でSELECT結果を出力する -
transaction
...BeginTransaction()
によりトランザクションを開始する -
rollback
...RollBack()
によりロールバックする -
commit
...Commit()
によりコミットする -
columns
..GetSchema("Columns")
によりカラム一覧を出力する
OdbcSession.psm1
以上で作られたOdbcSession.psm1を省略せずにここに書いておきます。
function Enter-OdbcSession{
[CmdletBinding()]
param (
[Parameter(Mandatory,ValueFromPipeline)]
[String]$DSN # データソース名
)
begin {
# ODBC接続
$Con = New-Object System.Data.Odbc.OdbcConnection("DSN=$DSN")
# 接続を開く
try{
$Con.Open()
} catch{
$_.Exception.InnerException[0].Message | Out-Host
break
}
}
process{
while($true){
# プロンプト
$InputString = Read-Host "[$DSN]"
# データベース名
if($InputString -eq "database"){
$Con.Database | Out-Host
continue
}
# テーブル一覧
if($InputString -eq "tables"){
$Con.GetSchema("Tables") |
Select-Object TABLE_SCHEM, TABLE_NAME | Out-Host
continue
}
# SELECT文
if($InputString -like "select*"){
# ODBCデータアダプタ
$Adapter = New-Object System.Data.Odbc.OdbcDataAdapter($InputString, $Con)
# データセット
$DataSet = New-Object System.Data.DataSet
# SELECT文を実行する
try{
$Adapter.Fill($DataSet) | Out-Host
# SELECT結果を出力する
$DataSet.Tables[0] | Format-Table | Out-Host
} catch{
$_.Exception.InnerException[0].Message | Out-Host
}
continue
}
# INSERT、DELETE、UPDATE文
if($InputString -like "insert*" -or $InputString -like "delete*" -or $InputString -like "update*"){
# ODBCコマンド
$Cmd = New-Object System.Data.Odbc.OdbcCommand($InputString, $Con)
# INSERT、DELETE、UPDATE文を実行する
try{
$Cmd.ExecuteNonQuery() | Out-Host
} catch{
$_.Exception.InnerException[0].Message | Out-Host
}
continue
}
# クリアスクリーン
if(($InputString -eq "clear") -or ($InputString -eq "cls")){
Clear-Host
continue
}
# 終了
if($InputString -eq "exit"){
break
}
# 認識していないコマンド
"$InputString はコマンドとして認識されません。" | Out-Host
continue
}
}
end{
# 接続を閉じる
try{
$Con.Close()
} catch{
$_.Exception.InnerException[0].Message | Out-Host
return
}
}
}