绑定参数的数组Binding Arrays of Parameters

使用参数数组的应用程序将数组绑定到 SQL 语句中的参数。Applications that use arrays of parameters bind the arrays to the parameters in the SQL statement. 有两种绑定样式:There are two binding styles:

  • 将数组绑定到每个参数。Bind an array to each parameter. 每个数据结构 (数组) 包含单个参数的所有数据。Each data structure (array) contains all the data for a single parameter. 这称为按 列绑定 ,因为它将一个值的列绑定到一个参数。This is called column-wise binding because it binds a column of values for a single parameter.

  • 定义一个结构来保存整组参数的参数数据,并绑定这些结构的数组。Define a structure to hold the parameter data for an entire set of parameters and bind an array of these structures. 每个数据结构包含单个 SQL 语句的数据。Each data structure contains the data for a single SQL statement. 这称为按 行绑定 ,因为它绑定了一行参数。This is called row-wise binding because it binds a row of parameters.

当应用程序将单个变量绑定到参数时,它会调用 SQLBindParameter 将数组绑定到参数。As when the application binds single variables to parameters, it calls SQLBindParameter to bind arrays to parameters. 唯一的区别是传递的地址是数组地址,而不是单变量地址。The only difference is that the addresses passed are array addresses, not single-variable addresses. 应用程序设置 SQL_ATTR_PARAM_BIND_TYPE 语句特性来指定它是在默认) 还是按行绑定 (使用列。The application sets the SQL_ATTR_PARAM_BIND_TYPE statement attribute to specify whether it is using column-wise (the default) or row-wise binding. 是使用按列绑定还是按行绑定,在很大程度上取决于应用程序首选项。Whether to use column-wise or row-wise binding is largely a matter of application preference. 根据处理器访问内存的方式,按行绑定可能会更快。Depending on how the processor accesses memory, row-wise binding might be faster. 但是,除了大量的参数行以外,差别可能会忽略不计。However, the difference is likely to be negligible except for very large numbers of rows of parameters.

按列绑定Column-Wise Binding

使用按列绑定时,应用程序将一个或两个数组绑定到要为其提供数据的每个参数。When using column-wise binding, an application binds one or two arrays to each parameter for which data is to be provided. 第一个数组保存数据值,第二个数组保存长度/指示器缓冲区。The first array holds the data values, and the second array holds length/indicator buffers. 每个数组包含的元素数量与参数的值相同。Each array contains as many elements as there are values for the parameter.

默认情况下,按列绑定。Column-wise binding is the default. 应用程序还可以通过设置 SQL_ATTR_PARAM_BIND_TYPE 语句特性,从按行绑定改为按列绑定。The application also can change from row-wise binding to column-wise binding by setting the SQL_ATTR_PARAM_BIND_TYPE statement attribute. 下图显示了按列绑定的工作方式。The following illustration shows how column-wise binding works.

显示列-方式绑定的工作原理Shows how column-wise binding works

例如,下面的代码将10元素数组绑定到 PartID、Description 和 Price 列的参数,并执行语句以插入10行。For example, the following code binds 10-element arrays to parameters for the PartID, Description, and Price columns, and executes a statement to insert 10 rows. 它使用按列绑定。It uses column-wise binding.

#define DESC_LEN 51  
#define ARRAY_SIZE 10  
  
SQLCHAR *      Statement = "INSERT INTO Parts (PartID, Description,  Price) "  
                                                "VALUES (?, ?, ?)";  
SQLUINTEGER    PartIDArray[ARRAY_SIZE];  
SQLCHAR        DescArray[ARRAY_SIZE][DESC_LEN];  
SQLREAL        PriceArray[ARRAY_SIZE];  
SQLINTEGER     PartIDIndArray[ARRAY_SIZE], DescLenOrIndArray[ARRAY_SIZE],  
               PriceIndArray[ARRAY_SIZE];  
SQLUSMALLINT   i, ParamStatusArray[ARRAY_SIZE];  
SQLULEN ParamsProcessed;  
  
memset(DescLenOrIndArray, 0, sizeof(DescLenOrIndArray));  
memset(PartIDIndArray, 0, sizeof(PartIDIndArray));  
memset(PriceIndArray, 0, sizeof(PriceIndArray));  
  
// Set the SQL_ATTR_PARAM_BIND_TYPE statement attribute to use  
// column-wise binding.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);  
  
// Specify the number of elements in each parameter array.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, ARRAY_SIZE, 0);  
  
// Specify an array in which to return the status of each set of  
// parameters.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, 0);  
  
// Specify an SQLUINTEGER value in which to return the number of sets of  
// parameters processed.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0);  
  
// Bind the parameters in column-wise fashion.  
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,  
                  PartIDArray, 0, PartIDIndArray);  
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0,  
                  DescArray, DESC_LEN, DescLenOrIndArray);  
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,  
                  PriceArray, 0, PriceIndArray);  
  
// Set part ID, description, and price.  
for (i = 0; i < ARRAY_SIZE; i++) {  
   GetNewValues(&PartIDArray[i], DescArray[i], &PriceArray[i]);  
   PartIDIndArray[i] = 0;  
   DescLenOrIndArray[i] = SQL_NTS;  
   PriceIndArray[i] = 0;  
}  
  
// Execute the statement.  
SQLExecDirect(hstmt, Statement, SQL_NTS);  
  
// Check to see which sets of parameters were processed successfully.  
for (i = 0; i < ParamsProcessed; i++) {  
   printf("Parameter Set  Status\n");  
   printf("-------------  -------------\n");  
   switch (ParamStatusArray[i]) {  
      case SQL_PARAM_SUCCESS:  
      case SQL_PARAM_SUCCESS_WITH_INFO:  
         printf("%13d  Success\n", i);  
         break;  
  
      case SQL_PARAM_ERROR:  
         printf("%13d  Error\n", i);  
         break;  
  
      case SQL_PARAM_UNUSED:  
         printf("%13d  Not processed\n", i);  
         break;  
  
      case SQL_PARAM_DIAG_UNAVAILABLE:  
         printf("%13d  Unknown\n", i);  
         break;  
  
   }  
}  

按行绑定Row-Wise Binding

使用按行绑定时,应用程序为每组参数定义结构。When using row-wise binding, an application defines a structure for each set of parameters. 结构包含每个参数的一个或两个元素。The structure contains one or two elements for each parameter. 第一个元素保存参数值,第二个元素保存长度/指示器缓冲区。The first element holds the parameter value, and the second element holds the length/indicator buffer. 然后,应用程序分配这些结构的数组,其中包含的元素数量与每个参数的值相同。The application then allocates an array of these structures, which contains as many elements as there are values for each parameter.

应用程序将结构的大小声明为带有 SQL_ATTR_PARAM_BIND_TYPE 语句特性的驱动程序。The application declares the size of the structure to the driver with the SQL_ATTR_PARAM_BIND_TYPE statement attribute. 应用程序绑定数组的第一个结构中的参数地址。The application binds the addresses of the parameters in the first structure of the array. 因此,驱动程序可以将特定行和列的数据地址计算为Thus, the driver can calculate the address of the data for a particular row and column as

Address = Bound Address + ((Row Number - 1) * Structure Size) + Offset  

其中,行从1到参数集的大小进行编号。where rows are numbered from 1 to the size of the parameter set. 偏移量(如果已定义)是 SQL_ATTR_PARAM_BIND_OFFSET_PTR 语句特性指向的值。The offset, if defined, is the value pointed to by the SQL_ATTR_PARAM_BIND_OFFSET_PTR statement attribute. 下图显示了按行绑定的工作方式。The following illustration shows how row-wise binding works. 参数可以按任意顺序放置在结构中,但会按顺序显示以便清晰。The parameters can be placed in the structure in any order but are shown in sequential order for clarity.

说明行-方式绑定的工作原理Shows how row-wise binding works

下面的代码创建一个结构,其中包含要存储在 PartID、Description 和 Price 列中的值的元素。The following code creates a structure with elements for the values to store in the PartID, Description, and Price columns. 然后,它会分配这些结构的10元素数组,并使用按行绑定将其绑定到 PartID、Description 和 Price 列的参数。It then allocates a 10-element array of these structures and binds it to parameters for the PartID, Description, and Price columns, using row-wise binding. 然后,它执行语句以插入10行。It then executes a statement to insert 10 rows.

#define DESC_LEN 51  
#define ARRAY_SIZE 10  
  
typedef tagPartStruct {  
   SQLREAL       Price;  
   SQLUINTEGER   PartID;  
   SQLCHAR       Desc[DESC_LEN];  
   SQLINTEGER    PriceInd;  
   SQLINTEGER    PartIDInd;  
   SQLINTEGER    DescLenOrInd;  
} PartStruct;  
  
PartStruct PartArray[ARRAY_SIZE];  
SQLCHAR *      Statement = "INSERT INTO Parts (PartID, Description,  
                Price) "  
               "VALUES (?, ?, ?)";  
SQLUSMALLINT   i, ParamStatusArray[ARRAY_SIZE];  
SQLULEN ParamsProcessed;  
  
// Set the SQL_ATTR_PARAM_BIND_TYPE statement attribute to use  
// column-wise binding.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, sizeof(PartStruct), 0);  
  
// Specify the number of elements in each parameter array.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, ARRAY_SIZE, 0);  
  
// Specify an array in which to return the status of each set of  
// parameters.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, 0);  
  
// Specify an SQLUINTEGER value in which to return the number of sets of  
// parameters processed.  
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0);  
  
// Bind the parameters in row-wise fashion.  
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,  
                  &PartArray[0].PartID, 0, &PartArray[0].PartIDInd);  
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0,  
                  PartArray[0].Desc, DESC_LEN, &PartArray[0].DescLenOrInd);  
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,  
                  &PartArray[0].Price, 0, &PartArray[0].PriceInd);  
  
// Set part ID, description, and price.  
for (i = 0; i < ARRAY_SIZE; i++) {  
   GetNewValues(&PartArray[i].PartID, PartArray[i].Desc, &PartArray[i].Price);  
   PartArray[0].PartIDInd = 0;  
   PartArray[0].DescLenOrInd = SQL_NTS;  
   PartArray[0].PriceInd = 0;  
}  
  
// Execute the statement.  
SQLExecDirect(hstmt, Statement, SQL_NTS);  
  
// Check to see which sets of parameters were processed successfully.  
for (i = 0; i < ParamsProcessed; i++) {  
   printf("Parameter Set  Status\n");  
   printf("-------------  -------------\n");  
   switch (ParamStatusArray[i]) {  
      case SQL_PARAM_SUCCESS:  
      case SQL_PARAM_SUCCESS_WITH_INFO:  
         printf("%13d  Success\n", i);  
         break;  
  
      case SQL_PARAM_ERROR:  
         printf("%13d  Error\n", i);  
         break;  
  
      case SQL_PARAM_UNUSED:  
         printf("%13d  Not processed\n", i);  
         break;  
  
      case SQL_PARAM_DIAG_UNAVAILABLE:  
         printf("%13d  Unknown\n", i);  
         break;  
  
   }