Windows Internal DatabaseへのPerlからのアクセス

WSUSのDBから必要なデータを直接引き抜きたかったので, いろいろと調べた結果のメモ

use strict;
use Win32::OLE;

# datetime型のデータが文字列で取得できる。
use Win32::OLE::variant;

# 指定タイプライブラリのコンスタントが参照可能
use Win32::OLE::Const 'Microsoft ActiveX Data Objects 2.0 Library';

# エラー時に本処理を中止し、Perlがエラーメッセージを出力し、本プロセスが終了する。
Win32::OLE->Option(Warn => 3);

# DBサーバー名とDB名設定
my $server = 'np:\\\\.\pipe\mssql$microsoft##ssee\sql\query';
my $db = 'SUSDB';

# WINDOWS 認証設定
my $connStr = "Provider=sqloledb;".
              "Data Source=$server;".
              "Initial Catalog=$db;".
              "Integrated Security=SSPI;";

print "$connStr\n";

# DB接続
my $objDB = Win32::OLE->new("ADODB.Connection");
$objDB->Open($connStr);
$objDB->{Errors}->{Count} and 
    die "cannot connect '$connStr'";

# テーブル有無チェック
my $rs = Win32::OLE->new("ADODB.Recordset");
$rs->Open("Select count(*) existance From sysobjects Where NAME = 'tbUpdate'", $objDB);

if ($rs->{existance}->{Value} == 0) {
  # テーブル無しの場合
  die "Table is not find";
}
$rs->Close();

# データ読み出し
$rs = Win32::OLE->new("ADODB.Recordset");
$rs->{CursorLocation} = adUseClient;
$rs->Open(qq{
SET NOCOUNT ON;

SELECT 
  RevisionID, 
  COUNT(SupersededUpdateID) AS counter 
INTO #X1 
FROM 
  SUSDB.dbo.tbRevisionSupersedesUpdate 
GROUP BY
  RevisionID;

SELECT
  COUNT(RevisionID) AS counter,
  SupersededUpdateID
INTO #Y1 
FROM
  SUSDB.dbo.tbRevisionSupersedesUpdate 
GROUP BY
  SupersededUpdateID;

SELECT
  B.updateID,
  B.LegacyName, 
  A.Title, 
  A.Description,
  D.Title AS Category,
  F.KBArticleID, 
  G.SecurityBulletinID, 
  H.MoreInfoURL, 
  A.RevisionID, 
  #X1.counter AS SupersedeUpdateCount, 
  #Y1.counter AS SupersededUpdateCount
FROM
  SUSDB.dbo.tbPreComputedLocalizedProperty A
  INNER JOIN
    SUSDB.dbo.tbUpdate B ON A.UpdateID = B.UpdateID 
  INNER JOIN
    SUSDB.dbo.tbRevisionInCategory C ON A.RevisionID = C.RevisionID 
  INNER JOIN
    SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty D ON C.CategoryID = D.CategoryID
  INNER JOIN
    SUSDB.dbo.tbRevision E on E.localUpdateID = B.localUpdateID
  LEFT JOIN
    SUSDB.dbo.tbKBArticleForRevision F ON F.RevisionID = E.RevisionID
  LEFT JOIN 
    SUSDB.dbo.tbSecurityBulletinForRevision G ON G.RevisionID = E.RevisionID
  INNER JOIN
    SUSDB.dbo.tbMoreInfoURLForRevision H ON H.RevisionID = E.RevisionID
  LEFT JOIN
    #X1 ON A.RevisionID = #X1.RevisionID
  LEFT JOIN
    #Y1 ON B.updateID = #Y1.SupersededUpdateID
WHERE
  A.ShortLanguage = 'en' AND
  D.CategoryType = 'UpdateClassification' AND
  D.ShortLanguage = 'en' AND
  E.islatestRevision = 'True' AND
  H.ShortLanguage = 'en'
ORDER BY
  G.SecurityBulletinID;
}, $objDB);

while(!$rs->EOF and $rs->{RecordCount} != 0){
  print join ',', map {(s/"/""/g or /[\r\n,]/) ? qq("$_") : $_} (
    $rs->{Fields}->{UpdateID}->{Value},
    $rs->{Fields}->{Title}->{Value}
  );
  print "\n";
  $rs->MoveNext();
}
$rs->Close();

# DB切断
$objDB->Close();