LoginSignup
1
2

More than 1 year has passed since last update.

@e_do_kiriko

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

Last updated at Posted at 2021-07-10

はじめに

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

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

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

プロンプトを作る

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

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

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
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
PS D:\OdbcSession> Remove-Module OdbcSession

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

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

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

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

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

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

PowerShell
[TEST]:

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

ODBC接続をする

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

image.png

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

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

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

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

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

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

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

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

接続情報を出力する

OdbcConnection オブジェクトから databaseというコマンドとtablesというコマンドを作ります。

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

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
[TEST]: select * from dbo.TEST
1

TEST
----
TEST

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

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

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

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

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
    }
  }
}
1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2