ODBC テーブル値パラメーターの使用
このトピックでは、ODBC でテーブル値パラメーターを使用する主なユーザー シナリオについて説明します。
複数行のバッファーに完全にバインドされるテーブル値パラメーター (すべての値をメモリ内に保持した TVP としてデータを送信する)
行のストリーミングを使用するテーブル値パラメーター (実行時のデータを使用する TVP としてデータを送信する)
システム カタログからテーブル値パラメーターのメタデータを取得
準備されたステートメント用にテーブル値パラメーターのメタデータを取得
複数行のバッファーに完全にバインドされるテーブル値パラメーター (すべての値をメモリ内に保持した TVP としてデータを送信する)
複数行のバッファーに完全にバインドされる形式で使用する場合、すべてのパラメーター値はメモリ内から使用できます。このような形式は OLTP トランザクションなどでは一般的に行われ、テーブル値パラメーターを 1 つのストアド プロシージャにパッケージ化できます。テーブル値パラメーターを使用しないと、複雑な複数のステートメントで構成されるバッチを動的に生成し、サーバーを複数回呼び出すことになります。
テーブル値パラメーター自体は他のパラメーターと共に、SQLBindParameter を使用してバインドされます。すべてのパラメーターがバインドされたら、アプリケーションでは、各テーブル値パラメーターのパラメーター フォーカスの属性 SQL_SOPT_SS_PARAM_FOCUS を設定し、テーブル値パラメーターの列に対して SQLBindParameter を呼び出します。
テーブル値パラメーターのサーバー型は、SQL Server 固有の新しい型 SQL_SS_TABLE です。SQL_SS_TABLE の C データ型へのバインドは、常に SQL_C_DEFAULT にする必要があります。テーブル値パラメーターにバインドされたパラメーターについては、データが転送されません。このパラメーターは、テーブルのメタデータを渡し、テーブル値パラメーターを構成する列にデータを渡す方法を制御するために使用されます。
テーブル値パラメーターの長さには、サーバーに送信される行数が設定されます。テーブル値パラメーターの SQLBindParameter の ColumnSize パラメーターでは、送信できる最大行数を指定します。この値は、列バッファーの配列のサイズです。ParameterValuePtr は、SQLBindParameter のテーブル値パラメーター用のパラメーター バッファーです。ParameterValuePtr およびこれと関連する BufferLength は、必要に応じてテーブル値パラメーターの型名を渡すために使用されます。型名は、ストアド プロシージャの呼び出しには必要ありませんが、SQL ステートメントには必要です。
テーブル値パラメーターの型名を SQLBindParameter の呼び出しで指定する場合、ANSI アプリケーションとしてビルドされているアプリケーションであっても、常に Unicode 値として指定する必要があります。SQLSetDescField を使用してテーブル値パラメーターの型名を指定する場合は、アプリケーションのビルド方法に準拠したリテラルを使用できます。ODBC ドライバー マネージャーで、必要な Unicode 変換を実行します。
テーブル値パラメーターのメタデータおよびテーブル値パラメーターの列は、SQLGetDescRec、SQLSetDescRec、SQLGetDescField、および SQLSetDescField を使用して、個別に明示的に操作できます。ただし、通常は SQLBindParameter をオーバーロードする方が便利で、ほとんどの場合に明示的な記述子のアクセスが必要ありません。この方法は、テーブル値パラメーターの影響を受ける記述子フィールドがやや異なる点を除いて、他のデータ型の SQLBindParameter の定義と一貫性があります。
アプリケーションでは、場合によっては、動的な SQL を含むテーブル値パラメーターを使用して、テーブル値パラメーターの型名を指定する必要があります。このような場合に、テーブル値パラメーターが接続の現在の既定のスキーマで定義されていないときは、SQLSetDescField を使用して、SQL_CA_SS_TYPE_CATALOG_NAME および SQL_CA_SS_TYPE_SCHEMA_NAME を設定する必要があります。テーブル型の定義とテーブル値パラメーターは同じデータベースに存在する必要があるため、アプリケーションでテーブル値パラメーターを使用する場合は、SQL_CA_SS_TYPE_CATALOG_NAME を設定しないでください。設定すると、SQLSetDescField でエラーが報告されます。
このシナリオのサンプル コードは、CodePlex で入手できるサンプル アプリケーションの demo_fixed_table-valued parameter_binding プロシージャに含まれています。詳細については、「SQL Server データベース エンジン サンプル」を参照してください。
行のストリーミングを使用するテーブル値パラメーター (実行時のデータを使用する TVP としてデータを送信する)
このシナリオでは、要求に応じてアプリケーションからドライバーに行が渡され、サーバーにストリーム送信されます。これにより、すべての行をメモリ内にバッファリングする必要がなくなります。これは、一括挿入や一括更新のシナリオの代表的な例です。テーブル値パラメーターは、パフォーマンスの点ではパラメーター配列と一括コピーとの間に位置します。つまり、テーブル値パラメーターは、パラメーター配列と同程度にプログラミングが容易ですが、サーバー側の柔軟性が増します。
テーブル値パラメーターとその列は、前の「複数行のバッファーに完全にバインドされるテーブル値パラメーター」で説明したとおりにバインドされますが、テーブル値パラメーター自体の長さのインジケーターは、SQL_DATA_AT_EXEC に設定されます。ドライバーは、実行時データ パラメーターの通常の方法 (SQL_NEED_DATA を返す方法) で、SQLExecute または SQLExecuteDirect に応答します。ドライバーでテーブル値パラメーターのデータを受け取る準備ができたら、SQLParamData が SQLBindParameter の ParameterValuePtr の値を返します。
アプリケーションでは、テーブル値パラメーターに SQLPutData を使用して、テーブル値パラメーターを構成する列のデータが利用できるかどうかを示します。テーブル値パラメーターに対して SQLPutData が呼び出されるとき、DataPtr は常に NULL である必要があり、StrLen_or_Ind は 0 またはテーブル値パラメーターのバッファーに指定された配列のサイズ (SQLBindParameter の ColumnSize パラメーター) の数値以下である必要があります。0 はテーブル値パラメーターの行がなくなったことを示すため、ドライバーはプロシージャの次の実パラメーターの処理に進みます。StrLen_or_Ind が 0 ではない場合、ドライバーは、テーブル値パラメーターにバインドされていないパラメーターと同じ方法で、テーブル値パラメーターを構成する列を処理します。つまり、テーブル値パラメーターの各列で、その実際のデータ長 (SQL_NULL_DATA) を指定するか、長さまたはインジケーターのバッファーを使用して実行時データを指定することができます。文字値またはバイナリ値が個別に渡される場合、通常どおり SQLPutData を繰り返し呼び出すことで、テーブル値パラメーターの列の値を渡すことができます。
テーブル値パラメーターのすべての列が処理されたら、ドライバーはテーブル値パラメーターに戻り、テーブル値パラメーターのデータの次の行を処理します。したがって、実行時データのテーブル値パラメーターの場合、バインドされたパラメーターを順番にスキャンする通常の方法には従いません。バインドされたテーブル値パラメーターは、StrLen_Or_IndPtr が 0 の状態で SQLPutData が呼び出されるまでポーリングされます。0 の状態で呼び出された時点で、ドライバーはテーブル値パラメーターの列をスキップし、ストアド プロシージャの次の実パラメーターに移動します。SQLPutData から 1 以上のインジケーター値を渡すと、ドライバーはバインドされたすべての行と列の値を取得するまで、テーブル値パラメーターの列と行を順番に処理します。その後、ドライバーはテーブル値パラメーターに戻ります。SQLParamData からテーブル値パラメーターのトークンを取得してから、テーブル値パラメーターの SQLPutData(hstmt, NULL, n) を呼び出すまでの間に、アプリケーションでは、サーバーに渡す次の行に、テーブル値パラメーターを構成する列のデータおよびインジケーター バッファーのコンテンツを設定する必要があります。
このシナリオのサンプル コードは、CodePlex で入手できるサンプル アプリケーションの demo_variable_table-valued parameter_binding ルーチンに含まれています。詳細については、「SQL Server データベース エンジン サンプル」を参照してください。
システム カタログからテーブル値パラメーターのメタデータを取得
アプリケーションからテーブル値パラメーターをパラメーターに含むプロシージャに対して SQLProcedureColumns を呼び出すと、DATA_TYPE は SQL_SS_TABLE として返され、TYPE_NAME はテーブル値パラメーターのテーブル型の名前です。新たに 2 つの列が、SQLProcedureColumns によって返される結果セットに追加されます。1 つは SS_TYPE_CATALOG_NAME で、テーブル値パラメーターのテーブル型が定義されているカタログの名前を返します。もう 1 つは SS_TYPE_SCHEMA_NAME で、テーブル値パラメーターのテーブル型が定義されているスキーマの名前を返します。SS_TYPE_CATALOG_NAME および SS_TYPE_SCHEMA_NAME は、ODBC 仕様に準拠して、以前のバージョンの SQL Server で追加されたドライバー固有のすべての列の前、かつ ODBC 自体によって指定されるすべての列の後に作成されます。
新しい列は、テーブル値パラメーター用だけでなく、CLR ユーザー定義型パラメーター用にも作成されます。UDT パラメーターの既存のスキーマ列とカタログ列も依然として作成されますが、共通のスキーマ列とカタログ列を必要とするデータ型にもそれらの列を含めておくと、今後アプリケーション開発が簡単になります (XML スキーマ コレクションは多少異なり、この変更には含まれていないことに注意してください)。
アプリケーションでは SQLTables を使用して、永続的なテーブル、システム テーブル、およびビューと同じ方法で、テーブル型の名前を決定します。アプリケーションでテーブル値パラメーターに関連付けられたテーブル型を識別できるように、新しいテーブル型として TABLE TYPE が導入されました。テーブル型と通常のテーブルでは、異なる名前空間を使用します。つまり、テーブル型と実際のテーブルに、同じ名前を使用できます。これに対処するために、新しいステートメント属性として SQL_SOPT_SS_NAME_SCOPE が導入されました。この属性では、テーブル名をパラメーターとして受け取る SQLTables やその他のカタログ関数で、テーブル名を実際のテーブルの名前として解釈するか、テーブル型の名前として解釈するかを指定します。
アプリケーションでは、SQLColumns を使用して、永続的なテーブルと同じ方法でテーブル型の列を決定しますが、まず SQL_SOPT_SS_NAME_SCOPE を指定して、実際のテーブルではなくテーブル型に対して操作していることを示す必要があります。テーブル型では SQLPrimaryKeys も使用でき、この場合も SQL_SOPT_SS_NAME_SCOPE を使用します。
このシナリオのサンプル コードは、CodePlex で入手できるサンプル アプリケーションの demo_metadata_from_catalog_APIs ルーチンに含まれています。詳細については、「SQL Server データベース エンジン サンプル」を参照してください。
準備されたステートメント用にテーブル値パラメーターのメタデータを取得
このシナリオでは、アプリケーションで SQLNumParameters および SQLDescribeParam を使用して、テーブル値パラメーターのメタデータを取得します。
IPD フィールド SQL_CA_SS_TYPE_NAME は、テーブル値パラメーターの型名を取得するために使用されます。IPD フィールド SQL_CA_SS_TYPE_SCHEMA_NAME と SQL_CA_SS_TYPE_CATALOG_NAME は、それぞれスキーマとカタログを取得するために使用されます。
テーブル型の定義とテーブル値パラメーターは同じデータベースに存在する必要があります。テーブル値パラメーターを使用しているときに、アプリケーションで SQL_CA_SS_TYPE_CATALOG_NAME を設定すると、SQLSetDescField でエラーが報告されます。
SQL_CA_SS_TYPE_CATALOG_NAME および SQL_CA_SS_TYPE_SCHEMA_NAME を使用すると、CLR ユーザー定義型パラメーターに関連付けられたカタログとスキーマも取得できます。SQL_CA_SS_TYPE_CATALOG_NAME および SQL_CA_SS_TYPE_SCHEMA_NAME は、CLR UDT 型の既存の型固有のカタログ スキーマの属性の代わりに使用します。
このシナリオでは、SQLDescribeParam によってテーブル値パラメーター列の列のメタデータが返されないため、アプリケーションで SQLColumns を使用して、テーブル値パラメーター列のメタデータも取得します。
このユース ケースのサンプル コードは、CodePlex で入手できるサンプル アプリケーションの demo_metadata_from_prepared_statement ルーチンに含まれています。詳細については、「SQL Server データベース エンジン サンプル」を参照してください。