実は、ストアドや.NET系のコーディングを始める前は、PerlやJavaがメインで、DB操作のためのSQLは、コードや設定ファイルの中に書くことが中心でした。(ただしOracleとかでした)

その後、SQL Serverを中心にお仕事をするようになってから、ストアドを多用するようになり、コードもC#などで書くようになっていきました。

さて今回、XML-RPCで外部APIと連携を行う必要が出たので、PerlでSQL Serverの操作を行うことになりました。（C#でもXML-RPCは実装可能だったのですが、私以外のメンテナーがいないので、同僚がメンテナンスしやすいPerlで行きましょう、ということになったのでした...）

ただ、私としては、できるだけ面倒なSQLはコードに書かず、サーバ側のストアドでカバーしたいというのが希望でした。同僚も、SQL(ストアド）の方は操作/修正には問題無いので、この方針で。

• ストアドを使うメリットは、まずは、Perl中にSQLを書かなくて済むこと。（コード内で、@や\といった特殊文エスケープしたりするのが面倒なんですよね....）
• 同じストアドは（データ取得用ですけど）、ManagementStudioからも呼べるし、WebServiceでも呼び出せます。JavaのJDBCでも呼べますね。

さて、問題は、PerlのDBIで、SELECTステートメントを返すストアドをどう実行するか、ということと、複数SELECTの結果が返る場合、値をどうやって取得するかです。

• どうやらDBI + ODBCだとうまくできません....。（というか、とんでもなく久しぶりなので、よく分らない）
• かと言って、またもPerl中に長～いSQLは書きたくない。

一方、ADO .NETだと、SELECT結果は、複数STATEMENTも含め、DataSetというオブジェクトで返してくれるので、それぞれテーブル名や順番に取り出すことができます。（ここが、私が.NETのASPにしたいよ～と言った理由のひとつでした）

で、かなり時間がかかってしまったのですが、調べた結果、Win32::OLEを使うと、ADOを用できることが判明。

若干C#などで扱うのよりは面倒ではありますが、これでいくらか幸せになりました。

use strict;
use Win32::OLE;
use vars qw($dsn);

# Data Source Setting
$dsn = "driver=SQL Server;".
 "Server=localhost;".
 "database=TestDB;".
 "Trusted_Connection=no;".
 "AutoTranslate=No;";

# Connection Open
my $Conn = Win32::OLE->new("ADODB.Connection");
$Conn->{CursorLocation} = 3;? # the same as adUseClient
$Conn->{Open} = $dsn;

if (Win32::OLE->LastError() != 0) {
  print "Failed creating ADODB.Connection object -> "
    . Win32::OLE->LastError();
  exit 0;
}

my $Cmd = Win32::OLE->new("ADODB.Command");

if (Win32::OLE->LastError() != 0) {
   print "Failed creating ADODB.Command object -> "
       . Win32::OLE->LastError();
   exit 0;
}

my $Id;
my $ReferenceId;

####
# For Test
#
# 実際は、CGIのRequest Parameterやコマンドライン引数などから受け取ります。
#
###
$Id = 8;
$ReferenceId= 300;

# Command Execute
$Cmd->{CommandType} = 4; # Execute as Stored

# Prepared Statementを使わない場合はこんなかんじ。
$Cmd->{CommandText} =
    "dbo.getSampleData ($Id, $ReferenceId )";
$Cmd->{CursorLocation} = 3;
$Cmd->{ActiveConnection} = $Conn;

$Cmd->{Prepared} = 1;
$Cmd->{CommandTimeout} = 15;

my $rs = $Cmd->Execute();

if (Win32::OLE->LastError() != 0) {
    print "Failed opening ADODB.Recordset object for Command -> ". Win32::OLE->LastError();
    exit 0;
}

my $rs_count = $rs->{RecordCount};
print $rs_count . "\n";
if (!$rs_count) {
    print "Record not found to post or close.\n";
    exit 0;
}

print "\n------------1st SELECT Result-------------\n";
while ($rs->EOF != 1) {
    print "Id = ". $rs->Fields('Id')->{Value}."\n";
    print "Name = ". $rs->Fields('Name')->{Value}."\n";
      :
    $rs->MoveNext();
}

my $rs1 = $rs->NextRecordSet();

print "\n------------2nd SELECT Result-------------\n";
while ($rs1->EOF != 1) {
    print "ReferenceId = ". $rs->Fields('ReferenceId')->{Value}."\n";
    print "ProductName = ". $rs->Fields('ProductName')->{Value}."\n";

      :

    $rs1->MoveNext();
}
$rs->Close();
$Conn->Close();
exit 0;

Prepared Statementを使ってストアド実行時の引数を渡したい場合は、こんな感じ。

$Cmd->{Prepared} = 1;

#ストアドの戻り値を指定する場合　（Direction = 4）
$Cmd->Parameters->Append(
    $Cmd->CreateParameter('@retval', 3, 4));

# ストアドへの引数はこちら
$Cmd->Parameters->Append($Cmd->CreateParameter('@Id', 3,
    1, , $Id));
$Cmd->Parameters('@Id')->{Value} = $Id;
　　:

my $rs = $Cmd->Execute();

if (Win32::OLE->LastError() != 0) {
    adoError(Win32::OLE->LastError());
}

# 戻り値を取得します
$retval = $Cmd->Parameters('@retval')->{Value};

ただ、通常のADOのように、PerlからでもResultSetにテーブル名を付けて、明示的にアクセスできるのかは、まだ分りませんでした....。

また、ストアドへのパラメータを指定する部分は、Win32::OLE::Variantを使えばもっといいのかもしれませんが、うまく動かなかったので、以下のサイトを参考に直接値を指定しました。

http://msdn.microsoft.com/library/ja/default.asp?url=/library/ja/jpdnvid/htm/VintrDev/VISQL/visql.asp

また、コーディングの際は、ActivePerlを使い、eclipse + Perl EPICというPerl用のプラグインを使ってみました。これもなかなか便利でした。(Visual Perlっていうのもあるんですね)