Perform JDBC Query アサーション

Perform JDBC Query アサーションは、外部データベースにクエリを実行し、後ほどクエリ結果を使用するために使用されます。 クエリ結果はこのアサーションによって作成されたコンテキスト変数に格納されます。
9-5
Perform JDBC Query
アサーションは、外部データベースにクエリを実行し、後ほどクエリ結果を使用するために使用されます。 クエリ結果はこのアサーションによって作成されたコンテキスト変数に格納されます。
JDBC Query アサーションで複数値のコンテキスト変数の使用をサポートするには、Manipulate Multivalued Variable アサーションを使用してこのような変数の値を生成できます。
JDBC クエリを実行するには、JDBC 接続の管理タスクで JDBC 接続を設定しておいてください。
警告:
一般的に Gateway の MySQL データベースへの接続は作成しないでください。 このデータベースに書き込むどのクエリも Gateway を動作不能にする可能性があります。
Perform JDBC Query アサーションは、「テスト」中でもさまざまなデータベースに書き込むことができます。 現在行っている変更は、JDBC 接続のプロパティで元に戻すことができないため、注意が必要です。
サポートされていない機能
以下の機能が Perform JDBC Query アサーションによって現在サポートされていないことに注意します。
  • 関数およびプロシージャは DB2 上でサポートされていません。
  • データベース名が JDBC 接続 URL で指定される場合に限り、関数およびプロシージャは MySQL 上でサポートされます。 DataDirect ではなくネイティブ MySQL ドライバを使用するときにのみサポートされます。
  • 入れ子の関数呼び出しがある関数およびプロシージャはサポートされていません。
  • PL/SQL ブロックはサポートされていません。
  • オーバーロード プロシージャまたは関数の呼び出しはサポートされていません。
  • Data Direct MySQL ドライバを使用した、MySQL Enterprise Edition での関数の呼び出しはサポートされていません。
  • ネイティブ ドライバを使用した Oracle での関数の呼び出しはサポートされていません。
  • DataDirect ドライバを使用した、ブール値を返す関数の呼び出しはサポートされていません。
  • 小文字の名前を持ったプロシージャおよび関数は Oracle でサポートされていません。
  • 32 KB よりも大きい値を持つ NCLOB または NBLOB を返す Oracle 内の関数およびプロシージャは、
    Layer7 API Gateway
    から呼び出すことができません。
  • ブール パラメータ BOOLEAN および BOOL はネイティブ MySQL ドライバに対してサポートされていません。
このアサーションによって作成されるコンテキスト変数
Perform JDBC Query アサーションは、クエリ結果を使用して以下のコンテキスト変数を設定します。 デフォルト
<prefix>
は「jdbcQuery」で、アサーション プロパティで変更できます。
変数
説明
<prefix>.<column_name>
SQL クエリで指定された列名を返します。 1 つの結果セットが返される場合、この変数が作成されます。
<prefix>.
resultSet1
.<column_name>
ストアド プロシージャが複数の結果セットを返す場合、「
resultSet1...N
」が変数の名前に追加されます。
<prefix>
.
queryresult.count
(SELECT クエリを使用する場合に)クエリによって返されたレコードの数または(非 SELECT クエリを使用する場合に)クエリによって影響を受けたレコードの数を返します。 この変数は常に作成されます。
<prefix>
.
xmlResult
プロパティで[
Generate XML Results
]チェック ボックスがオンのときに XML 結果を返し、作成されます。
<prefix>
.
multipleResultSet.count
この変数は、複数の結果セットがあるときに設定されます。 これは、呼び出されたプロシージャが複数の結果セットを返すときに発生します。 この変数は、単一の結果セットのみがある場合、設定されません。
: OUT パラメータも設定されている場合、それらは 1 つの結果セットと見なされます。
<prefix>
.
multipleResultSet.queryresult.count
この変数は、複数の結果セットがあるときにのみ設定されます。 それが設定される場合、すべての結果セットの合計として結果の数が含まれます。
: OUT パラメータも設定されれば、これらは 1 行と見なされます。
<prefix>.<out_parameter>
プロシージャからの OUT パラメータの名前を返します。
<prefix>
.return
<prefix>
.RETURN_VALUE
これらの変数は、関数を呼び出すことによって、DBMS によって異なる変数の名前で設定されます。
ポリシー利用中に、Gateway は、1 つの列名あたり 1 つの複数値のコンテキスト変数を作成します。 複数値のコンテキスト変数内の値の数は返されたレコードの数に対応します。
: コンテキスト変数で SQL 列名以外の名前を使用する場合、アサーション プロパティでマッピングを指定します。
結果セット変数および複数結果セットの理解
Perform JDBC Query アサーションは複数の結果セットをサポートします。 SQL クエリが実行されると(たとえば、「select * from my_table」)、「結果セット」が返されます。 この結果セットは、行の論理的なセットで、各行は一連の列で構成されます。 (たとえば)my_table から column_a および column_b を選択し、my_table に 10 行ある場合、結果セットには 10 行が含まれ、各行には列が 2 つあります。
プロシージャ(たとえば、「CALL MY_PROC」)を呼び出すと、プロシージャは複数の結果セットを返す可能性があります(まれですが、可能性はあります)。 これが発生すると、2 つの結果セットが返された場合、
<prefix>.multipleResultSet.count
変数は「2」に設定されます。 結果セット #1 に 10 行があり、結果セット #2 には 5 行がある場合、
<prefix>.multipleResultSet.queryresult.count
変数は「15」に設定されます。
たとえば、以下の結果セットを返すストアド プロシージャを考慮します。
group
id
name
value
set1
8
name8
test value8
set1
3
name3
test value3
group
id
name
value
set2
1
extra1
test1
set2
4
extra4
test4
group
id
name
value
set3
6
name6
test value6
set3
5
name5
test value5
これらは、(デフォルト プレフィックス「jdbcQuery」を使用して)返されるコンテキスト変数になります。
${jdbcQuery.resultSet1.group}
=
set1,set1
${jdbcQuery.resultSet1.id}
=
8,3
${jdbcQuery.resultSet1.name}
=
name8,name3
${jdbcQuery.resultSet1.value
=
test value8,test value3
${jdbcQuery.resultSet2.group}
=
set2,set2
${jdbcQuery.resultSet2.field1}
=
1,4
${jdbcQuery.resultSet2.field2}
=
extra1,test1
${jdbcQuery.resultSet2.field3
=
extra4,test4
${jdbcQuery.resultSet3.group}
=
set3,set3
${jdbcQuery.resultSet3.id}
=
6,5
${jdbcQuery.resultSet3.name}
=
name6,name5
${jdbcQuery.resultSet3.value}
=
test value6,test value5
${jdbcQuery.multipleResultSet.count}
=
3
アサーションの使用
  1. 以下のいずれかを実行します。
    • アサーションをポリシー作成ウィンドウに追加するには、「アサーションの追加」を参照してください。
    • 既存のアサーションの設定を変更するには、下記の手順 2 に進みます。
  2. アサーションを追加すると、[
    JDBC Query Properties
    ]ダイアログ ボックスが自動的に表示されます。アサーションを変更する場合は、ポリシー ウィンドウで[
    Perform JDBC Query
    ]を右クリックして[
    JDBC Query Properties
    ]を選択するか、ポリシー ウィンドウでアサーションをダブルクリックします。 このアサーションのプロパティが表示されます。
    : [SQL Query]パネルと[Context Variables Naming]パネルの間の分割バーを使用して、パネルの相対的サイズを適切に調整できます。
  3. 以下のようにプロパティを設定します。
    設定
    説明
    JDBC Connection
    Choose one connection
    ドロップダウン リストからクエリが実行される JDBC 接続を選択します。 接続がリストで表示されていない場合、ボックスに接続名を入力できます。 コンテキスト変数を参照することもできます。
    コンテキスト変数を接続名で指定した場合、[
    Test
    ]ボタンを使用して、接続をテストすることはできません。
    これらの接続の定義については、「JDBC 接続の管理」を参照してください。
    SQL Query(詳細については、以下の「SQL クエリのヒント」を参照)
    Convert Variables to Strings
    このチェック ボックスは、JDBC ドライバに送信される前の、コンテキスト変数の処理方法を指定します。
    • コンテキスト変数の内容を文字列に変換するには、このチェック ボックスをオンにします。 複数値のコンテキスト変数の場合、その内容はカンマで値が区切られて 1 つの値に連結されます。
    • 複数値または単一値のコンテキスト変数の各値をそのまま追加するには、このチェック ボックスをオフにします。 Perform JDBC Query アサーションが含まれるポリシーが実行されると、Gateway は複数値のコンテキスト変数の各値についてパラメータのリストを持ったステートメントを組み立てます。 これがデフォルトの設定です。
    Query Timeout
    タイムアウトになる前にクエリに対する応答をアサーションが待機する時間の長さ(秒単位)を入力します。
    デフォルト:
    0
    (ゼロ、つまり、以下に説明するように、Gateway 全体のタイムアウトを使用します)
    : (1)ここで入力された値は、
    jdbcqueryManager.maxGatewayStatementTimeout
    クラスタ プロパティによって定義された Gateway 全体のタイムアウト設定をオーバーライドします。 目的は Gateway のデフォルトより短いタイムアウト値を提供することです。 より長いタイムアウト値が入力された場合、それは無視され、Gateway のデフォルトが代わりに使用されます。
    (2)JDBC ドライバは、タイムアウトを設定するすべての呼び出しを無視するように設定されている可能性があります。 そのように設定されている場合、Gateway は、その JDBC 接続を使用して、任意のクエリのタイムアウトを制御できません。 詳細については、JDBC 管理者にお問い合わせください。 (3)応答がない DBMS へのキャンセル リクエストが無期限に待機しないようにするには、DataDirect ドライバを使用するときに、接続プロパティ
    EnableCancelTimeout
    を「true」に設定する必要がある可能性があります。
    SQL クエリ ボックス
    実行する SQL クエリを入力します。 非 Select DML クエリの使用法には注意してください。 Perform JDBC Query アサーション内にトランザクション管理はありません。 このアサーションが実行されると、結果はアサーションを囲むポリシー ロジックにかかわらず DBMS で永続です。 SQL クエリの値を必要に応じてコンテキスト変数に置き換えることができますが、クエリの他の部分はクリア テキストのままにする必要があります。 以下に例を示します。
    有効なクエリを以下に示します。
    SELECT column_name FROM table WHERE username = 'Bob' AND password = '123password'
    SELECT column_name FROM table WHERE username = ${request.user} AND password = ${request.password}
    以下は有効なクエリではありません。
    SELECT ${columnName} FROM ${tableName} WHERE username = 'Bob' AND password = '123password'
    SELECT ${columnName} FROM ${tableName} WHERE username = ${request.user} AND password = ${request.password}
    引用符でコンテキスト変数を囲まないでください。 これは SELECT および INSERT ステートメントの両方に適用されます。
    クエリの最大長は 4 KB(4096 文字)です。
    SQL クエリの詳細については、このトピック内の「SQL クエリのヒント」および「関数とプロシージャの使用例」を参照してください。
    Test
    SQL クエリが選択された JDBC 接続で有効かどうかを確認するには、
    [Test]
    をクリックします。 続行する前にクエリの結果を理解していることを確認する必要があります。 SQL クエリが有効かどうかを示すメッセージが表示されます。
    コンテキスト変数が接続名または SQL クエリのいずれかで使用される場合、テストは可能ではありません。
    Specify Schema
    このチェック ボックスは以下の条件下でのみ使用可能です。
    • プロシージャまたは関数の呼び出しが SQL クエリ テキスト ボックスで定義されている。
      かつ
    • データベースが Oracle または SQL Server である。
    SQL クエリ テキスト ボックスはクエリの一部としてスキーマ値を受け入れないので、スキーマを指定する必要がある場合はこのチェック ボックスをオンにし、隣接したフィールドにスキーマの名前(スペースのない文字列または単一値のコンテキスト変数である必要がある)を入力します。この値は、JDBC ドライバがデータベースから正しいメタデータを取得できるようにそのドライバに渡されます。
    SQL クエリがプロシージャまたは関数の呼び出しに対するスキーマ値を必要とする場合、このチェック ボックスをオンにし、スキーマの名前を入力します。コンテキスト変数を参照することもできます。 オブジェクトがパッケージ内で含まれている場合、SQL クエリ自体はパッケージを参照する必要があります。たとえば、以下のようになります。
    CALL mypackage.myfunction
    : クエリがエラー メッセージ
    「The database object either does not exist or the SQL query contains the object's schema」
    で失敗する場合、スキーマを指定する必要がある可能性があります。
    Context Variables Naming
    Save results to context variables
    このチェック ボックスは[Context Variables Naming]テーブルの上にあります。 このチェック ボックスを使用して、テーブルに指定されたコンテキスト変数への SQL 結果の保存をすばやく有効または無効にします。 ヒント: このチェック ボックスをオンにしておくことをお勧めします。 ただし、メモリ問題が発生している場合は、結果の保存を無効にすることを考慮します。
    • テーブルを通常どおり操作するには、このチェック ボックスをオンにします。コンテキスト変数を追加、編集、または削除できます。また、SQL 結果は指定された変数に保存されます。
    • コンテキスト変数への SQL 結果の保存を無効にするには、このチェック ボックスをオフにします。 これはテーブルおよびその編集コントロールを無効にします。 テーブルで定義されたすべての変数は残ります。
    (1)このチェック ボックスは、下の[Generate XML Result]チェック ボックスと無関係に動作します。 これにより、結果をコンテキスト変数に保存しないことを選択する場合でも、ユーザは ${
    <prefix>
    .xmlResult} 変数に入力できます。 (2)コンテキスト変数
    ${<prefix>.queryresult.count}
    ${<prefix>.multipleResultSet.count}
    、および
    ${<prefix>.multipleResultSet.queryresult.count}
    は、[Save results to context variables]チェック ボックスに関係なく、常に作成されます。 これらの変数については、「このアサーションによって作成されるコンテキスト変数」で説明しています。
    テーブル
    このテーブルでは、SQL 列見出しを別の名前にマッピングできます。 これは、作成されるコンテキスト変数の名前を変更します。 たとえば、よりわかりやすい変数名が必要な場合、または組織の命名基準に従う必要がある場合などがあります。 詳細については、「このアサーションによって作成されるコンテキスト変数」を参照してください。
    そのアサーションは複数の結果セットをサポートします。 詳細については、上記の「このアサーションによって作成されるコンテキスト変数」および「結果セット変数および複数結果セットの理解」を参照してください。
    マッピングを追加する方法
    Add
    ]をクリックします。 [Context Variable Naming]ダイアログ ボックスが表示されます。
    1. SQL
      列ラベル
      を入力します。 たとえば、「Column1」と入力します。
    2. Variable Name
      ]にマッピング宛先を入力します。 たとえば、「Cust_Acct」と入力します。
    3. OK
      ]をクリックします。 これは、
      ${<prefix>.Column1}
      ではなく、
      ${<prefix>.Cust_Acct}
      という名前のコンテキスト変数を作成します。
    マッピングを編集する方法
    1. 行を選択し、[
      Edit
      ]をクリックします。
    2. 必要に応じて、フィールドを変更します。
    3. OK
      ]をクリックします。
    マッピングを削除する方法
    1. 行を選択し、[
      Remove
      ]をクリックします。
    2. Remove
      ]をクリックして確定します。 名前は SQL 列名に戻ります。
    Prefix
    このアサーションによって作成されるコンテキスト変数に追加するプレフィックスを入力します。 このプレフィックスは、一意性を確保し、このアサーションのインスタンスがポリシー内に複数存在する場合に変数が互いに上書きされることを防ぎます。
    コンテキスト変数を参照することもできます。
    デフォルトのプレフィックスは
    jdbcQuery
    です。
    詳細については、「このアサーションによって作成されるコンテキスト変数」を参照してください。
    Other Settings
    Query Name
    必要に応じて、クエリの名前を入力します。 この名前は Policy Manager での表示用にのみ使用されます。
    Maximum records per query
    SQL クエリから返されるレコードの最大数を指定します。 デフォルト値は
    10
    です。 指定可能な値の範囲は、1 ~ 2147483647 で、
    jdbcquery.maxRecords.defaultValue
    クラスタ プロパティを使用して変更できます。
    Fail assertion if no results
    SQL クエリが結果を返さない場合にアサーションを失敗させる場合は、このチェック ボックスをオンにします。
    結果がない場合にアサーションの失敗を防ぐ場合は、このチェック ボックスをオフにします (アサーションは、その他の理由で、たとえば、無効なクエリが原因で失敗する可能性があります)。
    Generate XML Result
    コンテキスト変数に JDBC クエリの XML 結果を配置できます。
    • 設定されたすべての変数の XML 文字列表現をコンテキスト変数 ${
      <prefix>
      .xmlResult} に格納するには、このチェック ボックスをオンにします。
    • コンテキスト変数に XML 結果を配置しない場合は、このチェック ボックスをオフにします。 注: ${
      <prefix>
      .xmlResult} に以前に入力されている場合、内容は残ります。
    XML 結果の構造の例を以下に示します。
    <?xml version="1.0" encoding="UTF-8"?> <L7j:jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result"> <L7j:row> <L7j:col name="COLNAME" type= "JAVA_DATA_TYPE">DATA_VALUE</L7j:col> .... </L7j:row> .... <L7j:jdbcQueryResult>
    示されている型は XML 結果が取得された
    後の
    データ型で、実際のデータベース フィールドの型ではありません。
  4. 完了したら、[
    OK
    ]をクリックします。
SQL クエリのヒント
SQL クエリを入力する場合、以下に注意してください。
  • Perform JDBC Query アサーションによって作成される変数はすべて複数値です。 このため、サフィックス(つまり、セレクタ)が存在する場合、配列構文を使用できません。 同様に、変数「${jdbcQuery.return_value.
    millis
    }」は動作しません(「millis」は任意のサフィックスです)。
  • 詳細については、「複数値のコンテキスト変数の使用」にある「複数値の変数とセレクタ」を参照してください。
  • クエリが null 値を必要とする場合は、コンテキスト変数
    ${policy.nullvalue}
    を使用します。 この組み込み変数は、常に null に解決されます。
    : Oracle データベースについては、null 値は VARCHAR 型の空の文字列に同等です。 したがって、Oracle で null 値を渡す別の方法は空の文字列を渡すことです。 たとえば、
    nullFunction
    が VARCHAR パラメータをとるとき、以下は両方とも同じことをもたらす結果になります。
    func nullFunction""
    func nullFunction $ {policy.nullvalue}
  • クエリに、AVG( )、MAX( )、MIN( )、入れ子の SELECT などの SQL 関数が含まれる場合、「AS」キーワードを使用して戻り値のエイリアスを作成する必要があります。たとえば、以下のようになります。
    SELECT max(column_name) AS alias_name FROM table; SELECT column_name, (SELECT COUNT(*) FROM table1 WHERE conditions) AS alias_name FROM table2 WHERE conditions;
  • SQL クエリはスキーマを参照できません。 スキーマ値を指定する必要がある場合は、
    [Specify Schema]
    チェック ボックスをオンにし、そこに値を入力します。
    : スキーマを指定する機能は Oracle および SQL Server にのみ使用可能です。
    警告:
    Policy Manager は、ユーザが破壊的な SQL クエリを入力するのを
    妨げません
    。 そのようなクエリは、テスト中にさえ、ユーザのデータベースを後戻りできないほどに破損させる可能性があります。
  • 以下のようなエラー メッセージが表示される場合
    Query testing failed; Data truncation: Incorrect datetime value:
    <datetime_variable>
    for column 'date' at row 1
    クエリに渡される変数が引用符で囲まれていないことを確認します。 Gateway は、JDBC クエリを処理する際に自動的に引用符を含めます (対照的に、SQL クライアントに直接値を入力する場合は、必要な場所に引用符を手動で追加する必要があります)。
変数の文字列への変換
コンテキスト変数を参照する SQL クエリを書き込む場合、文字列に変換された値を使用するのか、RAW 値を使用するのかを決定する必要があります。
RAW 値を使用するとき、その値は JDBC ドライバに直接渡されます。その後、値がサポートされている場合、ドライバは必要に応じそれを変換します。
変数のタイプが JDBC ドライバによってサポートされているが、その他の変数の RAW 値を同時に使用する間に、その文字列値を使用する場合は、RAW 変数を文字列変数に変換するために新しい変数をまず作成する必要があります。 詳細については、上記の表の[Convert Variables to String]オプションを参照してください。
関数およびストアド プロシージャの使用
クエリ ステートメントには、関数およびストアド プロシージャへの呼び出しが含まれる可能性があります。 Gateway は、関数またはプロシージャのデータベース メタデータを調べることで、その関数またはプロシージャが入力、出力、または両方として必要とするパラメータを決定します。
ストアド プロシージャを呼び出すには、CALL または EXEC キーワードの後にプロシージャの名前、その後にプロシージャのパラメータを続けます。 関数を呼び出すには、FUNC キーワードを使用します。
重要:
プロシージャ名にはハイフン(-)を含めることができません。含まれている場合は、エラーが発生します。
プロシージャ/関数のパラメータはリテラル値、単一のコンテキスト変数、または複数値のコンテキスト変数として提供できます。 これらのパラメータは丸かっこで囲むことも(たとえば、「CALL myproc (param1, param2,.....,paramN)」)、囲まない(たとえば、「CALL myproc param1, param2, ....., paramN」)ことも可能です。
提供される必要があるプロシージャのただ 1 つのパラメータは IN または INOUT パラメータです。 Gateway は、プロシージャのメタデータに基づいて正しい OUT または INOUT パラメータの登録を自動的に処理します。
「SQL Query」テキスト フィールドのコンテキスト変数をプロシージャ(またはその他の SQL ステートメント)からの OUT 変数にバインドするメソッド/構文はありません。
関数を呼び出すには、関数およびそのパラメータの名前が後続する FUNC キーワードを使用します。 パラメータを提供する方法と同じルールが関数に適用されます。
プロシージャまたは関数は呼び出された後、その出力は自動的に設定されます。 以下の表では、関数を呼び出すときに各データベースの出力変数の名前のリストを示します。
データベース
デフォルト変数名
MySQL(ネイティブ ドライバでのみ)
return
Oracle
RETURN_VALUE
MS SQL Server
RETURN_VALUE
DB2(サポートされていません)
n/a
OUT/INOUT パラメータはすべて自動的に処理されます。 プロシージャまたは関数のいずれかを呼び出すときは、入力パラメータの型を考慮します。 Date、Timestamp、BLOB などの型が正しく機能するには、コンテキスト変数が正しい型(Date/Time または byte [])であることを確認する必要があります。 また変数を文字列に変換しないように Perform JDBC Query アサーションが設定されていることを確認します。 これにより、RAW 型を JDBC ドライバに渡すことができます。ドライバは、それがサポートする任意の変換を提供できます。
存在しない関数またはプロシージャを呼び出すと、パッケージ名も指定された場合にのみ例外がトリガされることに注意します。 たとえば、存在しない「mypackage.myfunction」を呼び出すと、例外がトリガされますが、「myfunction」の場合は例外はトリガされません。
Oracle データベースの場合、関数およびプロシージャ名は大文字にし、スペースまたは特殊文字を含めないようにしてください。
例外:
関数またはプロシージャの名前を引用符で囲んでいない場合は、名前に小文字が含まれている関数またはプロシージャを呼び出すことができます。
アプリケーション ユーザに返されるメッセージ
ユーザがアプリケーション ユーザ(スキーマ所有者としてではない)としてデータベースに接続されているときに、有効なストアド プロシージャまたは関数がパラメータなしで呼び出されると、以下のメッセージが返されます。
データベース
返されるメッセージ
MySQL
"query testing failed: [l7tech][MySQL JDBC Driver][MySQL]No database selected
Oracle
"query testing failed: [l7tech][Oracle JDBC Driver][Oracle]ORA-06564: object <object name> does not exist ORA-06512: at "sys.dbms_utility", line 156 ORA-06512: at line 1
MS SQL Server
"query testing failed: [l7tech][SQL Server JDBC Driver][SQLServer]Could not find stored procedure 'sp name'."
DB2
n/a(プロシージャおよび関数は、現在 DB2 でサポートされていません)
既知の問題
以下の既知の問題に注意します。
  • OUT パラメータが INOUT として処理される MS SQL データベースに既知の問題があります。パラメータが不適切に設定される結果になる可能性があります。 これを回避するには、クエリですべてのパラメータ(IN および OUT)を明示的に設定します。
  • OUT/INOUT パラメータを使用するストアド プロシージャは、変数
    ${jdbcQuery.queryresult.count}
    に対して常に「1」以上の値を返します。 これは、パラメータが結果で常に返され、アサーションが失敗しないからです。 ただし、アサーションは結果がない場合は失敗するように設定されています。
  • 32 KB よりも大きい値を持つ NCLOB または NBLOB を返す Oracle 内の関数およびプロシージャは、
    Layer7 API Gateway
    から呼び出すことができません。
関数とプロシージャの使用例
関数およびプロシージャを実行するためにキーワードを使用する方法を示すいくつかの例を以下に示します。
ヒント:
関数からの出力変数の名前はデータベースによって決定されます。下に表示された「outParameter」は単に例です。
  • FUNC キーワードを使用した関数の実行:
    FUNC [package].[function]([IN parameters]...)Sets jdbcQuery.[<outParameter>]
  • EXEC キーワードを使用したプロシージャの実行:
    EXEC [package].[procedure]([IN and INOUT parameters]...)
  • CALL キーワードを使用したプロシージャの実行:
    CALL [package].[procedure]([IN and INOUT parameters]...)Sets jdbcQuery.[<outParameter>] etc.
  • 「Specify Schema」テキスト フィールド:
    using multi-valued context variables
    FUNC [package].[function](${vars},${singleVar})
    is equivalent to
    FUNC [package].[function](${vars.1},${vars.2}....,${singleVar})
以下のものを含めて関数およびプロシージャを呼び出す方法の例を以下に示します。
  • プロシージャから値にアクセスする方法
  • プロシージャから 1 つ以上の出力結果セットにアクセスする方法
  • パラメータとして非プリミティブ型を提供する方法
以下の例は以下の変数を使用します。
  • ${myvars}
    =
    "multivalue1"、"multivalue2"
    - この複数値の変数には 2 つの値が含まれます。
  • ${myvar1} = "singlevalue1"
    - この変数には単一の値が含まれます。
  • ${myvar2} = "singlevalue2"
    - この変数には単一の値が含まれます。
関数の呼び出し
Oracle の関数定義の例(2 つのパラメータをとり、varchar2 値を返す):
CREATE or REPLACE FUNCTION MY_FUNC(a IN VARCHAR2, b IN VARCHAR2)RETURN VARCHAR2
この関数を呼び出した後、単一の出力変数セットがあります。 返される変数の名前は DBMS によって異なります。
この例では、設定されたプレフィックスが「jdbcQuery」でである場合、出力変数は次のとおりです。
jdbcQuery.RETURN_VALUE
複数値の変数で呼び出します。
FUNC MY_FUNC(${myvars})
単一の値で呼び出します。
FUNC MY_FUNC(${myvar1}, ${myvar2})
丸かっこなしで呼び出します。
FUNC MY_FUNC ${myvars} OR FUNC MY_FUNC ${myvar1}, ${myvar2}
リテラル値で呼び出します。
FUNC MY_FUNC "input1", 'input2' OR FUNC MY_FUNC ("input1", 'input2')
プロシージャの呼び出し
Oracle のプロシージャ定義例(3 つのパラメータをとる、そのうち 2 つは OUT パラメータ)
CREATE or REPLACE PROCEDURE MY_PROC (a IN VARCHAR2, b INOUT VARCHAR2, c OUT VARCHAR2, d IN VARCHAR2)
この関数を呼び出した後、2 つの出力変数セットがあります。 デフォルト値は、OUT 変数の名前によって異なります。 jdbcQuery が使用中のプレフィックスである場合、以下のコンテキスト変数が設定されます。
jdbcQuery.b
jdbcQuery.c
CALL および EXEC は交換可能です。 どちらも特定の意味を持ちません。両方とも、SQL クエリがプロシージャを等しく呼び出すことを示します。
複数値の変数およびリテラル値で呼び出します。
CALL MY_PROC (${myvars}, "d value")
複数値の変数および単一の変数で呼び出します。
EXEC MY_PROC (${myvars}, ${myvar1})
すべての変数が処理された後、値の数は、予期された入力パラメータの数と一致する必要があります。 OUT パラメータの位置は重要でありません。 各コンテキスト変数が評価された後、解決された値の数は入力パラメータの数に一致する必要があります。 値は、解決された順序に基づいて適用されます。
上記の例で、プロシージャは以下のランタイム値で呼び出されます。
multivalue1、multivalue2、singlevalue1
単一値の変数およびリテラル値で呼び出します。
CALL MY_PROC (${myvar1}, ${myvar2}, 'd value')
関数と同様、パラメータのまわりの丸かっこはオプションです。
日付型パラメータの使用
Oracle の関数定義例:
create or replace FUNCTION DATE_FUNC (param1 IN DATE) RETURN DATE
リテラル文字列日付値で関数を呼び出します。
func DATE_FUNC '2012-12-31 23:55:40.99'
Date/Time 型のコンテキスト変数を使用して、関数を呼び出します。
Set Context Variable date as Date/Timefunc DATE_FUNC ${date}
String 型のコンテキスト変数を使用して、関数を呼び出します。
${dateStr} = "2012-12-31 23:55:40.99"func DATE_FUNC ${dateStr}
[Convert Variables to String]チェック ボックスがオンの場合、Date/Time 変数は、Date/Time 変数のデフォルト フォーマットを使用して、文字列に変換されます。 DBMS で必要な形式で動作するには、関数でそれを参照するときに Date/Time を明示的にフォーマットする必要がある可能性があります。
Set Context Variable date as Date/Time func DATE_FUNC ${date.yyyy-MM-dd HH:mm:ss.SS}
上記の例で、Date/Time が実行時に解決される場合、それは実際に String に変換されます。 これは、[Convert Variables to Strings]が使用されている場合にのみ、必要です。
数値型の使用
Oracle の関数定義例:
create or replace FUNCTION NUMBER_FUNC (param1 IN NUMBER) RETURN NUMBER
リテラル数値で関数を呼び出します。
func NUMBER_FUNC 1243
Integer 型のコンテキスト変数を使用して、関数を呼び出します。
Set Context Variable integer as Integer to 5func NUMBER_FUNC ${integer}
String 型のコンテキスト変数を使用して、関数を呼び出します。
${integerStr} = "12345"func NUMBER_FUNC ${integerStr}
ブール値の使用
SQL Server 関数定義例:
CREATE or ALTER FUNCTION BOOL_FUNC( @a BIT, @b BIT)
リテラル ブール値で関数を呼び出します。
func BOOL_FUNC 'false', 'true'orfunc BOOL_FUNC 0, 1orfunc BOOL_FUNC '0', '1'
Integer 型のコンテキスト変数で関数を呼び出します。
Set Context Variable a as Integer to 0Set Context Variable b as Integer to 1func BOOL_FUNC ${a}, ${b}
String 型のコンテキスト変数で関数を呼び出します。
${falseStr} = "false"${trueStr} = "true"func BOOL_FUNC ${falseStr},${trueStr}or${aStr} = "0"${bStr} = "1"func BOOL_FUNC ${aStr},${bStr}
byte[] および BLOB 値の使用
関数定義例:
create or replace function BLOB_FUNC (a in BLOB) return BLOB
リテラル 16 進文字列を使用して呼び出します。
func BLOB_FUNC '0123456789abcdef'
String 型のコンテキスト変数を使用して呼び出します。
${hexString} = "0123456789abcdef"func BLOB_FUNC ${hexString}
「Encode / Decode」アサーションを使用して、16 進数に変換された String 値を使用して呼び出します。
Base16 Encode ${myvar1} into ${hexString}func BLOB_FUNC ${hexString}
カプセル化されたアサーションによって作成された byte[] コンテキスト変数を使用して呼び出します。
Output a byte[] variable called ${bytes} from an encapsulated assertionfunc BLOB_FUNC ${bytes}
null 値の使用
特殊な組み込み変数を介して SQL クエリで null 値を提供することができます。 また、null 値が含まれる可能性がある任意の既存の変数または複数値の変数を介して null 値を提供することができます。
事前定義済み変数
${policy.nullvalue}
は SQL クエリに null 値を渡すことをサポートします。 [Convert Variables to Strings]チェック ボックスがオンでない場合にのみ、この変数は SQL クエリに null 値を渡します。
関数定義例:
create or replace function NULL_FUNC (a in NUMBER, b in VARCHAR2) return VARCHAR2
リテラル null 値を使用して、関数を呼び出します。
func NULL_FUNC null 'asdf'
null 値を持ったコンテキスト変数を使用して、関数を呼び出します。
func BOOL_FUNC ${policy.nullvalue}, 'b value'
サポートされるデータ型
以下の DBMS 型は、[SQL Query]テキスト ボックスを介して DML ステートメントを介してサポートされます。 プロシージャまたは関数を呼び出すときに、これらの型は入力値または出力値としてサポートされます。
DBMS 型
コンテキスト変数の型
Char、Varchar など
String
Numeric(Integer、Long など)
Integer または String
BLOB
16 進値または byte [] を持った String(セット コンテキスト変数を介してセットできません)
CLOB
String
Date
Date/Time
Timestamp
Date/Time
Boolean
DBMS によってサポートされる場合、String または Integer(以下の「ブール値」を参照)
String 値を使用して多くの DBMS 型(たとえば、CLOB を含む文字型、Numeric 型、Date と Timestamp(正しくフォーマットされている場合)、BLOB(16 進文字列を経由))の値を提供できます。
ブール値
各データベース型のブール値のサポートを以下に示します。
DBMS
サポート
Oracle
Data Direct ドライバまたはネイティブ ドライバのいずれかによってサポートされていません
MySQL
0 = false、それ以外 = true
ブール値はネイティブ ドライバを使用した MySQL で動作しません。
SQL Server
0 または「false」= false、1 または「true」= true
DB2
ブール型なし
Date 値
どちらかのリテラル値、String コンテキスト変数、または Date/Time コンテキスト変数(最も簡単な統合に推奨)を使用して、DATE または TIMESTAMP の値を提供できます。
日付またはタイム スタンプを表すリテラル文字列値に対して必要な形式は、DBMS 設定および接続プロパティを含む多くの要因によって決まります。 以下のデフォルト形式が動作することがわかっています。
  • Oracle:
    yyyy-mm-dd hh:mm:ss.fffffffff
    (たとえば、「1999-01-31 24:24:24:123456」)
  • その他の DBMS:
    yyyy-mm-dd hh:mm:ss
    (たとえば、「1999-01-31 24:24:24」)
BLOB 値
SQL クエリで BLOB 値を提供するには、コンテキスト変数またはリテラル値のいずれかを使用できます。 また、String コンテキスト変数、またはリテラル 16 進値(たとえば、「0123456789abcdef」)を介して 16 進文字列としてバイナリ データを提供できます。
既知の Oracle の問題
  • 関数およびプロシージャ名は大文字にする必要があります
  • Binary_Float、Binary_Double、Binary_Integer、Pls_Integer が文字列として返されます
  • 32 kB より大きな NCLOB 値を返す関数はサポートされていません
メタデータのキャッシュ
このセクションは、Perform JDBC Query アサーションが JDBC ドライバを介してどのようにメタデータについてデータベースにクエリを実行するかについていくつかの洞察を提供します。
関数およびプロシージャ メタデータはキャッシュされます。 キャッシュは以下の方法で実行できます。
  • 熱心に、バックグラウンド キャッシュ タスクを介して
  • 怠惰に、メタデータがメッセージ トラフィック処理時間(MTPT)でダウンロードされるときに
(1)キャッシュが設定され、キャッシュに使用可能なデータがない場合、メタデータはメッセージ処理スレッドによってダウンロードされます。 (2)存在しない関数またはプロシージャが参照されると、パッケージ名が指定されいる場合のみ、例外が発行され、キャッシュされます。 たとえば、存在しない関数「mypackage.myfunction」を呼び出すと、例外がトリガされます。 ただし、存在しない「myfunction」を呼び出しても例外はトリガされません。
メタデータのキャッシュは、
jdbcQueryManager.cacheMetaData.enable
クラスタ プロパティを「true」(デフォルト)に設定することで、有効になります。 このプロパティが既存のキャッシュされたデータに影響せず、バックグラウンド タスクがメタデータをキャッシュするのを防がないことに注意します。
キャッシュのクラスタ プロパティの詳細については、「JDBC のクラスタ プロパティ」を参照してください。
メタデータのバックグラウンド キャッシュ
Gateway には、メタデータがメッセージ トラフィック処理時間に確実に使用可能になるようにするために、メタデータを熱心にダウンロードするバックグラウンド タスクがあります。 Gateway を起動したとき、またはポリシーが保存されアクティブになったとき、この熱心なキャッシュが発生します。このキャッシュは、コンテキスト変数が JDBC 接続名またはスキーマに使用されていない場合のみ可能です。
データのキャッシュは、一連の一意キーの追跡に基づきます。 キーはそれぞれ次のように定義されます。
接続名 + プロシージャ名または関数名 + オプションのスキーマ名
そのため、2 つの Perform JDBC Query アサーションが同じプロシージャまたは関数を参照する場合、そのメタデータの単一のコピーのみが維持されます。 このバックグラウンド タスクは、デフォルトでは 10 分ごとに行われ、
jdbcQueryManager.cacheMetaDataTask.enable
クラスタ プロパティを介して有効または無効にされます。 タスク間隔は
jdbcQueryManager.cacheRefreshInterval
クラスタ プロパティによって制御されます。
このバックグラウンド タスクの処理時間を改善するには、多くのメタデータが管理されている場合、バックグラウンド処理スレッドの数を増やすことができます。 処理スレッドの数はデフォルトでは 10 ですが、
jdbcQueryManager.minCacheConcurrency
クラスタ プロパティを介して 200 まで増加できます。
注:
並列スレッドを注意して増加させます。
バックグラウンド タスクが一意のキーのメタデータを取得できない場合、例外がキャッシュされます。 このキャッシュ キーの Perform JDBC Query アサーションが含まれるポリシーを実行すると、クリーンアップ バックグラウンド タスクによってクリアされるまで、または、メタデータをダウンロードするバックグラウンド タスクによって修正されるまで、このキャッシュされた例外(たとえば、アサーションが失敗する可能性があります)を繰り返し返します。
クリーンアップ キャッシュ タスクは、デフォルトでは毎分実行され、
jdbcQueryManager.cacheCleanUpInterval
クラスタ プロパティによって制御されます。
管理されたキーのライフ サイクル
バックグラウンド タスクが一意のキーを管理し始めた後、ある時点で、キーの管理を停止する必要がある可能性があります。 これは、一意のキーを参照している Perform JDBC Query アサーションがないときに発生する可能性があります。ただし、これはコンテキスト変数が使用される場合に追跡するのが難しい可能性があります(たとえば、コンテキスト変数が実行時に解決されるまで、参照されている一意のキーは不明です)。
この問題を解決するために、Gateway は、特定のキーのキャッシュからのデータが使用される頻度を追跡できます。 それがしきい値を上回る場合、バックグラウンド タスクはそのメタデータの管理を停止します。
値は
jdbcQueryManager.cacheKeyNoUsageExpiration
クラスタ プロパティで定義されます。 デフォルトは 31 日です。また、値は秒単位で設定されます。
自動的な怠惰キャッシュ
バックグラウンド タスクが有効になっていないが、キャッシュが許可されている場合、ダウンロードされたすべてのメタデータは 2 回目にダウンロードされないようにキャッシュされます。 このメタデータの一意のキーは管理するためにキーのリストに追加されます。
キャッシュ有効期限
キャッシュされたアイテムを期限切れになるように設定して、古いメタが原因で Perform JDBC Query アサーションが失敗しないようにすることができます。
有効期限はバックグラウンド タスク リフレッシュ間隔より長くする必要があります。 また、ジョブが完了するのにかかる推定時間よりも長くする必要があります。
キャッシュ有効期限は
jdbcQueryManager.cacheStaleTimeout
クラスタ プロパティを介して制御されます。 デフォルトは 30 分で、秒単位で設定されます。
手動でのキャッシュへの格納
キャッシュが有効である場合は、Perform JDBC Query アサーションで[Test]ボタンをクリックすることにより、手動で呼び出すことができます。 これにより、メタデータがキャッシュ内にない場合は、メタデータがダウンロードされ、キャッシュされます。
キャッシュ ログ記録
バックグラウンド タスクが実行中か動作していることを示すアイテムは FINE レベルでログ記録されます。 キャッシュがメッセージ トラフィック処理時間に動作していることを示すアイテムは FINEST レベルでログ記録されます。
FINE ログ記録は以下のものに使用されます。
  • メタデータがダウンロードされ、キャッシュに追加されたとき。
  • メタデータをダウンロードできず、例外がキャッシュに追加されたとき。
  • バックグラウンドでメタデータを維持するタスクが開始されたとき、およびそれが終了したとき。
  • メタデータを管理するプロシージャを表す一意のキーがクリーンアップ タスクによってキャッシュから削除されたとき。
FINEST ログ記録は以下のものに使用されます。
  • メタデータ キャッシュ ヒット
  • メタデータ キャッシュ ヒットだがデータが期限切れのとき(古くなっている)
  • メタデータ キャッシュ ミス