パッケージ化されたクエリーの入力パラメーターの指定
データベース固有のクエリーには、入力パラメーターは必要ありません。ただし、パッケージ化されたクエリーを作成するときに、データベース固有のクエリーを実行および送信する前に挿入する1つ以上の番号付き入力パラメーターを定義できます。データベース固有のSQLの入力値ごとに、Studioの[Parameters(パラメーター)]パネルエディターを使用して、パッケージ化されたクエリーに入力パラメーターを作成する必要があります。
たとえば、データベース固有のクエリーを使用してテーブルから行をフェッチしても、特定の行を識別するためにIDが必要になる場合があります。パッケージ化されたクエリーでは、実行時に挿入するID入力パラメーターまたはその他のパラメーター値を簡単に定義できます。
TDV Serverは、パッケージ化されたクエリーを実行するときに、データベース固有のクエリーに実際の値を挿入してから、実行できるようにパッケージ化されたクエリーをデータソースに送信します。
入力パラメーターは名前とデータ型で構成され、両方ともパラメーターを定義するときに指定します。クエリーの実行時にパラメーター名が表示され、入力値を入力するように求められます。パラメーター名は、データベース固有のクエリーでは効果がありません。パッケージ化されたクエリーのユーザーによって使用されます。ただし、指定するデータ型は、値がデータベース固有のクエリーに挿入されるときにどのようにフォーマットされるかに影響します。
データ型
パッケージ化されたクエリーは、次の入力データ型をサポートします。
DECIMAL, DOUBLE, FLOAT, NUMERIC, BIGINT, BIT, INTEGER, SMALLINT, TINYINT, CHAR, LONGVARCHAR, VARCHAR, DATE, TIME, TIMESTAMP
入力置換
パッケージ化されたクエリーでは、{0}から{N‑1}までの番号が付けられた置換パターンを使ってN個の入力を定義できます。
入力置換パターン
パッケージ化されたクエリーでは、番号が付けられた入力ごとに2つの異なる入力置換パターンのいずれかを使用します。SQLの最初の行は<version 2>で始まる必要があります。
• {N}—番号付きの入力は、変更なしでデータ値に置き換えられます。
• {N:string-sql-literal}—VARCHARおよびTIMESTAMPのデータ型の場合、番号付きの入力プレースホルダーは置換文字列値に置き換えられ、一重引用符で囲まれます。
一重引用符が、置換される文字列値に含まれている場合、2番目の一重引用符で自動的にエスケープされます。
Null値に置き換えられます。
入力を必要としないパッケージクエリーには、置換パターンや置換プレースホルダーが必要ありませんが、クエリーには、少なくとも1つの列を持つカーソルである出力パラメーターが1つだけ含まれている必要があります。
STRINGおよびDATEデータ型を使用する場合は、入力置換パターンの前後に一重引用符を使用する必要があります。このルールは、数値データ型には適用されません。
入力パラメーターの評価
このセクションでは、パッケージ化されたクエリーの入力パラメーターが実行時にどのように評価されるかを示します。最初の6つのクエリー例は有効です。最後の2つのクエリー例は無効です。
有効なクエリー1: 単純な置換
次の例の{0}は文字列、{1}は数値です。パッケージクエリーは次のように定義されます。
<version 2>
SELECT customer.balance
FROM customer
WHERE customer.name = '{0}' AND customer.id = {1}
文字列のパターンは一重引用符で囲まれています。1番目の入力値により{0}の出現箇所がすべて置き換えられ、2番目の入力値により{1}が置き換えられるというように置き換えられます。
有効なクエリー2: 複数の置換
それぞれの入力置換パターンはパッケージ化されたクエリー内に複数回出現することができます。
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0} AND customer.zip = {2})
OR (customer.id = {1} AND customer.zip = {2})
置換前のパッケージ化されたクエリー
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0} AND customer.zip = {2})
OR (customer.id = {1} AND customer.zip = {2})
置換後のパッケージ化されたクエリー
1番目の入力値101、2番目の入力値102、customer.zip値94403のように実際の入力値が与えられた場合、置換は次のようになります。
<version 2>
SELECT
customer.name, customer.balance
FROM
customer
WHERE
(customer.id = 101 AND customer.zip = 94403)
OR (customer.id = 102 AND customer.zip = 94403)
有効なクエリー3: 置換を停止するためのエスケープ文字
'{i}'などのパターンを保持する必要がある場合は、バックスラッシュ(\)を使用して、中括弧の開始と終了をエスケープします('\{i\}')。
置換前のパッケージ化されたクエリー
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0}
AND (customer.note = 'preserved \{1\}input')
OR (customer.id = {1} AND customer.zip = {2}
置換後のパッケージ化されたクエリー
前の例と同じ入力値を置換します。
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = 101
AND (customer.note = 'preserved {1} input')
OR (customer.id = 102 AND customer.zip = 94403
注意: バックスラッシュ(\)は、元のクエリーから削除されます。2番目の中括弧のエスケープは任意です。
有効なクエリー4: エスケープ文字のエスケープ
バックスラッシュが変更してはならない有効な文字列(たとえば、'\{i\}')の一部である場合は、もう1つのバックスラッシュ文字('\\{i\\}')を追加します。
置換前のパッケージ化されたクエリー
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0}
AND (customer.zip = '\\{2\\}')
OR (customer.id = {1} AND customer.zip = {2})
置換後のパッケージ化されたクエリー
前の例と同じ入力値を置換します。
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = 101
AND (customer.zip = '\{2\}')
OR (customer.id = 102 AND customer.zip = 94403)
有効なクエリー5: {N:string-sql-literal}オプション
クエリー入力が一重引用符を含む文字列の場合、入力プレースホルダーを{N:string-sql-literal}とすることで、一重引用符で囲み、パッケージ化されたクエリーによってエスケープして、置換後に2つの一重引用符になるようにする必要があります。
置換前のパッケージ化されたクエリー
<version 2>
SELECT customer.balance
FROM customer
WHERE
customer.name = {0:string-sql-literal} AND customer.id = {1}
置換後のパッケージ化されたクエリー
1番目の置換値は「Michael’s son」で、2番目は「123」です。
SELECT customer.balance
FROM customer
WHERE
customer.name = ’Michael’’s son’ AND customer.id = 123
有効なクエリー6: 数値の別のエスケープ例
クエリー入力に「{55}」のように中括弧で囲んだ数値を含める場合は、各中括弧をバックスラッシュでエスケープします。「{the Second}」のように中括弧で文字列を囲む場合は、中括弧をエスケープする必要はありません。
置換前のパッケージ化されたクエリー
<version 2>
SELECT customer.balance
FROM customer
WHERE
customer.id = {0}
AND customer.name = ‘George {the Second}’
AND customer.id = ‘\{55\}’
置換後のパッケージ化されたクエリー
SELECT customer.balance
FROM customer
WHERE
customer.id = 123
customer.name = ‘George {the Second}’
AND customer.id = ‘{55}’
無効なクエリーA
次のクエリーは、置換パターン入力{1}がクエリーにないため無効です。
SELECT customer.name, customer.balance
FROM customer
WHERE customer.id = {0} AND customer.status = {2}
無効なクエリーB
次のクエリーは、データベース固有のクエリーに4つの入力を定義しているが、パッケージ化されたクエリーで3つの入力置換しか定義していないため無効です。4番目のSQL入力プレースホルダー{3}によって、クエリーが無効になります。入力パラメーターをもう1つ定義する必要があります。さらに、customer.zipは整数であるため、一重引用符で囲む必要はありません。
SELECT customer.name, customer.balance
FROM customer
WHERE
customer.id = {0} AND customer.zip = '{1}'
AND customer.status = {2} AND customer.email = {3}