簡単な3つのSQLユーティリティー
カウント、データ・エレメント記述などを返す
プログラムを一度に 1 つずつ作成するのは間違いだったと気付いたのはずっと以前のことです。同じコードが 2 箇所で必要になった場合、「これはプロシージャーでなければならないのか」と自問します。必要なコードは異なっていても、他のコードとほぼ同じなら、「これをユーティリティーにできないだろうか」と考えます。
動的 SQL はユーティリティーにソリッド・サポートを提供していますが、多少手ごわいかもしれません。戻されているデータのフォーマットがわからない場合、ポインターの配列をもてあそび、そのフォーマットの正体をデコードする必要があります。SystemiNEWS誌の 2007 年 3 月号で、私はそれらすべての関数を処理する場合に役立つツールを公開しました (「CL のサブファイル・コマンド」 を参照)。以降、そのツールの機能拡張を続けました。現在このツールは複数のパーツで構成され、かなり複雑になっています。CL プログラムのプロンプト画面で顧客番号を確認するなど、単純な作業に必要なツールを超えたツールとなっています。
多くのユーザーが、動的 SQL の実行や CL からの SQL の実行に QShell の DB2 コマンドを使用しています。この手法はパワフルで柔軟性があるものの、QTEMP を処理したり、オーバーライドに応答したりせず、データ受信時にコツが必要になります。
ここでは、動的 SQL を使用する単純で、わかりやすい 3 つのユーティリティーについてお話します。組み込み SQL は初めてというユーザーでも理解できると思います。
- ITSQL3RI ― カウントを返す
- ITSQL2RI ― データ・エレメントを返す
- ITPRMPRI ― 汎用検索ウィンドウからの選択肢を返す
実際にその場まで行かなくても、ツールが目の前にあるタスクにアプローチする場合、それらを簡単に変更できることがわかるでしょう。それらのツールは完全にネイティブで、QTEMP 上のデータで動作でき、通常のオーバーライドの影響を受け、結果を単純なパラメーター・インターフェースで返します。ただし、すべてのことを行うわけではありません。各ユーティリティーは、使いやすい方法で 1 つのことを行います。不明なデータ型やポインターの複雑さを回避するため、それぞれ明確に定義された 1 ビット・データを返します。
3 つのツールは CL から使用することを想定しているため、プログラムとして存在します。したがって、CL プログラムにバインドしなくても済みます。つまり、ツールは OPM RPG、CL、COBOL、RPG III だけでなく、ILE RPG からも動作します。2 つのデータ・リトリーブ・ツールは CL から使用するよう作成されていますが、汎用検索ウィンドウはどこにいても使用できます。
レコード・フォーマットを取得できないのはなぜ?
System iNetwork フォーラムで多くのユーザーが、あらかじめレコード・フォーマットがわからない場合に SQL でそのフォーマットを取得する方法を尋ねてきます。レコード・フォーマット全体を区別されていないメモリーの塊で要求しており、その状態で取り組もうとしています。
結局、MyTable フォーマットから Select * を使用して列を指定することで、特定の表で動的 SQL をコーディングでき、外部記述データ構造を提供して結果を受信します。ソート順と WHERE 節は完全に動的の可能性があります。読者の多くは、柔軟性ある照会プログラムを使用して、オーダー・マスターなど特定のデータベース表で、これを行ったことがあると思います。以下のように指定して、レコードのすべてのフィールドを返すことができます。
Exec sql Fetch Cursor1 into :dsCursor1;
ただし、SQL でレコード・フォーマット全体を受信しているように見えますが、実際は戻されたすべてのフィールドについて個別の編集を要求しています。RPG の SQL プリコンパイラーは、データベース表の外部記述に基づくデータ構造など、任意の宣言済みデータ構造で各変数を見つけます。SQL はレコード・フォーマットを取得しているように見えますが、それは幻想にすぎません。プリコンパイラーは、使用する必要がある特定のデータ構造に対してコンパイルする必要があります。ランタイム時に外部記述データ構造を自力でセットアップすることはできません。2007 年 3 月に公開したツールで私が行ったように、プログラマーはそれができます。ただし大変なので、必ずしも必要ではないと思います。
必要なものは当てになるもの
SQL には幅広い種類のキャストと文字列処理関数があるため、予想どおりのフォーマットでデータを返す SQL ステートメントを構成できます。そのようにして、どの表を使用するのか知らなくても、結果生じる型を処理するユーティリティーを作成できます。複雑なアプリケーションの場合、このアプローチでは SQL 構成が難しくなり過ぎますが、多くの一般的なケースでは問題なく動作します。
ごく一般的なタスクの 1 つに、ユーザー入力の検証があります。通常は、ユーザー入力はマスター・ファイルか制御ファイル中の値に対して検証します。ユーザーは、顧客マスターと照合する必要がある顧客番号を入力しているでしょう。あるいは、会社の部門か保管番号を入力しているでしょう。SQL に何かが存在することを検証する方法の 1 つとして、カウントを選択する方法があります。カウントがゼロより大きな場合、データ項目が存在します。カウントにより、受信する内容 (絶対にヌルではない整数) がわかります。ツール ITSQL3RI (図 1、前半と後半) はそのカウントを返します。
ユーティリティーは CL で扱いやすいため、2 進整数ではなくパック 10 進数を返します。CL プログラムは SQL ステートメントを構成し、それをユーティリティーに渡して、戻されたカウントと SQL コードを受信します。いつもの SQL の場合のように、SQL コードか SQL 状態をチェックする必要があります。エラーが発生した場合、カウントで負の 1 (-1) を返します。
図 2 はこのツールでカウントを取得する CL の例を示しています。SQL を構成するステートメントがアポストロフィを繰り返して、CL コードにそれらが文字列の一部であることを示している様子に注目してください。ITSQL3RI の内部を見てみると、どのように動作しているかわかります。ステートメントには、RPG プログラムに ASTMT という変数、SQL に C1STMT という変数がある点に注目してください。declare ステートメントと prepare ステートメントは両方とも SQL 名を参照しています。また prepare ステートメントは RPG 変数も参照しています。動的組み込み SQL はこのようにして SELECT ステートメントと連携する必要があります。カーソルを参照する任意の動的 SQL プログラムは、ステートメント名に基づいてカーソルを宣言し、プログラム変数からステートメント名を準備し、カーソルを開いて、そこから取り出す必要があります。SELECT ステートメントで動的 SQL を使用する場合は必ず、これらの手順に従う必要があります。静的 SQL の場合のように、単純に SELECT INTO を実行することはできません。カーソルは ENDMOD で宣言されているため、たとえプログラムが LR をセットするか閉じていなくても、呼び出し元に戻るとカーソルを失います。 別の方法でこのルーチンで同じことを行うには、次のように SQL を構成します。
Select 1 from CustMaster if custnumber=12345;
顧客番号 12345 の顧客がいない場合、SQL は何も検索せず、SQL コード 100 を返します。このコードでは、ルーチンはカウントで 0 を返します。顧客 12345 が存在する場合、ルーチンは 1 を返します。繰り返しますが、CL でチェックするのはカウントの 0 またはゼロ以上の数字です。
整数だけが人生ではない!
整数以外の数字を受信したい場合があるでしょう。そのために、フィールドを 1 つ返す別のユーティリティーがあります。ITSQL2RI (図 3、前半と後半) はあなたが選択した 15 位置パック整数、15,5 パック数、または 500 バイト文字列を返すことができます。ユーティリティーはどのフォーマットを使用するか判断しようとはしません。ITSQL2RI を呼び出す CL プログラムが、ユーティリティーが渡す第 2 パラメーターにどのフォーマットが必要かをユーティリティーに指示します。整数に I、小数点以下の数字に N、文字列に A が選択されます。これらをごちゃまぜにすると SQL コードでエラーになりますので、必ず確認してください。
一般的に、文字フォーマットを使用します。複数の列が必要な場合、それらに専念できます。CL プログラムに戻り、サブストリング関数 %SST を使用してフィールドをプルアウトします。この特定のツールは、取得された最初の行からのデータだけを返し、すぐにカーソルを失います。通常、CL プログラム内で行いたいことはこれだけです。このアプリケーションを繰り返し呼び出して、複数の行を返すよう簡単に変更できます。しかし、そのためにはアプリを *ENDACTGRP にスコープし、最後にもう一度それを呼び出して、カーソルの保留解除を処理する必要があります。これは、通常私が使用する方法には不便です。
想像できると思いますが、ITSQL3RI ツールと ITSQL2RI ツールを使用して CL プログラムのデータを取得します。RPG には優れた組み込み SQL サポートがあるため、プログラムを SQLRPGLE に切り替えたくないが、SQL のデータ・エレメントを 1 つ取得したい場合を除いて、これらのツールをそこで使用する理由はほとんどありません。
ただし、ITSQL2RI は明らかにあまり使用されているとは言えません。これは式エバリュエーターとしての役割を果たすことができます。私の CL プログラムの多くはこのツールに依存しているため、SQL の豊富な日付関数を利用して、日付範囲を計算したり、他の日付計算をして自分のパラメーターを設定したりすることができます。SQL で任意の計算を構成し、SYSIBM/SYSDUMMY1 から選択する SQL ステートメントで実行できます。IBM はこの表には厳密に 1 行しか入らないことを保証しています。この目的のためだけに設計されているのです。したがって、このツールと SQL ステートメントを合わせて使用して、自分で作成したものも含めて定数と SQL 関数を伴う計算を実行できます。図 4 は日付範囲を取得している CL スニペットを示しています。
さあ、次はちょっと複雑になります。
最後にコーディングするはずの検索ウィンドウ
さて、ITPRMPRI (コード・バンドルを参照) はおそらく検索ウィンドウに関する私のニーズのほとんどを処理するとは思いますが、600 万行になるアドレス帳には相変わらず従来のウィンドウを使用します。ウィンドウ・ユーティリティーは CL または RPG で動作し、コマンド・ラインからでも動作します。スタンドアロン・オプションとしてメニュー上に掲げておきました。アプリケーション定義テーブルに入力することで、検索ウィンドウ・アプリケーションを作成します。アプリケーションにはそれぞれ、その特定のウィンドウを表示したいプログラムにより渡される 10 バイトの名前が付いています。選択内容を実行する SQL は、タイトル、列見出し、行のキーの開始位置と長さとともにテーブルに保管されています。
検索ウィンドウが必要な場合は、ITPRMPRI を呼び出して、アプリケーション ID と選択した値の 70 バイトの受信側パラメーターを 1 つか 2 つ渡します。最初のパラメーターには選択されたキー・フィールドしか入っていませんが、2 番目のパラメーターには行全体が入っています。必要なのはアプリケーション ID だけですが、キー・フィールド・リターン・パラメーターを渡して選択された値を受信する必要があります。ITPRMPRI はアプリケーション ID を使用して、アプリケーション定義を取得し、そこに保管されている SQL ステートメントを実行します。アプリケーションは、ユーザーの選択元となるサブファイルに結果セットを提示します。どの行にも行番号が付いています。行を選択するには、行番号または選択された行の隣にある X を入力します。他のサイトでは、行をクリックして選択を機能させたことがありますが、このやり方のほうが現在のユーザーにはなじみがありました。
アプリケーションは検索機能もサポートしています。これは、汎用ウィンドウがトラブル処理用に備えていた位置決め機能に置き換わるものです。「FIND」フィールドに文字列を入力すると、この文字列が行のどこかに含まれている行だけ表示するようリストが制限されます。データを一度に 2 つのサブファイルに読み込むことで、この機能をサポートしています。常に隠されたサブファイルの 1 つは、サブセットを参照しているときにデータ全体のストレージ・ツールの役割を果たします。プログラムは、データベースに戻って検索を実行することはありません。この検索方法では、すべての行を一度に読み込む必要があります。その方法を 600 万行を含む表に使用しないのはそのためです。
アプリケーションは、ユーザーに表示されたデータに組み込まれたキー・フィールドのみ返します。2 つのフィールドを選択しようと考えたことがありました。1 つは表示された行を表すフィールドと、もう 1 つはサブファイルの隠しフィールドを返すデータを含むフィールドです。しかし、そうするとアプリケーションを設定するときに SQL がさらに複雑になってしまいます。
表示行を構成するには、SQL は目的のフィールドをすべて文字に変換し、それらの文字を各列間の適切なスペース数とともに連結する必要があります。また SQL は表示行の書式設定をすべて処理します。一般的に、ステートメントを対話式 SQL で動作させ、DBU を使用してそれをアプリケーション定義テーブルの SQL フィールドに貼り付けます。ITPRMPRI に必要なパラメーターはアプリケーション名だけで、ルーチンはコマンド・ラインから実行するプログラムであるため、アプリケーションの設計中にこうして簡単にステートメントを実行し、列見出しとその詳細の位置が合っているかチェックできます。
変数の選択基準
私のショップでは、ある特定のツアーのすべての出発日を検索するといったことをするため、ウィンドウでマーカーや置換値を渡すことができます。SQL では、置換マーカーは次のようにコーディングされています。
select depart from toursched where tourid='<<TOUR>>'
置換値は切り取られており、マーカーがアポストロフィの中にあるかどうかは問題にしていない点に注目してください。このアプローチはあらゆるケースに当てはまるものではありませんが、QMQRY のメソッドを使用するよりは簡単です。文字変数を渡す前に、アポストロフィでラップしなくてもすみます。CL では、マーカーを定数として渡しますが、置換値は全 50 バイトの変数として宣言する必要があります。そうしないと、浮遊データを選択する可能性があります。RPG では、単純にプロトタイプに依存できます。
置換マーカーは必要ないですが、最大 4 つまで指定できます。置換マーカーは、タイトルや列見出し、さらに SQL にも常駐できます。例えば、タイトルにマーカーを配置すると、一般的なタイトルではなく、「ライン川地域の城へ出発」などと、より記述的なウィンドウ・タイトルを使用できます。マーカーは複数回発生する場合があり、同じマーカー名は同じ置換値で表示されるたびに置き換えられます。マーカーをプロンプト・プログラムに渡す場合、山括弧は取り除いてください。
一般的に、コマンド・ラインのテストを簡単にするため、アプリケーションは置換マーカーではなく定数を使用して動作させています。それが気に入ったら、置換マーカーを挿入して、呼び出しプログラムからテストします。
以下の 2 つの call ステートメントはプロンプト・ウィンドウを起動します。最初のステートメントは必要なアプリケーション ID とオプションのリターン・キーを渡すだけです。2 つ目のステートメントもパラメーターを渡して行全体、マーカー「PGM」、&REP1 変数に保存された置換値を取得します。50 バイトで &REP1 を宣言します。値が定数であっても、50 バイト変数として渡します。PGMSUPL アプリケーションでは、マーカーと置換値の組み合わせは 1 つしか使用しませんが、最大 4 つの組み合わせを渡すことができます。
CALL ITPRMPRI ('DSPOBJD' &KEY )
CALL ITPRMPRI ('PGMSUPL' &KEY &RETURN 'PGM' &REP1)
便利だとわかった場合に備えて、ユーザー定義の表関数でアプリケーションを実行してみました。うまくいきました。SELECT ステートメントは、最大 70 文字のフィールドを 1 つ返す有効な SQL でなければなりません。実際、それより長い文字列を選択できますが、切り捨てられます。
アプリケーション・ウィンドウの例については、図 5 と図 6 を参照してください。図 6 では FIND 機能が採用されていることがわかると思います。図 7 は前述の 2 つの図のアプリケーションの定義を示しています。図 8 では、アプリケーション定義にマーカー変数があることがわかります。
試してみる
さあ、これらのツールをダウンロードして自分のショップで試してみてください。ファイル群には 2 つの SQL 検索ツール ITSQL2RI と ITSQL3RI の RPG プログラム、またプロンプト・プログラム ITPRMPRI、さらにディスプレイ・ファイルとプロンプト・アプリケーション定義ファイルの DDS が組み込まれています。これらのツールのシンプルな使いやすさが気に入るでしょう。もう検索ウィンドウをコーディングしないことに満足したら、記事「CL のサブファイル・コマンド」 をチェックしてください。リストでコーディング作業することはなくなりますよ。
