PowerShellでODBC接続して対話型でSQL操作するモジュールを作ろう

## はじめに
PowerShellではスクリプトファイル（.psm1）に関数を定義をして、**スクリプトモジュール**として読み込むことで、定義した関数を**コマンドレット**として呼び出すことができます。

その入門として `Enter-OdbcSession` というコマンドレットを作成します。イメージは `Enter-PSSession` です。つまり、`Enter-OdbcSession` を入力するとプロンプトが出力されて入力待ちとなる、というようなものです。

`Enter-PSSession` ではリモート上のPowerShellを操作するものですが、`Enter-OdbcSession` はデータソースにODBC接続をしてSQLコマンドを入力したりできるようなものを想定しています。

## プロンプトを作る

以下のようなスクリプトファイルを作成します。

```powershell:OdbcSession.psm1
function Enter-OdbcSession{
  [CmdletBinding()]
  param (
    [Parameter(Mandatory)]
    [String]$DSN # データソース名
  )

  begin {
  }

  process{
    while($true){
      # プロンプト
      $InputString = Read-Host "[$DSN]"
    }  
  }

  end{
  }
}
```

説明の前に実際に実行してみましょう。スクリプトを`Import-Module`で読み込んで `Enter-OdbcSession` と入力します。

```powershell:PowerShell
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`を指定したわけです。必須パラメーターにあえて何も指定せずに実行してみましょう。

```powershell:PowerShell
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{}` を次のように編集します。

```powershell:OdbcSession.psm1
  ...
  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` と入力すると

```powershell:PowerShell
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` で削除します。

```powershell:PowerShell
PS D:\OdbcSession> Remove-Module OdbcSession
```

削除されたのでこの状態では `Enter-OdbcSession` はコマンドとして認識されていません。改めて`Import-Module`をすることで最新のスクリプトがモジュールとして読み込まれます。その上で定義した`exit`と`clear`を入力してみましょう。

```powershell:PowerShell
PS D:\OdbcSession> Enter-OdbcSession -DSN TEST
[TEST]: exit
PS D:\OdbcSession>
```

`exit`と入力したことで `if($InputString -eq "exit")`条件に当てはまり`while`を中断する`break`が実行されました。続いてあえて定めていないコマンドを入力してみましょう。

```powershell:PowerShell
[TEST]: error
error はコマンドとして認識されません。
[TEST]:
```

`error`はどの`if()`条件にも当てはまらず`while($true){}`の最後まで到達して`"$InputString はコマンドとして認識されません。"`が出力されました。

続いて`clear`と入力します。すると`Clear-Host`が実行されますのでスクリーンがクリアされてスクリーン全体が次のようになるはずです。

```powershell:PowerShell
[TEST]:
```
`Clear-Host`の後に`continue`を書いているのは`while($true){}`の最後まで到達させずに途中で次の繰り返しに移るためです。

## ODBC接続をする
いよいよODBC接続をします。ここではODBCユーザーデータソースにTESTという名前のデータソースを用意しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/22212/f4ca561c-29c3-56b3-bff1-47c074fae133.png)

接続文字列は`"DSN=TEST"`で接続可能なようにしてあります。この部分については環境によって異なると思いますので読み替えてください。

ODBC接続するために以下のクラスのオブジェクトを用意します。

- **OdbcConnection** クラス
  - コンストラクター
      - OdbcConnection(String)
  - プロパティ
      - Database 
  - メソッド
      - Open()
      - Close()
      - GetSchema()

**OdbcConnection** オブジェクトは接続文字列により指定したデータソースについて接続することができます。これを使って `begin{}` と `end{}` を次のように編集します。

```powershell:OdbcSession.psm1
  ...
  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を指定してみます。

```powershell:PowerShell
PS D:\OdbcSession> Enter-OdbcSession -DSN ERROR
ERROR [IM002] [Microsoft][ODBC Driver Manager] データ ソース名および指定された既定のドライバーが見つかりません。
PS D:\OdbcSession>
```

以上は`try{}catch{}`によって`Open()`での例外からODBCエラーを出力しています。続いて用意していた`TEST`というDSNを指定してみましょう。

```powershell:PowerShell
PS D:\OdbcSession> Enter-OdbcSession -DSN TEST
[TEST]:
```

プロンプトが出力されました。`Open()`で例外が起きなかったということです。

## 接続情報を出力する
**OdbcConnection** オブジェクトから `database`というコマンドと`tables`というコマンドを作ります。

```powershell:OdbcSession.psm1
  ...
  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
      }
    ...
    }
  }
  ...
```

実行してみます。

```powershell:PowerShell
[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**オブジェクトに格納します。

```powershell:OdbcSession.psm1
  ...
  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
      }
      ...
    }
  }
  ...
```

実行してみましょう。

```powershell:PowerShell
[TEST]: select * from dbo.TEST
1

TEST
----
TEST

```

あらかじめ用意しておいた`TEST`というテーブルの内容が出力されました。SELECT文の直後に出力されている`1`は`$Adapter.Fill($DataSet)`が返す値で実行結果の件数を表しています。

続いてあえて誤ったSELECT文を実行してみます。

```powershell:PowerShell
[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文のように結果を出力する必要がないのでより簡単に書けます。

```powershell:OdbcSession.psm1
  ...
  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`テーブルに対して追加、削除、更新をします。

```powershell:PowerShell
[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を省略せずにここに書いておきます。

```powershell: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
    }
  }
}
```