Using Parameter Arrays

ODBC Driver for Teradata® User Guide

brand
Teradata Tools and Utilities
prodname
ODBC Driver for Teradata
vrm_release
16.20
category
User Guide
featnum
B035-2526-108K

To use arrays of parameters, the application needs to do the following:

  1. (Optional, the default is column-wise.) Call SQLSetStmtAttr with an argument of SQL_ATTR_PARAM_BIND_TYPE to specify column-wise or row-wise binding.

    For column-wise binding, the value is SQL_BIND_BY_COLUMN. For row-wise binding, the value is set to the size of() a row in the array holding the parameters.

  2. (Optional, default is 1.) Call SQLSetStmtAttr with an argument of SQL_ATTR_PARAMSET_SIZE to specify the number of sets of parameters.

    An array request with only 1 parameter set is equivalent to a “non-array” request.

  3. (Required only if SQL_ATTR_PARAMSET_SIZE is greater than 1.) Call SQLSetStmtAttr with an argument of SQL_ATTR_PARAM_STATUS_PTR to point to an array which contains return status information for each set of parameter values.
  4. (Optional) Call SQLSetStmtAttr with an argument of SQL_ATTR_PARAM_OPERATION_PTR to point to an array used to exclude or include sets of parameter values.
  5. (Optional) Call SQLSetStmtAttr with an argument of SQL_ATTR_PARAMS_PROCESSED_PTR to specify the address of a variable in which the driver can return the number of sets of parameters processed, including error sets.
  6. (Optional) Call SQLSetStmtAttr with an argument of SQL_ATTR_PARAM_BIND_OFFSET_PTR to specify the address of a variable containing an integer offset to be added to the ParameterValuePtr and StrLen_or_IndPtr parameters to SQLBindParameter.
  7. Call SQLBindParameter for each parameter to bind arrays to parameters.
  8. Call one of the execution functions: SQLExecDirect or SQLPrepare/SQLExecute.
Only steps 7 and 8 are required, all other steps are optional.
It is not possible to distinguish an array request with array size 1 from a non-array parameterized request.

The following figure illustrates the use of parameters.



When the statement is executed, ODBC Driver for Teradata uses the information it stored to retrieve the parameter values and send them to the data source.

The array pointed to by the SQL_ATTR_PARAM_OPERATION_PTR statement attribute can be used to ignore rows of parameters. If an element of the array is set to SQL_PARAM_IGNORE, the set of parameters corresponding to that element is excluded from the SQLExecute or SQLExecDirect call. If fetched rows are used as input parameters, the values of the row status array can be used in the parameter operation array.

If the SQL_ATTR_PARAM_STATUS_PTR statement attribute has been set, SQLExecute or SQLExecDirect returns the parameter status array, which provides the status of each set of parameters. The parameter status array is allocated by the application and filled in by the driver. Its elements indicate whether the SQL statement was executed successfully for the row of parameters or whether an error occurred while processing the set of parameters. If an error occurs, the driver sets the corresponding value in the parameter status array to SQL_PARAM_ERROR and returns SQL_ERROR. The application can check the status array to determine which parameter rows were processed. Using the row number, the application can often correct the error and resume processing.

The table that follows lists the types of DML statements with array support information.

DML Statement Description
ABORT Only when WHERE clause is present
DELETE All forms, except “Positioned”
EXECUTE macro The macro must be a single statement qualified for iteration
INSERT Includes INSERT ... SELECT
LOCKING Modifier Modified request must be qualified for iteration
MERGE Similar to UPDATE (Upsert Form)
ROLLBACK Only when WHERE clause is present (alias of ABORT)
SELECT Responses returned as in unfolded request
UPDATE (Searched Form) Includes complex and “unreasonable” updates
UPDATE (Upsert Form) UPDATE ... ELSE INSERT (Atomic UPSERT)