ダイナミックSQLのテクニック
ダイナミックに実行されたITS00187R2を使用することで、基本的には自分自身のプログラムと同様のプログラムを構築することができます。たとえば私の職場では、同じプログラムを使用して、FLATSQLというコマンド(select文が返してきたすべてのフィールドを1行で表示する)やTABSQLというコマンド(タブ区切りのファイルを作成する)を作成していますし、同じコードを基にストアード･プロシージャを作ることも計画しています。
SQLユーティリティ・プログラムであるITS00187R2はダイナミックなカラム選択を容易にし、しかもそれをあらゆる種類のプログラムから行なうことができます。ITS00187R2はRPG IIIだけでなく、RPG IVとも一緒に動作させることができます。しかし、ITS00187R2のプログラミングについてもっと理解を深め、その制限を回避したいのではないでしょうか。
たとえば、このユーティリティを使用したプログラムが、同じユーティリティを使用している他のプログラムを呼び出すことができるためには、その性質上、その2つめのプログラムが*NEWアクティベーション・グループで実行されている必要があります。そうでなければ、再帰呼び出しエラーになります。カーソルを自分でオープンする場合、1つのモジュールで同時に複数のカーソルをオープンすることができます。
サービス・プログラムITS00187Rは、渡すポインタがどんなものであってもその上で動作し、カーソルごとに新たなコピーを用意する必要はありません。2つの別の行にあるフィールドを比較するテスト・ツールを書きたい場合、自分でカーソルをオープンし、このサービス・プログラムを使用してメモリを割り当て、表示行のフォーマットを整えるようにすればよいでしょう。
ITS00187R2ユーティリティは5,000バイトのSQLしか処理できず、5,000バイトのフォーマットされたデータしか返すことができません。柔軟性とランタイムの利点のトレードオフを考慮して、この数字に決めました。もちろん、もっと大きなフィールドに変更することもできます。このサービス・プログラムはもっと多くのデータを処理することができます。また、データを数値で受け取ったりタイムスタンプを付けたりして、実際の数値やタイムスタンプ・フィールドに容易に戻すこともできますし、フェッチによってフィールドに入った実際のデータを取得することもできます。したがって、ITS00187R2の動作を理解しておくことは大切なことなのです。
ダイナミックSQLを使用する
予めわかっているフィールドに対してダイナミックSQLを使用するのは、きわめて簡単です。
- SQL変数中でSQL selectを含んでいるカーソルを定義します。
- プログラムの文字変数からSQL変数を準備します。
- カーソルをオープンします。
- SQLSTTがデータの終わりを知らせるまで、ループ中でプログラム変数へフェッチします。
- カーソルをクローズします。
このプログラミングは実にシンプルです。1つのファイルに対してさまざまなソート・シーケンスやさまざまな選択方法を行ないたいだけであれば、上記のステップだけで十分です。このようなアプリケーションをたくさん作る方法はすばやく習得できます。
フィールドが事前にわからない場合は、コードはより複雑になります。記述子エリアを解釈して操作する必要があるからです。この場合、以下のような手順になります。
- SQL変数中でSQL selectを含んでいるカーソルを定義します。
- 記述子エリア中にSQL変数を準備します。最大数のカラムを設定した場合、十分な大きさのメモリ領域中にステートメントを一回準備することができます(すばやく作れるソリューションです)。または、ヘッダ・データだけでステートメントを一回作成し、カラム記述子に必要なメモリを割り当てて、もう一度準備するという方法もあります。
- 記述子エリアを解釈して、フィールド・タイプ、サイズ、名前などを判断します。
- 1行のデータを返すために必要なメモリを割り当てます。
- 各フィールドのサイズに基づいて、データの返却用に割り当てられたメモリへのポインタからスタートして、各カラムの変位をそれに加えていき、移動したポインタをそのカラムが使用することになる記述子エリアに対して更新します。
- カーソルをオープンします。
- SQLSTTがデータの終わりを知らせるまで、ループ中でカーソルをフェッチします。フェッチする際、「using descriptor」節をコードに書きます。こうするとシステムは各フィールドを、そのカラム用の記述子が示すポインタアドレスから始まるメモリにコピーします。
- カーソルをクローズします。
- ダイナミックに割り当てられたメモリを解放します。
役に立つツール
本稿では、SQLカーソルを渡す方法を皆さんがすでにご存知であるということを前提にしています。本稿で述べるタスクは、記述子エリアの処理、メモリの設定、表示可能なフォーマットへのデータの変換に関するものです。こうしたタスクは、それを遂行するためのテクニックをご存知の方にとってさえも、かなり複雑なものです。私たちは皆ILEモジュールのプログラミングができますので、上記のようなコードをいったん書いてしまえば、サービス・プログラムを使用して必要なときにそれを起動することができます。
私の場合、LISTSQL関数などといったダイナミックSQLタスクをサポートするサービス・プログラムを書きました。このサービス・プログラムのルーチンは、他のダイナミックSQLの設定にも容易に適用することができます。ダイナミックSQLサービスの一覧を図1に示します。
このサービス・プログラムにはSQL文はありませんが、その代わりにポインタと配列があり、計算が行なわれます。RPGはオブジェクト指向言語ではありませんので、計算はすべて、呼び出し側に保存されているポインタを基にして行なわれます。サービス・プログラムに別のグループのポインタが渡されたら、別のメモリ領域で作業が行なわれますので、複数のカーソルを一度に処理することができます。
(サービス・プログラムが1つのカーソル用に必要とする変数は、コピー・メンバーITS187CPYにあります。複数のカーソルを実行したい場合は、これらの変数のように別の複数の変数宣言をする必要があります。)
呼び出し側は通常は単にポインタを渡すだけで、そのポインタにアクセスする必要がありません。もしRPGがオブジェクト指向言語だったら、ポインタを特定のインスタンスの中に隠蔽していたかもしれません。RPGが今のままだとしても、それぞれのカーソル用のポインタ列に対するインデックスとして動作するサービス・プログラム中に「OpenID」番号を設けることもできますので、それをサービス・プログラムの配列中に保存することもできます(CLIはこのように動作します)。こうすると、定義可能な最大カーソル数を制限することになるかもしれませんが、カーソルは200程度もあれば十分でしょう。
ITS00187R2のOPENアクション
サービス・プログラムのプロシージャを理解するのは、SQLユーティリティであるITS00187R2がそのプロシージャをどのように使用しているのかを見てみるのが一番簡単でしょう。最も複雑なコードはOPENアクションに含まれているコードです。
ITS00187R2は二重準備手法を使用しています。コードの別の場所でカラム数を256個に制限しているので、この手法はばかげているかもしれません。最初の準備は記述子エリアの「Header」を使用します。これは行の値を設定する16バイトの値です。つまり、どれだけのカラムを記述したいのかを指定するヘッダの部分がゼロに設定されるということで、戻ってくるのは修正されたヘッダだけです。このヘッダ変数を準備することで、ヘッダ中にポストされる実際のカラム数に影響を与えます(ITS00187R2のソースコードの22.36行目から22.30行目を参照してください)。
ITS00187R2がどこにあるかを知る必要はありません。prAllcSQLDAがカラム数を抽出し、すべてのカラムの記述を保持するのに十分なメモリ領域を割り当て、このメモリ領域へのポインタと自分自身の中の変数(最後の2バイト)にあるカラム数を返します。必要なメモリ・サイズを求める計算はきわめて簡単です。ヘッダのサイズと、カラムの数にカラムあたりの記述子エリアのサイズをかけたものを合計すればよいのです。(ITS00187R2のソースコードの30.01行目から34.00行目を参照してください。プロトタイプはITS187CPYの1.00行目から3.00行目に定義されています。prAllcSQLDAはITS00187Rの32.00行目から40.00行目になります。)
コピーされた変数にはDESCRIPTORという変数が含まれ、prAllcSQLDAから返ってくるポインタを基にしています。ポインタで参照されたメモリ領域は、返されてくるであろうと予想されていたSQLのカラム数で既に更新されていますので、この時点でITS00187R2はもう一度SQLを準備することができ、選択された各フィールドのメタデータを取り戻すことができます。コピーされた変数からDESCRIPTORのサイズがわかりますが(そのDESCRIPTORをクリアしない限りは)、それがどれくらい大きいかは問題ではありません。ITS00187R2はこの変数を単に参照渡しで渡すだけですので、その開始アドレスだけが重要なのです。DESCRIPTORのサイズは、そこに書かれているよりもずっと小さいのが普通で、その中に何かを移動させるとメモリが破壊されます(ITS187CPYのソースコードの.20行目を参照してください)。
返されてきた行データ用にメモリを設定する作業は、prAllcSQLBufferが行なってくれます。このルーチンに渡される最初のポインタは記述子エリアへのポインタで、これは値渡しで渡されます。以後その他のポインタにはすべて、ダイナミックに割り当てられたメモリ領域を指すポインタ値が返ってきます。(宣言についてはITS187CPYの.24行目から.32行目、プロトタイプについてはITS187CPYの4.00行目から9.00行目、ルーチンについてはITS00187Rの42.00行目から50.32行目を参照してください。)
PrAllcSQLBufferは汗水たらして働かなければなりません。SQL PREPAREが返してくる記述子エリアには、各SQLフィールドのタイプと、その値としてnullを取りうるのか否かを指定した数値コードがあります。バッファに必要なサイズの計算は、このコードを基に異なります。割り当てルーチンは、各カラムについて独自に計算した結果を保持するメモリ領域を割り当て、サービス・プログラム(prGetSQLFldDes)中の別のルーチンをカラムごとに呼び出してメモリ領域に書き込みます。返されたフィールド記述データ構造には、カラム用に返されたデータを保持するのに必要なバッファのサイズが含まれています。(ITS00187Rサービス・プログラムのソースコードの42.00行目から50.33行目を参照してください。)
PrAllcSQLBufferはカラムごとのサイズを合計し、1行全体のデータを保持するメモリ領域を割り当てます。(注記: 「select * from mytable」を処理すると、その結果バッファのサイズは、そのテーブル用に外部で記述されたデータ構造のサイズとほぼ同じになります。ただしいつも全く同じサイズになるとは限りません。)
またPrAllcSQLBufferは、nullインジケータ配列用(セレクトされたカラムごとに単精度整数の領域)と、配列内の各カラムに対する変位の配列用(カラムごとに整数の領域)にも、メモリ領域を割り当てます。プログラムは変位を使用してバッファの開始位置にポインタをすばやく調整し直し、バッファ中の各カラムの開始位置を知らせます。(サービス･プログラムITS00187Rのソースコードの120.01行目にあるprGetSQLDisplayを見ていただくとおわかりの通り、変位の配列とその配列に渡すカラム数を使用して、バッファ中の特定のカラムへの変位を計算しています。)
prGetSQLFldDesが返すデータ構造中には、カラム中のデータを文字表現するのに必要なエリアがどれくらいかを指定するフィールド(ITS187CPY中のdsSQLFldDesの中のfd_ColWide)があります。この計算は、このデータにちょうど必要なサイズを結果として算出します。ヘッダが大きければ、文字カラムの幅を増やしてヘッダが尻切れにならないようにする必要があります。ITS00187R2は、ソースコードの55.16行目でprAdjSQLColWideを起動して、調整後の幅をポストしています。
このような選択をした場合、SQL名(記述子に関する限り最大30)の代わりにシステム・カラム名(最大10バイト)を使うこともできます。この方法は、準備の際のオプションの1つであり、ルーチンはどちらでも同じように動作します。また両方とも使用することもできます。両方とも使用する場合は、それぞれの名前に対して別々のカラム・エントリが割り当てられるので、返ってくるカラム・データのサイズが2倍になります。これは、今回のサービス・プログラムでは動作しませんので、使用しないでください。
PrGetSQLDisplayは、計算結果が保存されているメモリ中に保存されている表示可能幅に基づいて、返すデータの幅を調整します。私がご紹介している表示可能カラム幅の設定方法と異なるルールを使用する場合は、その修正した計算を使用してprAdjSQLColWideと同じようなルーチンを書く必要があります。(おそらくレポート・ライターはユーザーにカラム幅を設定させるでしょうが、私のシステムのユーザーはそうしたことで手を煩わすことを嫌うのです。)
表示可能データの幅は、dsSQLFldDes fd_ColWide(ITS187CPYの.02行目から.09行目)と同じような、ダイナミックに割り当てられたフィールド記述中に保存されます。重要なのは実際のメモリ中にあるデータであり、dsSQLFldDesは取り出し用のコピーに過ぎません。したがって、prAdjSQLColWideが行なっている方法でポインタ(ptrFldDes1)を調整し(ITS00187Rの241.00行目および241.15行目)、調整する必要のある実際のメモリ位置を探さなければなりません。
ITS00187R2が各カラムの幅を設定したら、prGetSQLHdrLine(ITS00187R2の55.22行目、ITS00187Rサービス・プログラムの243.00行目から255.00行目)を起動し、計算されたカラム幅に合うように整えられたカラム・ヘッダを、空白文字1つをはさんで付けて、1つのヘッダ行にデータをフォーマットします。便利なことに、prGETSQLHdrLineはデータを文字に変換する際、開始位置とカラム幅の配列を返します。
ヘッダを生成する方法については、それなりの意見を持っている人が多いようです。ですから、prGetSQLHdrLineをお使いになりたくないかもしれません。しかしながら、ヘッダが起動しているルーチンprGetSQLName、prGetSQLDspWidth、prSQLFldisNumberは役に立つということがおわかりいただけるでしょう。これらのルーチンはすべて、ダイナミックに割り当てられた記述子エリアで動作して、計算を実行します。(起動の例については、ITS00187Rサービス・プログラムの251.01、251.05、251.06の各行を参照してください。実際のルーチンの中身については、156.00行目から166.00行目、184.00行目から220.00行目、205.00行目から219.00行目を参照してください。)
ITS00187R2は、記述子エリアを使用して何かをフェッチする前に、返ってくる各フィールドとnullインジケータ用にポインタをセットしておく必要があります。(私はすべてのフィールドを隣接したメモリ領域に保存していますが、SQLは必ずしもそれを前提としていません。SQLはデータの各ビットに対してそれぞれ別々のポインタを必要としているだけです。)prBndSQLColルーチンは、それより以前に呼ばれたルーチンが返したメモリへのポインタを使用して、こうした雑用を処理します。各カラムのポインタを取得するには、ptrBufferに対するptrColDspを基にした変位の配列から変位を加えるだけです。
次に、prBndSQLColルーチンは、取得したポインタを記述子エリア中に用意された場所にポストします。(起動の仕方についてはITS00187Rの112.30行目および112.31行目、ITS00187R2のソースコードの54.00行目を参照してください。)また、束縛ルーチンもnullインジケータ用にポインタをセットしています(ITS00187Rの112.32行目から112.34行目)。
最後に、OPENプロシージャがカーソルをオープンし、ITS00187R2はフェッチを開始する準備が整います(ITS00187R2の55.02行目から55.09行目を参照してください)。このプログラムは、既にオープンされているカーソルについては一旦クローズして、再びオープンしてから、処理します。
ITS00187R2のFETCHアクション
フェッチはきわめて簡単です。ITS00187R2は記述子エリアを使用してデータをフェッチしますが、この記述子エリアは、prAllcSQLDAが返すポインタを基にした変数です。システムは、prBndSQLColが指定した記述子エリアに保存されているメモリ・アドレスに各カラムの値を移動します(ITS00187R2の63.00行目から64.00行目を参照してください)。
バッファ中では、行のデータはフェッチが返したデータと同じフォーマットとなっています。ptrBuffer上に変数を配置し、ptrColDspが指し示す配列からの変位を加えることで、このデータを元のフォーマットで取り出すことができます。(または、記述子エリア中のポインタが指し示すメモリ位置に移動しても取り出せます。)prGetSQLFldDspが返してくるデータ構造には、バッファ中のフィールドのサイズがバイト数で含まれています。(ITS187CPYの.03行目、dsSQLFldDesのfd_BuffSizeを参照してください。)しかしこうした値にはすべて上限があることがわかりました。結局、フィールドから値を取り出したとき、このフィールドをどうしたらよいのでしょうか。
私は、prGetSQLDisplayの方がよいと思います(ITS00187Rの114.00行目から134.54行目を参照してください)。prGetSQLDisplayは、特定のカラム用のデータが画面上やプリンタに綺麗に表示/印刷されるようにフォーマットして返してくれます。このルーチンは、カラム用にこのルーチン(またはプログラマ)が決めた固定幅に返された、可変文字列の幅を設定します。ITS00187R2は、このルーチンを直接は使用しませんが、その代わりに、prGetSQLDtlLineを呼び出します(ITS00187Rの255.08行目から255.66行目を参照してください)。このルーチンは、prGetSQLDisplayが返してきたフィールドの中をループして、空白文字を挟んで各カラムを連結します。
あるいは、prGetSQLCharを呼び出す方法もあります(ITS00187Rの134.60行目から136.80行目を参照してください)。このルーチンは、prGetSQLDisplayと同じように動作しますが、RPGの組み込み関数を介してオリジナルのフォーマットにした文字データを返します。
ITS00187R2のCLOSEアクション
カーソルの処理が終了したら、カーソルをクローズしなければならないのはご存知でしょう。また、ダイナミックに割り当てたメモリもすべて開放しなければなりません。prDeallcSQL187はこの雑用をこなしてくれます。prDeallcSQL187をキャンセルのルーチンに入れておけば、うっかりこの処理をし忘れることはなくなるでしょう(ITS00187R2の22.11行目から22.15行目を参照してください)。991.00行目から998.00行目のLISTSQLは、キャンセル・ルーチンの例です。LISTSQLは、通常の終了処理にはsrReturnで呼び出されていますが(LISTSQLの293.28行目)、異常終了の際は290.09行目で登録されています。
