ODBC テーブル値パラメーターの使用

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

このトピックでは、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_SCHEMA_NAMEを設定する必要があります。 テーブル型の定義とテーブル値パラメーターは同じデータベースに存在する必要があるため、アプリケーションでテーブル値パラメーターを使用する場合は、SQL_CA_SS_CATALOG_NAMEを設定しないでください。 それ以外の場合、SQLSetDescField はエラーを報告します。

このシナリオのサンプル コードは、「Table-Valued パラメーターの使用 (ODBC)」の手順demo_fixed_TVP_bindingに含まれています。

行のストリーミングを使用するテーブル値パラメーター (実行時のデータを使用する TVP としてデータを送信する)

このシナリオでは、要求に応じてアプリケーションからドライバーに行が渡され、サーバーにストリーム送信されます。 これにより、すべての行をメモリ内にバッファリングする必要がなくなります。 これは、一括挿入や一括更新のシナリオの代表的な例です。 テーブル値パラメーターは、パフォーマンスの点ではパラメーター配列と一括コピーとの間に位置します。 つまり、テーブル値パラメーターは、パラメーター配列と同程度にプログラミングが容易ですが、サーバー側の柔軟性が増します。

テーブル値パラメーターとその列は、前の「複数行のバッファーに完全にバインドされるテーブル値パラメーター」で説明したとおりにバインドされますが、テーブル値パラメーター自体の長さのインジケーターは、SQL_DATA_AT_EXEC に設定されます。 ドライバーは、sqlExecute または SQLExecuteDirect に通常の方法で応答します。つまり、実行時データ パラメーターに対しては、SQL_NEED_DATAを返します。 ドライバーがテーブル値パラメーターのデータを受け入れる準備ができたら、SQLParamData は SQLBindParameter で ParameterValuePtr の値を返します。

アプリケーションでは、テーブル値パラメーターに SQLPutData を使用して、テーブル値パラメーター構成列のデータの可用性を示します。 テーブル値パラメーターに対して SQLPutData が呼び出される場合、 DataPtr は常に null であり、 StrLen_or_Ind は、テーブル値パラメーター バッファー (SQLBindParameter の ColumnSize パラメーター) に指定された配列サイズ以下の 0 以上である必要があります。 0 はテーブル値パラメーターの行がなくなったことを示すため、ドライバーはプロシージャの次の実パラメーターの処理に進みます。 StrLen_or_Indが 0 でない場合、ドライバーはテーブル値パラメーターの構成列を、テーブル値以外のパラメーター バインド パラメーターと同じ方法で処理します。各テーブル値パラメーター列では、実際のデータ長、SQL_NULL_DATAを指定したり、長さ/インジケーター バッファーを使用して実行時にデータを指定したりできます。 テーブル値パラメーターの列値は、文字またはバイナリ値を 1 つずつ渡す場合に、通常どおり SQLPutData を繰り返し呼び出すことで渡すことができます。

テーブル値パラメーターのすべての列が処理されたら、ドライバーはテーブル値パラメーターに戻り、テーブル値パラメーターのデータの次の行を処理します。 したがって、実行時データのテーブル値パラメーターの場合、バインドされたパラメーターを順番にスキャンする通常の方法には従いません。 バインドされたテーブル値パラメーターは、SQLPutData が 0 と等しい StrLen_Or_IndPtr で呼び出されるまでポーリングされます。この時点で、ドライバーはテーブル値パラメーター列をスキップし、次の実際のストアド プロシージャ パラメーターに移動します。 SQLPutData が 1 以上のインジケーター値を渡すと、ドライバーは、バインドされたすべての行と列の値を持つまで、テーブル値パラメーターの列と行を順番に処理します。 その後、ドライバーはテーブル値パラメーターに戻ります。 SQLParamData からテーブル値パラメーターのトークンを受け取り、テーブル値パラメーターに対して SQLPutData(hstmt, NULL, n) を呼び出す間に、アプリケーションはテーブル値パラメーター構成列データとインジケーター バッファーの内容を設定して、次の行または行をサーバーに渡す必要があります。

このシナリオのサンプル コードは、「Table-Valued パラメーターの使用 (ODBC)」のルーチンdemo_variable_TVP_bindingにあります。

システム カタログからテーブル値パラメーターのメタデータを取得

アプリケーションがテーブル値パラメーターを持つプロシージャに対して SQLProcedureColumns を呼び出すと、DATA_TYPEは SQL_SS_TABLE として返され、TYPE_NAMEはテーブル値パラメーターのテーブル型の名前になります。 SQLProcedureColumns によって返される結果セットに 2 つの列が追加されます。SS_TYPE_CATALOG_NAMEは、テーブル値パラメーターのテーブル型が定義されているカタログの名前を返し、SS_TYPE_SCHEMA_NAMEはテーブル値パラメーターのテーブル型が定義されているスキーマの名前を返します。 ODBC 仕様に準拠して、SS_TYPE_CATALOG_NAMEとSS_TYPE_SCHEMA_NAMEは、以前のバージョンのSQL Serverで追加されたすべてのドライバー固有の列の前に表示され、ODBC 自体によって要求されたすべての列の後に表示されます。

新しい列は、テーブル値パラメーター用だけでなく、CLR ユーザー定義型パラメーター用にも作成されます。 UDT パラメーターの既存のスキーマ列とカタログ列も依然として作成されますが、共通のスキーマ列とカタログ列を必要とするデータ型にもそれらの列を含めておくと、今後アプリケーション開発が簡単になります (XML スキーマ コレクションは多少異なり、この変更には含まれていないことに注意してください)。

アプリケーションでは、SQLTables を使用して、永続テーブル、システム テーブル、およびビューの場合と同じ方法でテーブル型の名前を決定します。 アプリケーションでテーブル値パラメーターに関連付けられたテーブル型を識別できるように、新しいテーブル型として TABLE TYPE が導入されました。 テーブル型と通常のテーブルでは、異なる名前空間を使用します。 つまり、テーブル型と実際のテーブルに、同じ名前を使用できます。 これに対処するために、新しいステートメント属性として SQL_SOPT_SS_NAME_SCOPE が導入されました。 この属性は、SQLTable と、テーブル名をパラメーターとして受け取る他のカタログ関数が、テーブル名を実際のテーブルの名前またはテーブル型の名前として解釈するかどうかを指定します。

アプリケーションでは、SQLColumns を使用して、永続テーブルの場合と同じ方法でテーブル型の列を決定しますが、最初に SQL_SOPT_SS_NAME_SCOPE を設定して、実際のテーブルではなくテーブル型を操作していることを示す必要があります。 SQLPrimaryKeys は、SQL_SOPT_SS_NAME_SCOPEを使用してテーブル型でも使用できます。

このシナリオのサンプル コードは、「Table-Valued パラメーターの使用 (ODBC)」のルーチンdemo_metadata_from_catalog_APIsにあります。

準備されたステートメント用にテーブル値パラメーターのメタデータを取得

このシナリオでは、アプリケーションは SQLNumParameters と SQLDescribeParam を使用して、テーブル値パラメーターのメタデータを取得します。

IPD フィールド SQL_CA_SS_TYPE_NAME は、テーブル値パラメーターの型名を取得するために使用されます。 IPD フィールドSQL_CA_SS_SCHEMA_NAMEとSQL_CA_SS_CATALOG_NAMEは、それぞれカタログとスキーマを取得するために使用されます。

テーブル型の定義とテーブル値パラメーターは同じデータベースに存在する必要があります。 テーブル値パラメーターの使用時にアプリケーションがSQL_CA_SS_CATALOG_NAMEを設定すると、SQLSetDescField はエラーを報告します。

SQL_CA_SS_CATALOG_NAMEとSQL_CA_SS_SCHEMA_NAMEを使用して、CLR ユーザー定義型パラメーターに関連付けられているカタログとスキーマを取得することもできます。 SQL_CA_SS_CATALOG_NAMEとSQL_CA_SS_SCHEMA_NAMEは、CLR UDT 型の既存の型固有のカタログ スキーマ属性の代替手段です。

SQLDescribeParam はテーブル値パラメーター列の列のメタデータを返さないため、アプリケーションでは SQLColumns を使用してテーブル値パラメーターの列メタデータも取得します。

このユース ケースのサンプル コードは、「Table-Valued パラメーターの使用 (ODBC)」のルーチンdemo_metadata_from_prepared_statementにあります。

参照

テーブル値パラメーター (ODBC)