. Hey, Scripting Guy!Windows PowerShell で Access データベースを操作する

Microsoft Scripting Guys

データベースは、謎めいたソフトウェアです。最も単純な形式のデータベースは、情報を格納するためのファイル キャビネットに過ぎません。本当の魔法は、この格納された情報を使用することで始まります。もちろん、どれほど巧みに設計されたデータベースであっても、レコードが格納されていなければ、実用性がありません。データが存在してこそのデータベースです。大規模なデータベースを所有している人が一目置かれる際、その対象は、データベースではなく、データベースに格納されているデータです。

そのような大量のデータをどのようにしてデータベースに格納したのでしょうか。データベースへのデータの手動入力では処理が追い付かず、キー パンチ カードとともに姿を消しました。同業者に感銘を与え、ネットワークの謎を解明する可能性をもたらす規模のデータベースを構築するには、自動化が必要です。現時点では、それを実現するのが Windows PowerShell です。このコラムでは、Windows PowerShell を使用して、ローカル コンピュータについてのデータを収集し、ComputerData.mdb という名前の Office Access データベースに書き込む方法を紹介します。このデータベースは手動で作成することもできますが、「複数のテーブルがあるデータベースを作成する方法はありますか」に記載されているスクリプトを使用して作成することもできます。このスクリプトの処理内容がわかるように、このスクリプト名は WriteToAccessDatabase.ps1 としましょう。

まず、Check-Path 関数を作成します。この関数はデータベースが存在するかどうかを確認するために使用します。この関数を作成するには、Function キーワードを使用し、関数に名前を付けて、受け取る必要のある入力変数を定義します。この関数では、まず、Test-Path コマンドレットを使用してデータベースのパスに含まれているディレクトリが存在するかどうかを確認します。これを実行するために、Split-Path コマンドレットを使用してパスを親の部分と子の部分に分割します。ディレクトリが存在していることを確認するために必要なのは、パスの親部分だけです。パスの親部分を取得するには、Split-Path コマンドレットを次のように使用します。

PS C:\> Split-Path C:\fso\ComputerData.mdb -Parent
C:\fso

ディレクトリが存在していることを確認する代わりに、否定演算子 (!) を使用してディレクトリが存在しないことを確認することができます。ディレクトリが存在しない場合は、Throw キーワードを使用してエラーを生成します。

Function Check-Path($Db)
{
 If(!(Test-Path -path (Split-Path -path $Db -parent)))
   { 
     Throw "$(Split-Path -path $Db -parent) Does not Exist" 
   }

ディレクトリが存在しても、データベース ファイルが存在しない場合があります。そのため、ELSE キーワードを使用して、代替条件を挿入しています。ここでも、If ステートメントを使用して、データベース ファイルが存在するかどうかを確認し、存在しない場合は、Throw キーワードを使用してエラーを生成します。

  ELSE
  { 
   If(!(Test-Path -Path $Db))
     {
      Throw "$db does not exist"
     }
  }
} #End Check-Path

実際には、If…ELSE 構文を使用してデータベースが存在するかどうかを確認する必要はありません。–path パラメータを指定して Test-Path コマンドレットを呼び出すだけで、データベースが存在するかどうかを確認できます。ただし、If…ELSE 構文を使用すると、高レベルのフィードバックが得られるというメリットがあります。ここでは、ディレクトリが存在しているかどうかと、ディレクトリが存在している場合は、ファイルが存在しているかどうかを確認する必要があります。実際、ディレクトリ内にデータベースが存在しなかったり、ディレクトリ自体が存在しなかったりすることは十分にあり得ます。そのため、If…ELSE 構文を使用すると、より詳細なフィードバックを得られるので、トラブルシューティングに役立ちます。

データベースが存在することを確認したら、Get-Bios 関数を作成して Win32_Bios WMI クラスから BIOS 情報を取得します。この Get-Bios 関数を以下に示します。

Function Get-Bios
{
 Get-WmiObject -Class Win32_Bios
} #End Get-Bios

WMI の呼び出しを関数にカプセル化すると、リモート機能の追加や資格情報の承認など、関数の機能を簡単に変更することができます。この関数の機能を変更しても、スクリプトの残りの部分に影響を与えることはありません。実際、テストの観点では、この関数の機能に問題がある場合は、単純に関数のコードをコメント アウトすると、残りのスクリプトは引き続き機能します。WMI クラスに関連する情報の検索には、Windows PowerShell Scriptomatic (図 1 参照) を使用できます。このツールを使用すると、WMI の名前空間とクラスを簡単に検索できるだけでなく、Windows PowerShell スクリプトを作成して情報を取得することもできます。

図 1 Scriptomatic ユーティリティの Windows PowerShell バージョン

次に、Get-Bios 関数を作成して Win32_VideoController WMI クラスからビデオ情報を取得します。ご覧のとおり、この関数は Get-Bios 関数と似ています。

Function Get-Video
{
 Get-WmiObject -Class Win32_VideoController
} #End Get-Video

ここでは、データベースに接続する必要があります。これを行うには、Connect-Database 関数を使用します。Connect-Database 関数に渡す 2 つの入力パラメータ、–db と –tables を作成します。これらのパラメータで渡す値は関数内で $Db 変数と $Tables 変数に格納されます。Connect-Database 関数では、まず、Recordset オブジェクトを開く方法を制御するのに使用する 2 つの変数に値を割り当てます。Recordset オブジェクトの Open メソッドでは、次に示すようにパラメータを最大で 5 つ受け取ることができます。

RecordSet.Open Source, ActiveConnection, CursorType, LockType, Options 

1 番目のパラメータ Source には、有効な Command オブジェクト、SQL ステートメント、テーブル名、ストアド プロシージャの呼び出し、URL、または永続的に格納される Recordset オブジェクトを含むファイルやストリーム オブジェクトの名前を指定します。2 番目のパラメータ ActiveConnection には、有効な接続オブジェクトを評価する文字列または ConnectionString パラメータを含む文字列を指定します。3 番目の CursorType パラメータには Recordset オブジェクトを開くときに使用するカーソルの種類を指定します。図 2 に、カーソルの種類として指定できる値を示します。

4 番目の LockType パラメータにはレコードの更新時に使用するロックの種類を指定し、5 番目の Options パラメータには Source パラメータで指定した値の評価方法をプロバイダに通知する値を指定します。図 3 に、LockType パラメータに指定できる値を示します。

Recordset オブジェクトの Open メソッドに指定する 5 つのパラメータはすべてオプションで、通常は最初の 4 つのみを使用します。カーソルの列挙とロックの種類を指定する値を指定したら、New-Object コマンドレットを使用して新しい ADODB.Connection オブジェクトを作成します。このオブジェクトは $connection 変数に格納します。次に、Connection オブジェクトで Open メソッドを使用します。このメソッドでは、プロバイダ名とデータ ソースを指定する必要があります。それから、Update-Records 関数を呼び出し、$Tables 変数を渡します。Connect-DataBase 関数を以下に示します。

Function Connect-Database($Db, $Tables)
{
  $OpenStatic = 3
  $LockOptimistic = 3
  $connection = New-Object -ComObject ADODB.Connection
  $connection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$Db" )
  Update-Records($Tables)
} #End Connect-DataBase

Update-Records 関数では、まず、ADODB.Recordset オブジェクトのインスタンスを作成します。New-Object コマンドレットを使用してインスタンスを作成し、新しく作成した ADODB.Recordset オブジェクトを $RecordSet 変数に格納します。次に、ForEach ステートメントを使用して、テーブルの配列を処理します。テーブル名は $Tables 変数に格納され、スクリプトの開始時に割り当てられます。ForEach ループでは、まず、Select * from $Table という比較的汎用的なクエリを作成します。テーブル名に変数を使用すると、コードを 1 回だけ記述すれば良いというメリットがあります。クエリ内のテーブル名は、テーブル名の配列のループ処理を 1 回終えるたびに変更されます。

次は、Recordset オブジェクトの Open メソッドです。Recordset オブジェクトを開く方法を制御するために、$Query 変数に格納されているクエリ、$Connection 変数に格納されている接続オブジェクト、$OpenStatic 変数の値、および $LockOptimistic 変数の値を指定します。次に、Invoke-Expression コマンドレットを使用して、文字列の値を実行します。これは、複数のデータベース テーブルを更新するように設計された 2 つの関数を作成しているために実行しています。関数には、更新するテーブル名に関連した名前を付けました。関数名の半分が変数で構成されている場合、その関数を呼び出すことはできないので、変数を解決してから関数を呼び出す必要があります。

ただし、変数を解決しても動作しません。少なくとも、直接は動作しません。ここでは、関数名をコマンドではなく文字列として扱いながら、コマンドのように実行する必要があります。これを行うには、Invoke-Expression コマンドレットを使用します。このコマンドレットでは、さまざまな更新関数を個別に呼び出します。テーブル名の配列を処理するループ処理では、各 Recordset オブジェクトを閉じて、テーブル名の配列の次の項目に戻り、新しいクエリを作成して、新しい Recordset オブジェクトを開き、新しい関数を呼び出しています。次に示すように、テーブルの配列の各テーブル名について、この処理を繰り返します。

Function Update-Records($Tables)
{
  $RecordSet = new-object -ComObject ADODB.Recordset
   ForEach($Table in $Tables)
     {
      $Query = "Select * from $Table"
      $RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)
      Invoke-Expression "Update-$Table"
      $RecordSet.Close()
     }

レコードが更新されたら、接続を閉じることができます。これを行うには、Connection オブジェクトで Close メソッドを使用します。

   $connection.Close()
} #End Update-Records

Update-Records 関数では 2 つのサポート関数である Update-Bios 関数と Update-Video 関数を呼び出します。これらのサポート関数は、各データベース テーブル内の適切なフィールドを更新するように設計されています。追加テーブルをデータベースに追加するには、新しいテーブルを更新するための別の Update* 関数を追加する必要があります。データベース フィールド名は WMI プロパティ名と同じ名前にすることをお勧めします。このような関係を維持すると、データを簡単に把握することができます。既存のデータベースを更新するスクリプトを作成するときには、テーブル、列、フィールドに格納されているデータの型について、データベース スキーマを調べることをお勧めします。ComputerData データベースのデータベース スキーマを図 4 に示します。この画面は、「データベースを開かずにデータベースのテーブルや列を調べる方法はありますか」で紹介しているスクリプトを使用して出力されたものです。

図 4 ComputerData データベースのデータベース スキーマ

Update-Bios 関数では、まず、BIOS 情報を更新することを示すメッセージを表示します。次に、Get-Bios 関数を呼び出し、返された WMI Win32_Bios オブジェクトを $BiosInfo 変数に格納します。ここで、データベース テーブルにレコードを追加する必要があります。これを行うには、Recordset オブジェクトで AddNew メソッドを呼び出します。新しいレコードを追加したら、テーブルの各フィールドに情報を追加します。すべてのフィールドを更新したら、Update メソッドを呼び出して、レコードをテーブルにコミットします。Update-Bios 関数を以下に示します。

Function Update-Bios
{
 "Updating Bios"
 $BiosInfo = Get-Bios
 $RecordSet.AddNew()
 $RecordSet.Fields.Item("DateRun") = Get-Date
 $RecordSet.Fields.Item("Manufacturer") = $BiosInfo.Manufacturer
 $RecordSet.Fields.Item("SerialNumber") = $BiosInfo.SerialNumber
 $RecordSet.Fields.Item("SMBIOSBIOSVersion") = $BiosInfo.SMBIOSBIOSVersion
 $RecordSet.Fields.Item("Version") = $BiosInfo.Version
 $RecordSet.Update()
} #End Update-Bios

Bios テーブルが更新されたら、Video テーブルを更新する必要があります。これを行うために、Update-Video 関数を呼び出すことができます。この関数は Update-Bios 関数とまったく同じ処理を行います。Video テーブルを更新していることを示すメッセージを表示し、Get-Video 関数を呼び出してビデオ情報を取得し、AddNew メソッドを呼び出して Video テーブルに新しいレコードを追加し、すべての情報を適切なフィールドに書き込みます。この処理が完了したら、Update メソッドを呼び出します。

ビデオ情報の収集については、コンピュータに搭載されているビデオ コントローラが問題になる場合があります。私のコンピュータには子カードが挿入されており、複数のビデオ コントローラが存在しているとレポートされます。この事態に対応するため、ForEach ステートメントを使用して、Win32_VideoControllers のコレクションを反復処理しています。子カードの構成情報が不要であったり、ビデオ カードがデュアル チャネルで、同じ情報が 2 回報告されたりする場合は、ForEach ループを削除し、代わりに $VideoInfo[0] を指定して、報告される最初のレコードに直接インデックスを作成できます。ただし、1 つのレコードに対してインデックスを作成することはできないため、この方法には、クエリがシングルトンの場合にエラーが返されるという問題があります。

Function Update-Video
{ "Updating video" $VideoInformation = Get-Video 
Foreach($VideoInfo in $VideoInformation)  
  {
   $RecordSet.AddNew()   $RecordSet.Fields.Item("DateRun") = Get-Date
   $RecordSet.Fields.Item("AdapterCompatibility") = $VideoInfo.AdapterCompatibility
   $RecordSet.Fields.Item("AdapterDACType") = $VideoInfo.AdapterDACType
   $RecordSet.Fields.Item("AdapterRAM") = $VideoInfo.AdapterRAM
   $RecordSet.Fields.Item("Description") = $VideoInfo.Description
   $RecordSet.Fields.Item("DriverDate") = $VideoInfo.DriverDate
   $RecordSet.Fields.Item("DriverVersion") = $VideoInfo.DriverVersion
   $RecordSet.Update()
  }
} 
#End Update-Video

次に示すように、スクリプトのエントリ ポイントでは、データベースをポイントし、テーブルを一覧表示して、Connect-DataBase 関数を呼び出しています。

$Db = "C:\FSO\ComputerData.mdb"+
$Tables = "Bios","Video"
Check-Path -db $Db
Connect-DataBase -db $Db -tables $Tables

図 5 に示すように、スクリプトの実行後には、ComputerData.mdb データベースに新しいレコードが書き込まれます。完全な WriteToAccessDatabase.ps1 スクリプトを図 6 に示します。

図 5 ComputerData.mdb データベースに新しいレコードが追加された状態

図 6 WriteToAccessDataBase.ps1

Function Check-Path($Db)
{
 If(!(Test-Path -path (Split-Path -path $Db -parent)))
   { 
     Throw "$(Split-Path -path $Db -parent) Does not Exist" 
   }
  ELSE
  { 
   If(!(Test-Path -Path $Db))
     {
      Throw "$db does not exist"
     }
  }
} #End Check-Path

Function Get-Bios
{
 Get-WmiObject -Class Win32_Bios
} #End Get-Bios

Function Get-Video
{
 Get-WmiObject -Class Win32_VideoController
} #End Get-Video

Function Connect-Database($Db, $Tables)
{
  $OpenStatic = 3
  $LockOptimistic = 3
  $connection = New-Object -ComObject ADODB.Connection
  $connection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$Db" )
  Update-Records($Tables)
} #End Connect-DataBase

Function Update-Records($Tables)
{
  $RecordSet = new-object -ComObject ADODB.Recordset
   ForEach($Table in $Tables)
     {
      $Query = "Select * from $Table"
      $RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)
      Invoke-Expression "Update-$Table"
      $RecordSet.Close()
     }
   $connection.Close()
} #End Update-Records

Function Update-Bios
{
 "Updating Bios"
 $BiosInfo = Get-Bios

 $RecordSet.AddNew()
 $RecordSet.Fields.Item("DateRun") = Get-Date
 $RecordSet.Fields.Item("Manufacturer") = $BiosInfo.Manufacturer
 $RecordSet.Fields.Item("SerialNumber") = $BiosInfo.SerialNumber
 $RecordSet.Fields.Item("SMBIOSBIOSVersion") = $BiosInfo.SMBIOSBIOSVersion
 $RecordSet.Fields.Item("Version") = $BiosInfo.Version
 $RecordSet.Update()
} #End Update-Bios

Function Update-Video
{
 "Updating video"
 $VideoInformation = Get-Video
 Foreach($VideoInfo in $VideoInformation)
  { 
   $RecordSet.AddNew()
   $RecordSet.Fields.Item("DateRun") = Get-Date
   $RecordSet.Fields.Item("AdapterCompatibility") = $VideoInfo.AdapterCompatibility
   $RecordSet.Fields.Item("AdapterDACType") = $VideoInfo.AdapterDACType
   $RecordSet.Fields.Item("AdapterRAM") = $VideoInfo.AdapterRAM
   $RecordSet.Fields.Item("Description") = $VideoInfo.Description
   $RecordSet.Fields.Item("DriverDate") = $VideoInfo.DriverDate
   $RecordSet.Fields.Item("DriverVersion") = $VideoInfo.DriverVersion
   $RecordSet.Update()
  }
} #End Update-Video

# *** Entry Point to Script ***

$Db = "C:\FSO\ComputerData.mdb"
$Tables = "Bios","Video"
Check-Path -db $Db
Connect-DataBase -db $Db -tables $Tables

Windows PowerShell で Office Access データベースを処理する方法の詳細については、Hey, Scripting Guy! コラムの 2009 年 2 月 20 日の週の記事を参照してください。また、2009 年夏季スクリプト競技大会も間もなく開催されます。ご期待ください。詳細については、scriptingguys.com をご覧ください。

Ed Wilson は有名なスクリプトの専門家です。『Windows PowerShell Scripting Guide』(2008 年)、『Microsoft Windows PowerShell Step by Step』(2007 年) など、8 冊の書籍を執筆しています。Ed は、マイクロソフト認定システム エンジニア (MCSE) や情報システム セキュリティ プロフェッショナル (CISSP) など、20 種類を超えるこの業界の資格を持っています。また、余暇には、木工作業、水中写真撮影、スキューバ ダイビング、お茶などを楽しんでいます。

Craig Liebendorfer は言葉を巧みに操る、マイクロソフトのベテラン Web 編集者です。Craig は毎日言葉にかかわって給料を受け取ることができる仕事が存在することが、いまだに信じられないと思っています。彼が大好きなことの 1 つは場違いなユーモアなので、まさにこのコラムにうってつけの人物です。彼は自分の立派な娘が、自分の人生における最も偉大な功績であると考えています。