資料表值參數和資料行值的繫結與資料傳送

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

資料表值參數 (TVP) ，就像其他參數一樣，必須先系結，才能將它們傳遞至伺服器。 應用程式會以系結其他參數的方式系結資料表值參數：使用 SQLBindParameter 或對等呼叫 SQLSetDescField 或 SQLSetDescRec。 資料表值參數的伺服器資料類型是 SQL_SS_TABLE。 C 類型可以指定為 SQL_C_DEFAULT 或 SQL_C_BINARY。

在 SQL Server 2008 (10.0.x) 或更新版本中，僅支援輸入資料表值參數。 因此，任何嘗試將SQL_DESC_PARAMETER_TYPE設定為 SQL_PARAM_INPUT 以外的值，會傳回具有 SQLSTATE = HY105 的SQL_ERROR，以及「不正確參數類型」訊息。

您可以使用屬性 SQL_CA_SS_COL_HAS_DEFAULT_VALUE，將預設值指派給整個資料表值參數資料行。 不過，使用 sqlBindParameter StrLen_or_IndPtr中的 SQL_DEFAULT_PARAM，無法指派個別資料表值參數資料行值。 資料表值參數整體不能使用 sqlBindParameter StrLen_or_IndPtr 中的 SQL_DEFAULT_PARAM設定為預設值。 如果未遵循這些規則，SQLExecute 或 SQLExecDirect 會傳回SQL_ERROR。 診斷記錄是使用 SQLSTATE=07S01 產生的，而訊息「參數 p 的預設參數 < 無效」，其中 < p >> 是查詢語句中 TVP 的序數。

注意

資料表值參數沒有可以設定的預設值，因為SQL_DEFAULT_PARAM表示沒有資料列。 因此，如果沒有資料列，則沒有要系結的資料行。

繫結資料表值參數之後，應用程式必須接著繫結每個資料表值參數資料行。 若要這樣做，應用程式會先呼叫 SQLSetStmtAttr，將SQL_SOPT_SS_PARAM_FOCUS設定為數據表值參數的序數。 應用程式會呼叫下列常式來系結資料表值參數的資料行：SQLBindParameter、SQLSetDescRec 和 SQLSetDescField。 將 SQL_SOPT_SS_PARAM_FOCUS 設定為 0 可還原 SQLBindParameter、SQLSetDescRec 和 SQLSetDescField 在一般最上層參數上運作的一般效果。

注意

針對具有 unixODBC 2.3.1 至 2.3.4 的 Linux 和 Mac ODBC 驅動程式，透過具有SQL_CA_SS_TYPE_NAME描述元欄位的 SQLSetDescField 設定 TVP 名稱時，unixODBC 不會根據稱為 (SQLSetDescFieldA / SQLSetDescFieldW 的確切函式，自動在 ANSI 和 Unicode 字串之間轉換) 。 請務必一律使用 SQLBindParameter 或 SQLSetDescFieldW 搭配 Unicode (UTF-16) 字串來設定 TVP 名稱。

雖然不會針對資料表值參數本身傳送或接收任何實際資料，但是會針對每個構成的資料行傳送或接收資料。 因為資料表值參數是虛擬資料行，所以 SQLBindParameter 的參數會參考與其他資料類型不同的屬性，如下所示：

參數 非資料表值參數類型的相關屬性，包括資料行 資料表值參數的相關屬性
InputOutputType IPD 中的 SQL_DESC_PARAMETER_TYPE。

若為資料表值參數資料行，這必須與資料表值參數本身的設定相同。		 IPD 中的 SQL_DESC_PARAMETER_TYPE。

這必須是 SQL_PARAM_INPUT。
ValueType APD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。 APD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。

這必須是 SQL_C_DEFAULT 或 SQL_C_BINARY。
ParameterType IPD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。 IPD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。

這必須是 SQL_SS_TABLE。
ColumnSize IPD 中的 SQL_DESC_LENGTH 或 SQL_DESC_PRECISION。

這取決於 ParameterType的值。		 SQL_DESC_ARRAY_SIZE

當參數焦點設定為資料表值參數時，也可以使用 SQL_ATTR_PARAM_SET_SIZE 來設定。

如果是資料表值參數，這就是資料表值參數資料行緩衝區中的資料列數目。
DecimalDigits IPD 中的 SQL_DESC_PRECISION 或 SQL_DESC_SCALE。 未使用的。 這必須是 0。

如果此參數不是 0，SQLBindParameter 會傳回SQL_ERROR，而且會使用 SQLSTATE= HY104 和訊息「不正確有效位數或小數位數」產生診斷記錄。
ParameterValuePtr APD 中的 SQL_DESC_DATA_PTR。 SQL_CA_SS_TYPE_NAME。

這是預存程序呼叫的選擇性專案，如果不需要，則可以指定 Null。 它必須針對不是程序呼叫的 SQL 語句指定。

這個參數也會當做一個唯一值，應用程式可以在使用變動資料列繫結時使用這個值來識別這個資料表值參數。 如需詳細資訊，請參閱本主題後面的「變動資料表值參數資料列繫結」一節。

當呼叫 SQLBindParameter 時指定資料表值參數類型名稱時，它必須指定為 Unicode 值，即使在建置為 ANSI 應用程式的應用程式中也一樣。 參數 StrLen_or_IndPtr 所使用的值應該是SQL_NTS或名稱的字串長度乘以 (WCHAR) 的大小。
BufferLength APD 中的 SQL_DESC_OCTET_LENGTH。 資料表值參數類型名稱的長度 (以位元組為單位)。

如果類型名稱為 Null 終止，則可以SQL_NTS，如果不需要資料表值參數類型名稱則為 0。
StrLen_or_IndPtr APD 中的 SQL_DESC_OCTET_LENGTH_PTR。 APD 中的 SQL_DESC_OCTET_LENGTH_PTR。

若為資料表值參數，這就是資料列計數而非資料長度。

資料表值參數支援兩種資料傳送模式：固定資料列繫結和變動資料列繫結。

固定資料表值參數資料列繫結

對於固定資料列繫結而言，應用程式會針對所有可能的輸入資料行值配置夠大的緩衝區 (或緩衝區陣列)。 此應用程式會進行下列作業：

  1. 使用 SQLBindParameter、SQLSetDescRec 或 SQLSetDescField 呼叫來系結所有參數。

    1. 將 SQL_DESC_ARRAY_SIZE 設定為可針對每個資料表值參數傳送的最大資料列數目。 這可以在 SQLBindParameter 呼叫中完成。

  2. 呼叫 SQLSetStmtAttr，將SQL_SOPT_SS_PARAM_FOCUS設定為每個資料表值參數的序數。

    1. 針對每個資料表值參數，使用 SQLBindParameter、SQLSetDescRec 或 SQLSetDescField 呼叫來系結資料表值參數資料行。

    2. 針對每個具有預設值的資料表值參數資料行，呼叫 SQLSetDescField 將SQL_CA_SS_COL_HAS_DEFAULT_VALUE設為 1。

  3. 呼叫 SQLSetStmtAttr 將SQL_SOPT_SS_PARAM_FOCUS設為 0。 這必須在呼叫 SQLExecute 或 SQLExecDirect 之前完成。 否則，會傳回SQL_ERROR，並使用 SQLSTATE=HY024 產生診斷記錄，而且訊息「不正確屬性值，SQL_SOPT_SS_PARAM_FOCUS (必須在執行時間為零) 」。

  4. 針對不含資料列的資料表值參數，將 StrLen_or_IndPtr 或SQL_DESC_OCTET_LENGTH_PTR設定為SQL_DEFAULT_PARAM，或如果資料表值參數具有資料列，則會在下一次呼叫 SQLExecute 或 SQLExecDirect 時傳送的資料列數目。 StrLen_or_IndPtr 或SQL_DESC_OCTET_LENGTH_PTR無法設定為數據表值參數的SQL_Null_DATA，因為資料表值參數不可為 Null (不過資料表值參數組成資料行可以是可為 Null) 。 如果此值設定為不正確值，SQLExecute 或 SQLExecDirect 會傳回SQL_ERROR，而且會使用 SQLSTATE=HY090 產生診斷記錄，以及參數 p 的訊息「不正確字串或緩衝區長度」，其中 p > 是參數 < 編號。

  5. 呼叫 SQLExecute 或 SQLExecDirect。

    如果 資料行的StrLen_or_IndPtr 設定為SQL_LEN_DATA_AT_EXEC (長度) 或SQL_DATA_AT_EXEC ， 則輸入資料表值參數資料行值可以傳入片段。 這與使用參數陣列時分段傳遞值一樣。 如同所有執行中的資料參數，SQLParamData 不會指出驅動程式要求資料的陣列資料列;應用程式必須負責處理這項作業。 應用程式無法對驅動程式要求值的順序進行任何假設。

變動資料表值參數資料列繫結

針對變數資料列系結，資料列會在執行時間以批次方式傳輸，而應用程式會視需要將資料列傳遞至驅動程式。 這與個別參數值的資料執行中一樣。 應用程式會針對變動資料列繫結，執行下列作業：

  1. 如上一節的步驟 1 至 3 所述，系結參數和資料表值參數資料行：「已修正Table-Valued參數資料列系結」。

  2. 設定StrLen_or_IndPtr或SQL_DESC_OCTET_LENGTH_PTR，讓任何資料表值參數在執行時間傳遞至SQL_DATA_AT_EXEC。 如果兩者都未設定，則會依照上一節所述處理 參數。

  3. 呼叫 SQLExecute 或 SQLExecDirect。 如果有任何SQL_PARAM_INPUT或SQL_PARAM_INPUT_OUTPUT參數當做執行中資料參數來處理，這會傳回SQL_NEED_DATA。 在此情況下，應用程式會進行下列作業：

    • 呼叫 SQLParamData。 這會傳回資料執行中參數的 ParameterValuePtr 值，並傳回SQL_NEED_DATA的傳回碼。 當所有參數資料都傳遞至驅動程式時，SQLParamData 會傳回SQL_SUCCESS、SQL_SUCCESS_WITH_INFO或SQL_ERROR。 對於資料執行中參數， ParameterValuePtr與描述元欄位SQL_DESC_DATA_PTR相同，可以視為權杖，以識別唯一需要值的參數。 這個 "Token" 會在繫結階段從應用程式傳遞至驅動程式，然後在執行階段傳回應用程式。

  4. 若要傳送 null 資料表值參數的資料表值參數資料列資料，如果資料表值參數沒有資料列，應用程式會呼叫STRLEN_OR_IND設定為 SQL_DEFAULT_PARAM 的 SQLPutData。

    應用程式會針對非 NULL TVP：

    • 將所有資料表值參數資料行 的Str_Len_or_Ind 設定為適當的值，並填入資料表值參數資料行的資料緩衝區，這些資料行不是資料執行中參數。 您可以利用一般參數分段傳遞至驅動程式的相同方式來運用資料表值參數資料行的資料執行中。

    • 呼叫 STR_LEN_OR_IND 設定為要傳送至伺服器之資料列數目的 SQLPutData。 超出範圍 0 到SQL_DESC_ARRAY_SIZE或SQL_DEFAULT_PARAM的任何值都是錯誤，並傳回 SQLSTATE HY090，訊息為「不正確字串或緩衝區長度」。0 表示已傳送所有資料列，而且此清單中的第二個專案符號專案) (資料表值參數沒有其他資料 (。 只有當驅動程式第一次要求資料表值參數的資料時，才能使用 SQL_DEFAULT_PARAM (如此清單中第一個項目符號項目所述)。

  5. 傳送所有資料列時，請呼叫具有 0 Str_Len_or_Ind 值之資料表值參數的 SQLPutData，然後繼續進行上述步驟 3a。

  6. 再次呼叫 SQLParamData。 如果資料表值參數資料行之間有任何資料執行中參數，這些參數是由 SQLParamData 傳回的值 ValuePtrPtrPtr 來識別。 當所有資料行值都可供使用時，SQLParamData 會傳回資料表值參數的 ParameterValuePtr 值，而應用程式會再次開始。

下一步

資料表值參數 ODBC