今夜は、仙台でもいきなり雪が降ってきました。ううむ・・・明日の通勤が心配です・・・。
それはそれとして、今回の記事では、SQL CLRでCSVファイルを直接読み取る関数を作ってみます。
CSVファイルと言えば、データは複数行で構成されますから、通常のスカラ型関数では扱いきれません。
いよいよテーブル型で値を返す関数となります。
また、先日の記事で書かせていただきましたが、CSVファイル(テキストファイル)を読み取るためには、アセンブリを通常のSAFE権限ではなくて、EXTERNAL ACCESS権限で登録しなければなりません。
このテーブル型関数は、スカラ型関数と比べるとかなり複雑であり、取り決めも数多く存在します。
この取り決めは、一つでも無視すると途端にエラーに直結してしまうので、面倒でも丁寧に覚えていかないといけないです。
■テーブル型関数を組むにあたって■
(1)独特な構造を把握する必要があります。
SQL CLR関数を宣言する[Microsoft.SqlServer.Server.SqlFunction]部ですが、スカラ型関数と違って、テーブル型関数の場合はもっと詳細な記述が必要です。今回は以下のようになります。
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodNa
me="SQLCLR_GetCSV_StreamRow",TableDefinition="CSV_SEQ int,CSV_ITEM nvarchar(max),CSV_ELEMENT_COUNT int",
DataAccess=DataAccessKind.None
)]
特にFillRowMethodName="XXXX"というのは、超重要ポイントです。
テーブル型のSQL CLR関数は、かなり独特な構造を持っておりまして、作成した結果データは、SQL-Serverにreturnで直接戻したりしないのです。
returnされたデータは、SQL-Serverではなくて、FillRowMethodName="XXX"で指定した処理に送信されます。
この処理が、一行づつ、まるでストリーミングのようにSQL-Serverに戻してくれます。
自分でループ処理をプログラミングする必要は無く、FillRowMethodNameに処理名を記述するだけで、データの終了まで当該処理を繰り返してくれる仕組みになっています。最初は「えっ?」てなるかも知れませんが、何しろそういう取り決めなので慣れないといけないですね。
このように、テーブル型関数を作る時は、関数の本体と併せて、データを一行づつSQL-Serverに戻す「ストリーミング部分」の、二部構成で作るわけです。
この独特な構造を踏まえる事が、テーブル型関数のプログラミングを理解する事になる・・・と言えるかも知れませんね。
(2)関数本体の型は、クラスではなくてIEnamerableインターフェース
関数本体の型なのですが、クラスではなくて、IEnamerableインターフェースなのが大きな特徴になっています。このインターフェースにしないとエラーになってしまいます。
IEnamerableインターフェースを実装したオブジェクトならば何でも戻せるわけですが、今回は、List型のオブジェクトにデータをどんどん積んで行ってます。
(3)ストリーミング部分について
実際にコードのスクリーンショットを見ていただくと分かると思うのですが、ストリーミング部分は、関数本体から戻されたデータの、一行あたりの受信とSQL-Serverへの出力を定義しています。
型は無く、必ずstatic voidにします。
どこにもループ処理のプログラミングが存在していないので、いきなりコードを読んでも「一行あたりの処理」という部分は見えにくい点かと思います。
くどいようですが、処理の宣言部分にあるFillRowMethodName=""に名前を記述するだけで、繰り返しは自動でやってもらえるんですよ。
ここでも数々の取り決めがあります。まず、関数本体から送信されて来たデータは、必ずobject型で受信しなければなりません。object型以外ではコンパイルエラーになってしまうのです。
このように、テーブル型関数は、スカラ型関数と比べて驚くほどに様相が違っていますね。