EGL prepare ステートメントを使用すると、SQL ステートメントが動的に構成されます。
function printExpensiveItems(maxPrice Price in)
tempItem Item;
open expensiveItems for tempItem with
#sql{
select
EGL.ITEM.ITEM_ID, EGL.ITEM.NAME, EGL.ITEM.IMAGE,
EGL.ITEM.PRICE, EGL.ITEM.DESCRIPTION
from EGL.ITEM
where
EGL.ITEM.PRICE >= :maxPrice
order by
EGL.ITEM.ITEM_ID asc
};
forEach (tempItem)
SysLib.writeStdout(tempItem.Name :: " " ::
tempItem.Description :: " $" :: tempItem.Price);
end
パラメーターより小さいか等しい価格の品目を返す場合は、どのような関数を使用するのでしょうか。あるいは、最大価格と最小価格との間の品目を返す場合は、どのような関数を使用するのでしょうか。open ステートメントで明示的な SQL を使用する場合は、SQL ステートメントの Where 文節にある比較がハードコーディングされるため、それぞれの可能性ごとに 1 つ、計 3 つの関数をコーディングしなければなりません。selectStatement STRING = "select * from EGL.ITEM where EGL.ITEM.PRICE >= 500";次に、prepare ステートメントを使用して、このストリングを SQL ステートメントに変換します。ステートメントの名前を指定し、オプションで、ステートメントが影響するのがどの SQLRecord パーツであるかを示すレコード変数も指定します。
tempItem Item; prepare preparedStatement from selectStatement for tempItem;これにより、get、open、または execute ステートメント内の明示的な SQL の代わりに、準備済みステートメントを使用できるようになります (execute については、後の演習でより詳細に学習します)。
open expensiveItems for tempItem with preparedStatement;
この方法でストリングから SQL ステートメントを作成するのは、SQL に精通したプログラマーの上級テクニックです。EGL は設計時に SQL ステートメントが正確であるかどうか検査しないため、実行時にストリングが正しい SQL ステートメントに解決することを確認する必要があります。以下は、準備済みステートメントの処理でよく生じるエラーのリストです。
tempItem Item; maxPrice Price = 500; selectStatement STRING = "select * from EGL.ITEM where EGL.ITEM.PRICE >= :maxPrice"; prepare preparedStatement from selectStatement for tempItem;この場合、実際の SQL ステートメントには、全くそのままのストリング :maxPrice が含められます。そのストリングがホスト変数として使用されて maxPrice 変数の値で置換されることはありません。結果は、誤った SQL ステートメントとなります。
tempItem Item; maxPrice Price = 500; selectStatement STRING = "select * from EGL.ITEM where EGL.ITEM.PRICE >= " :: maxPrice; prepare preparedStatement from selectStatement for tempItem;この場合、maxPrice 変数の値は EGL がストリングを作成するときに解決され、次の SQL ステートメントを作成します。
select * from EGL.ITEM where EGL.ITEM.PRICE >= 500後でこの演習で、準備済みステートメントで変数を使用する別の方法を学習します。
incorrectSQL STRING; incorrectSQL = "select * from EGL.ITEM where"; incorrectSQL ::= "EGL.ITEM.PRICE >= :maxPrice"; incorrectSQL ::= "order by EGL.ITEM.PRICE";この例からは、次の誤った SQL ステートメントが作成されます。
select * from EGL.ITEM whereEGL.ITEM.PRICE >= :maxPriceorder by EGL.ITEM.PRICEこのステートメントには、WHERE の後と ORDER BY の前にそれぞれスペースが必要です。
また、準備済み SQL ステートメントは、明示的な SQL と同様に、一緒に使用する EGL ステートメントに適合している必要があります。例えば、EGL get および open ステートメントには結果セットが必要です。これらのステートメントのいずれかと一緒に準備済みステートメントを使用するには、SQL コードが結果セットを返す必要があります。このため、SQL INSERT または DELETE ステートメントを準備し、そのステートメントを get または open と一緒に使用することはできません。
この演習では、前回の演習で作成したプログラムを、2 つのパラメーター (そのうちの 1 つまたは両方を NULL にすることが可能) を受け入れるように変更します。この関数により、非 NULL のパラメーターに基づいてクエリーが作成され、ある一定の範囲の価格の品目が戻ります。
Completed query: select * from EGL.ITEM where EGL.ITEM.PRICE between 50.00 and 500.00 order by EGL.ITEM.PRICE Serial Mouse Great little mouse. All kinds'a applications. $65.22 Keyboard Got QWERTY? If not, go get this awesome flat keyboard. $78.99 Watch Keep time - and look rakish! $99.01 Shredder Don't let that politically-sensitive document fall into the wrong hands! $198.99 Combination Safe Keep your valuables safe and secure. Put 'em in here. $222.22次の例に示すように、パラメーター値を編集して、NULL 値を含むさまざまな範囲を試行できます。
function main()
minPrice, maxPrice Price?;
minPrice = 500;
maxPrice = null;
try
printItemRange(minPrice, maxPrice);
onException(exception SQLException)
handleDBException(exception);
end
end
値として 500 と NULL を渡すと、次のような結果が生じます。Completed query: select * from EGL.ITEM where EGL.ITEM.PRICE >= 500.00 order by EGL.ITEM.PRICE Desktop PC Lots'a power, at the right price. Includes monitor. $888.55 Laptop PC Great little machine. Take it anywhere with you. $1111.11
これで、selectItems.egl ファイルのコードが完成しました。 このファイル内にエラーがある場合 (赤の X 記号でマークされている場合) は、演習 4A で完成した selectItems.egl ファイルのファイルに記載されているコードと、作成したコードが一致していることを確認してください。
前の例では SQL ステートメントは、Where 文節内の変数が固定されていたという点で制限がありました。どの変数が使用されるかわかる前であっても、準備済みステートメントを作成することができます。このタイプのステートメントはより複雑ですが、ステートメントを準備しておいて後で詳細を決めたい場合には柔軟性があります。
selectStatement STRING = "select * from EGL.ITEM where "; ... selectStatement ::= "EGL.ITEM.PRICE between " :: minPrice :: " and " :: maxPrice :: " ";準備済みステートメントを作成する際に、疑問符 (?) をステートメントに置いて、その値が後で埋められることを示すこともできます。
selectStatement2 STRING = "select * from EGL.ITEM where " :: "EGL.ITEM.PRICE between ? and ? " :: "order by EGL.ITEM.PRICE"; tempItem Item; prepare preparedStatement2 from selectStatement2 for tempItem;これで、準備済みステートメントに 2 つのホスト変数を置く場所ができました。これらの場所は、BETWEEN 分節内の疑問符 (?) によって示されています。準備済みステートメントを使用する準備ができたら、using ステートメントを使用して値を挿入します。 例えば、価格が 5 から 500 の間の行を検索するには、リテラルの 5 と 500 をステートメントに渡します。
open expensiveItems2 for tempItem with preparedStatement2 using 5, 500;もちろん、using キーワードと一緒に変数を使用することもできます。
open expensiveItems2 for tempItem with preparedStatement2 using minPrice, maxPrice;
function printRanges()
tempItem Item;
selectStatement string = "select * from EGL.ITEM where " ::
"EGL.ITEM.PRICE between ? and ? " ::
"order by EGL.ITEM.PRICE";
prepare preparedStatement from selectStatement for tempItem;
SysLib.writeStdout("¥nPrinting values between 0 and 200");
open expensiveItems for tempItem with preparedStatement using 0, 200;
forEach(tempItem)
SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
" $" :: tempItem.Price);
end
SysLib.writeStdout("¥nPrinting values between 200 and 500");
open expensiveItems for tempItem with preparedStatement using 200, 500;
forEach(tempItem)
SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
" $" :: tempItem.Price);
end
SysLib.writeStdout("¥nPrinting values between 500 and 1,000");
open expensiveItems for tempItem with preparedStatement
using 500, 1000;
forEach(tempItem)
SysLib.writeStdout(tempItem.Name :: " " :: tempItem.Description ::
" $" :: tempItem.Price);
end
end
ホスト変数をこのように使用すると、前のセクションのプログラムを再作成でき、起こり得るケースごとにステートメントを変更することなく 1 つの準備済みステートメントを使用できます。
有効な準備済みステートメントの作成は複雑ではありますが、作成されたステートメントは明示的な SQL でハードコーディングされたステートメントよりも柔軟性があります。後の演習で、SQL ステートメントをカスタマイズするもう 1 つの方法である、SQLRecord パーツにデフォルトの SQL コードを設定する方法を学習します。