f90SQLBindParameter (SQLBindParameter)

Conformance

Version Introduced: ODBC 2.0

Standards Compliance: ODBC

Summary

f90SQLBindParameter binds a buffer to a parameter marker in an SQL statement. f90SQLBindParameter supports binding to a Unicode C data type, even if the underlying driver does not support Unicode data.

Note: This function replaces the ODBC 1.0 function SQLSetParam.

Syntax

Arguments

StatementHandle [Input]

Statement handle.

ParameterNumber [Input]

Parameter number, ordered sequentially in increasing parameter order, starting at 1.

InputOutputType [Input]

The type of the parameter. For more information, see "InputOutputType Argument" in "Comments."

ValueType [Input]

The Fortran data type of the parameter. For more information, see "ValueType Argument" in "Comments."

ParameterType [Input]

The SQL data type of the parameter. For more information, see "ParameterType Argument" in "Comments."

ColumnSize [Input]

The size of the column or expression of the corresponding parameter marker. For more information, see "ColumnSize Argument" in "Comments."

DecimalDigits [Input]

The decimal digits of the column or expression of the corresponding parameter marker. For further information concerning column size, see "Column Size, Decimal Digits, Transfer Octet Length, and Display Size," in Appendix 7.

ParameterValuePtr [Deferred Input]

A pointer to a buffer for the parameter's data. For more information, see "ParameterValuePtr Argument" in "Comments."

ParameterValue [Deferred Input]

Buffer for the parameter's data (in second syntax for the subroutine). For more information, see "ParameterValuePtr Argument" in "Comments."

BufferLength [Input/Output]

Length of the ParameterValuePtr buffer in bytes. For more information, see "BufferLength Argument" in "Comments."

StrLen_or_IndPtr [Deferred Input]

A pointer to a buffer for the parameter's length. For more information, see "StrLen_or_IndPtr Argument" in "Comments."

iRet [Output]

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When f90SQLBindParameter returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling f90SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by f90SQLBindParameter and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

Comments

An application calls f90SQLBindParameter to bind each parameter marker in an SQL statement. Bindings remain in effect until the application calls f90SQLBindParameter again, calls f90SQLFreeStmt with the SQL_RESET_PARAMS option, or calls f90SQLSetDescField to set the SQL_DESC_COUNT header field of the APD to 0.

ParameterNumber Argument

If ParameterNumber in the call to f90SQLBindParameter is greater than the value of SQL_DESC_COUNT, f90SQLSetDescField is called to increase the value of SQL_DESC_COUNT to ParameterNumber.

InputOutputType Argument

The InputOutputType argument specifies the type of the parameter. This argument sets the SQL_DESC_PARAMETER_TYPE field of the IPD. All parameters in SQL statements that do not call procedures, such as INSERT statements, are input parameters. Parameters in procedure calls can be input, input/output, or output parameters. (An application calls f90SQLProcedureColumns to determine the type of a parameter in a procedure call; parameters in procedure calls whose type cannot be determined are assumed to be input parameters.)

The InputOutputType argument is one of the following values:

• SQL_PARAM_INPUT. The parameter marks a parameter in an SQL statement that does not call a procedure, such as an INSERT statement, or it marks an input parameter in a procedure; these are collectively known as input parameters. For example, the parameters in

INSERT INTO Employee VALUES (?, ?, ?)

are input parameters, while the parameters in

{call AddEmp(?, ?, ?)}

can be, but are not necessarily, input parameters.

When the statement is executed, the driver sends data for the parameter to the data source; the *ParameterValuePtr buffer must contain a valid input value or the *StrLen_or_IndPtr buffer must contain SQL_NULL_DATA, SQL_DATA_AT_EXEC, or the result of the SQL_LEN_DATA_AT_EXEC macro.

If an application cannot determine the type of a parameter in a procedure call, it sets InputOutputType to SQL_PARAM_INPUT; if the data source returns a value for the parameter, the driver discards it.

• SQL_PARAM_INPUT_OUTPUT. The parameter marks an input/output parameter in a procedure. For example, the parameter in

{call GetEmpDept(?)}

is an input/output parameter that accepts an employee's name and returns the name of the employee's department.

When the statement is executed, the driver sends data for the parameter to the data source; the *ParameterValuePtr buffer must contain a valid input value or the *StrLen_or_IndPtr buffer must contain SQL_NULL_DATA, SQL_DATA_AT_EXEC, or the result of the f90SQLLenDataAtExec function. After the statement is executed, the driver returns data for the parameter to the application; if the data source does not return a value for an input/output parameter, the driver sets the *StrLen_or_IndPtr buffer to SQL_NULL_DATA.

Note: When an ODBC 1.0 application calls SQLSetParam in an ODBC 2.0 driver, the Driver Manager converts this to a call to f90SQLBindParameter in which the InputOutputType argument is set to SQL_PARAM_INPUT_OUTPUT.

SQL_PARAM_OUTPUT. The parameter marks the return value of a procedure or an output parameter in a procedure; these are collectively known as output parameters. For example, the parameter in

{?=call GetNextEmpID}

is an output parameter that returns the next employee ID.

After the statement is executed, the driver returns data for the parameter to the application, unless the ParameterValuePtr and StrLen_or_IndPtr arguments are both null pointers, in which case the driver discards the output value. If the data source does not return a value for an output parameter, the driver sets the *StrLen_or_IndPtr buffer to SQL_NULL_DATA.

ValueType Argument

The Fortran data type of the parameter. This argument sets the SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE, and SQL_DESC_DATETIME_INTERVAL_CODE fields of the APD. This must be one of the values in the Table 4.3 Fortran Data types (Chapter 4)

If the ValueType argument is one of the interval data types, the SQL_DESC_TYPE field of the ParameterNumber record of the APD is set to SQL_INTERVAL, the SQL_DESC_CONCISE_TYPE field of the APD is set to the concise interval data type, and the SQL_DESC_DATETIME_INTERVAL_CODE field of the ParameterNumber record is set to a subcode for the specific interval data type. The default interval leading precision (2) and default interval seconds precision (6), as set in the SQL_DESC_DATETIME_INTERVAL_PRECISION and SQL_DESC_PRECISION fields of the APD, respectively, are used for the data. If either default precision is not appropriate, the application should explicitly set the descriptor field by a call to f90SQLSetDescField or f90SQLSetDescRec.

If the ValueType argument is one of the datetime data types, the SQL_DESC_TYPE field of the ParameterNumber record of the APD is set to SQL_DATETIME, the SQL_DESC_CONCISE_TYPE field of the ParameterNumber record of the APD is set to the concise datetime Fortran data type, and the SQL_DESC_DATETIME_INTERVAL_CODE field of the ParameterNumber record is set to a subcode for the specific datetime data type.

If the ValueType argument is an SQL_F_NUMERIC data type, the default precision (which is driver-defined) and the default scale (0), as set in the SQL_DESC_PRECISION and SQL_DESC_SCALE fields of the APD, are used for the data. If the default precision or scale is not appropriate, the application should explicitly set the descriptor field by a call to f90SQLSetDescField or f90SQLSetDescRec.

SQL_F_DEFAULT specifies that the parameter value be transferred from the default Fortran data type for the SQL data type specified with ParameterType.

ParameterType Argument

This must be one of the values in Table 4.3 (Chapter 4), or a driver-specific value. This argument sets the SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE, and SQL_DESC_DATETIME_INTERVAL_CODE fields of the IPD.

If the ParameterType argument is one of the datetime identifiers, the SQL_DESC_TYPE field of the IPD is set to SQL_DATETIME, the SQL_DESC_CONCISE_TYPE field of the IPD is set to the concise datetime SQL data type, and the SQL_DESC_DATETIME_INTERVAL_CODE field is set to the appropriate datetime subcode value.

If ParameterType is one of the interval identifiers, the SQL_DESC_TYPE field of the IPD is set to SQL_INTERVAL, the SQL_DESC_CONCISE_TYPE field of the IPD is set to the concise SQL interval data type, and the SQL_DESC_DATETIME_INTERVAL_CODE field of the IPD is set to the appropriate interval subcode. The SQL_DESC_DATETIME_INTERVAL_PRECISION field of the IPD is set to the interval leading precision, and the SQL_DESC_PRECISION field is set to the interval seconds precision, if applicable. If the default value of SQL_DESC_DATETIME_INTERVAL_PRECISION or SQL_DESC_PRECISION is not appropriate, the application should explicitly set it by calling f90SQLSetDescField. For more information about any of these fields, see f90SQLSetDescField.

If the ValueType argument is a SQL_NUMERIC data type, the default precision (which is driver-defined) and the default scale (0), as set in the SQL_DESC_PRECISION and SQL_DESC_SCALE fields of the IPD, are used for the data. If the default precision or scale is not appropriate, the application should explicitly set the descriptor field by a call to f90SQLSetDescField or f90SQLSetDescRec.

ColumnSize Argument

The ColumnSize argument specifies the size of the column or expression corresponding to the parameter marker, or the length of that data, or both. This argument sets different fields of the IPD, depending on the SQL data type (the ParameterType argument). The following rules apply to this mapping:

• If ParameterType is SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY, or one of the concise SQL datetime or interval data types, the SQL_DESC_LENGTH field of the IPD is set to the value of ColumnSize.

• If ParameterType is SQL_DECIMAL, SQL_NUMERIC, SQL_FLOAT, SQL_REAL, or SQL_DOUBLE, the SQL_DESC_PRECISION field of the IPD is set to the value of ColumnSize.

• For other data types, the ColumnSize argument is ignored.

DecimalDigits Argument

If ParameterType is SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, SQL_INTERVAL_SECOND, SQL_INTERVAL_DAY_TO_SECOND, SQL_INTERVAL_HOUR_TO_SECOND, or SQL_INTERVAL_MINUTE_TO_SECOND, the SQL_DESC_PRECISION field of the IPD is set to DecimalDigits. If ParameterType is SQL_NUMERIC or SQL_DECIMAL, the SQL_DESC_SCALE field of the IPD is set to DecimalDigits. For all other data types, the DecimalDigits argument is ignored.

ParameterValuePtr Argument

The ParameterValuePtr argument is a buffer that, when f90SQLExecute or f90SQLExecDirect is called, contains the actual data for the parameter. The data must be in the form specified by the ValueType argument. This argument sets the SQL_DESC_DATA_PTR field of the APD. An application can set the ParameterValuePtr argument to a null pointer, as long as *StrLen_or_IndPtr is SQL_NULL_DATA or SQL_DATA_AT_EXEC. (This applies only to input or input/output parameters.)

A second syntax for f90SQLBindParam allows passing the variable that contains the parameter buffer instead of an address to the buffer (see the second Syntax for the subroutine). A limited number of Fortran Data types are allowed with this syntax. In the second syntax, the buffer variable is passed as argument ParameterValue. In this case, f90SQL retrieves the address from the passed variable and calls the f90SQLBindParam as defined in the first syntax. If the pass variable is a character string, the argument BufferLength is set to Len(ParameterValue).

If *StrLen_or_IndPtr is the result of the f90SQLLenDataAtExec(length) function or SQL_DATA_AT_EXEC, then ParameterValuePtr is an application-defined, 32-bit value that is associated with the parameter. It is returned to the application through f90SQLParamData. For example, ParameterValuePtr might be a token such as a parameter number, a pointer to data, or a pointer to a structure that the application used to bind input parameters. Note, however, that if the parameter is an input/output parameter, ParameterValuePtr must be a pointer to a buffer where the output value will be stored. If the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is greater than 1, the application can use the value pointed to by the SQL_ATTR_PARAMS_PROCESSED_PTR statement attribute in conjunction with the ParameterValuePtr argument. For example, ParameterValuePtr might point to an array of values and the application might use the value pointed to by SQL_ATTR_PARAMS_PROCESSED_PTR to retrieve the correct value from the array. For more information, see "Passing Parameter Values" later in this section.

If the InputOutputType argument is SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT, ParameterValuePtr points to a buffer in which the driver returns the output value. If the procedure returns one or more result sets, the *ParameterValuePtr buffer is not guaranteed to be set until all result sets/row counts have been processed. If the buffer is not set until processing is complete, the output parameters and return values are unavailable until f90SQLMoreResults returns SQL_NO_DATA. Calling f90SQLCloseCursor or f90SQLFreeStmt with an Option of SQL_CLOSE will cause these values to be discarded.

If the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is greater than 1, ParameterValuePtr points to an array. A single SQL statement processes the entire array of input values for an input or input/output parameter and returns an array of output values for an input/output or output parameter.

A driver will return SQLSTATE HY090 (Invalid string or buffer length), if ParameterValuePtr is not a null pointer and *StrLen_or_IndPtr is not SQL_NTS, SQL_NULL_DATA, SQL_DATA_AT_EXEC, SQL_DEFAULT_PARAM, greater than or equal to 0, or less than or equal to the value of SQL_LEN_DATA_AT_EXEC_OFFSET.

BufferLength Argument

For character and binary data, the BufferLength argument specifies the length of the *ParameterValuePtr buffer (if it is a single element) or the length of an element in the *ParameterValuePtr array (if the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is greater than 1). This argument sets the SQL_DESC_OCTET_LENGTH record field of the APD. If the application specifies multiple values, BufferLength is used to determine the location of values in the *ParameterValuePtr array, both on input and on output. For input/output and output parameters, it is used to determine whether to truncate character and binary data on output:

• For character data, if the number of bytes available to return is greater than or equal to BufferLength, the data in *ParameterValuePtr is truncated to BufferLength less the length of a null-termination character and is null-terminated by the driver.

• For binary data, if the number of bytes available to return is greater than BufferLength, the data in *ParameterValuePtr is truncated to BufferLength bytes.

For all other types of data, the BufferLength argument is ignored. The length of the *ParameterValuePtr buffer (if it is a single element) or the length of an element in the *ParameterValuePtr array (if the application calls f90SQLSetStmtAttr with an Attribute argument of SQL_ATTR_PARAMSET_SIZE to specify multiple values for each parameter) is assumed to be the length of the data type.

Note: If f90SQLBindParam is called using the second syntax of the subroutine, BufferLength is set to Len(ParameterValue) for character buffers.

Note: When an ODBC 1.0 application calls SQLSetParam in an ODBC 3.5 driver, the Driver Manager converts this to a call to f90SQLBindParameter in which the BufferLength argument is always SQL_SETPARAM_VALUE_MAX. Because the Driver Manager returns an error if an ODBC 3.5 application sets BufferLength to SQL_SETPARAM_VALUE_MAX, an ODBC 3.5 driver can use this to determine when it is called by an ODBC 1.0 application.

In SQLSetParam, the way in which an application specifies the length of the *ParameterValuePtr buffer so that the driver can return character or binary data, and the way in which an application sends an array of character or binary parameter values to the driver, are driver-defined.

StrLen_or_IndPtr Argument

The StrLen_or_IndPtr argument points to a buffer that, when f90SQLExecute or f90SQLExecDirect is called, contains one of the following. This argument sets the SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_INDICATOR_PTR record fields of the application parameter pointers.

• The length of the parameter value stored in *ParameterValuePtr. This is ignored except for character or binary C data.

• SQL_NTS. The parameter value is a null-terminated string.

• SQL_NULL_DATA. The parameter value is NULL.

• SQL_DEFAULT_PARAM. A procedure is to use the default value of a parameter, rather than a value retrieved from the application. This value is valid only in a procedure called in ODBC canonical syntax, and then only if the InputOutputType argument is SQL_PARAM_INPUT or SQL_PARAM_INPUT_OUTPUT. When *StrLen_or_IndPtr is SQL_DEFAULT_PARAM, the ValueType, ParameterType, ColumnSize, DecimalDigits, BufferLength, and ParameterValuePtr arguments are ignored for input parameters and are used only to define the output parameter value for input/output parameters.

• The result of the f90SQLLenDataAtExec(length) function. The data for the parameter will be sent with f90SQLPutData. If the ParameterType argument is SQL_LONGVARBINARY, SQL_LONGVARCHAR, or a long, data source - specific data type and the driver returns "Y" for the SQL_NEED_LONG_DATA_LEN information type in f90SQLGetInfo, length is the number of bytes of data to be sent for the parameter; otherwise, length must be a nonnegative value and is ignored. For more information, see "Passing Parameter Values" later in this section.

For example, to specify that 10,000 bytes of data will be sent with f90SQLPutData for an SQL_LONGVARCHAR parameter, an application sets *StrLen_or_IndPtr to SQL_LEN_DATA_AT_EXEC(10000).

• SQL_DATA_AT_EXEC. The data for the parameter will be sent with f90SQLPutData. This value is used by ODBC 1.0 applications when calling ODBC 3.5 drivers. For more information, see "Passing Parameter Values" later in this section.

If StrLen_or_IndPtr is a null pointer, the driver assumes that all input parameter values are non-NULL and that character and binary data are null-terminated. If InputOutputType is SQL_PARAM_OUTPUT and ParameterValuePtr and StrLen_or_IndPtr are both null pointers, the driver discards the output value.

Note: Application developers are strongly discouraged from specifying a null pointer for StrLen_or_IndPtr when the data type of the parameter is SQL_F_BINARY. To ensure that a driver does not unexpectedly truncate SQL_F_BINARY data, StrLen_or_IndPtr should contain a pointer to a valid length value.

If the InputOutputType argument is SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT, StrLen_or_IndPtr points to a buffer in which the driver returns SQL_NULL_DATA, the number of bytes available to return in *ParameterValuePtr (excluding the null-termination byte of character data), or SQL_NO_TOTAL if the number of bytes available to return cannot be determined. If the procedure returns one or more result sets, the *StrLen_or_IndPtr buffer is not guaranteed to be set until all results have been fetched.

If the value in the SQL_ATTR_PARAMSET_SIZE statement attribute is greater than 1, StrLen_or_IndPtr points to an array of SQLINTEGER values. These can be any of the values listed earlier in this section and are processed with a single SQL statement.

Passing Parameter Values

An application can pass the value for a parameter either in the *ParameterValuePtr buffer or with one or more calls to f90SQLPutData. Parameters whose data is passed with f90SQLPutData are known as data-at-execution parameters. These are commonly used to send data for SQL_LONGVARBINARY and SQL_LONGVARCHAR parameters, and can be mixed with other parameters.

To pass parameter values, an application performs the following sequence of steps:

1. Calls f90SQLBindParameter for each parameter to bind buffers for the parameter's value (ParameterValuePtr argument) and length/indicator (StrLen_or_IndPtr argument). For data-at-execution parameters, ParameterValuePtr is an application-defined, 32-bit value such as a parameter number or a pointer to data. The value will be returned later and can be used to identify the parameter.

2. Places values for input and input/output parameters in the *ParameterValuePtr and *StrLen_or_IndPtr buffers:

For normal parameters, the application places the parameter value in the *ParameterValuePtr buffer and the length of that value in the *StrLen_or_IndPtr buffer.

For data-at-execution parameters, the application places the result of the f90SQLLenDataAtExec(length) function (when calling an ODBC 2.0 driver) in the *StrLen_or_IndPtr buffer.

3. Calls f90SQLExecute or f90SQLExecDirect to execute the SQL statement.

If there are no data-at-execution parameters, the process is complete.

If there are any data-at-execution parameters, the function returns SQL_NEED_DATA.

4. Calls f90SQLParamData to retrieve the application-defined value specified in the ParameterValuePtr argument of f90SQLBindParameter for the first data-at-execution parameter to be processed. f90SQLParamData returns SQL_NEED_DATA.

Note: Although data-at-execution parameters are similar to data-at-execution columns, the value returned by f90SQLParamData is different for each.

Data-at-execution parameters are parameters in an SQL statement for which data will be sent with f90SQLPutData when the statement is executed with f90SQLExecDirect or f90SQLExecute. They are bound with f90SQLBindParameter. The value returned by f90SQLParamData is a 32-bit value passed to f90SQLBindParameter in the ParameterValuePtr argument.

Data-at-execution columns are columns in a rowset for which data will be sent with f90SQLPutData when a row is updated or added with f90SQLBulkOperations or updated with f90SQLSetPos. They are bound with f90SQLBindCol. The value returned by f90SQLParamData is the address of the row in the *TargetValuePtr buffer (set by a call to f90SQLBindCol) that is being processed.

Calls f90SQLPutData one or more times to send data for the parameter. More than one call is needed if the data value is larger than the *ParameterValuePtr buffer specified in f90SQLPutData; note that multiple calls to f90SQLPutData for the same parameter are allowed only when sending character data to a column with a character, binary, or data source - specific data type or when sending binary data to a column with a character, binary, or data source - specific data type.

5. Calls f90SQLParamData again to signal that all data has been sent for the parameter.

• If there are more data-at-execution parameters, f90SQLParamData returns SQL_NEED_DATA and the application-defined value for the next data-at-execution parameter to be processed. The application repeats steps 4 and 5.
• If there are no more data-at-execution parameters, the process is complete. If the statement was successfully executed, f90SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO; if the execution failed, it returns SQL_ERROR. At this point, f90SQLParamData can return any SQLSTATE that can be returned by the function used to execute the statement (f90SQLExecDirect or f90SQLExecute).

Output values for any input/output or output parameters are available in the *ParameterValuePtr and *StrLen_or_IndPtr buffers after the application retrieves all result sets generated by the statement.

Calling f90SQLExecute or f90SQLExecDirect puts the statement in an SQL_NEED_DATA state. At this point, the application can call only f90SQLCancel, f90SQLGetDiagField, f90SQLGetDiagRec, f90SQLGetFunctions, f90SQLParamData, or f90SQLPutData with the statement or the connection handle associated with the statement. If it calls any other function with the statement or the connection associated with the statement, the function returns SQLSTATE HY010 (Function sequence error). The statement leaves the SQL_NEED_DATA state when f90SQLParamData or f90SQLPutData returns an error, f90SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, or the statement is canceled.

If the application calls f90SQLCancel while the driver still needs data for data-at-execution parameters, the driver cancels statement execution; the application can then call f90SQLExecute or f90SQLExecDirect again.

Using Arrays of Parameters

When an application prepares a statement with parameter markers and passes in an array of parameters, there are two different ways this can be executed. One way is for the driver to rely on the array-processing capabilities of the back end, in which case the entire statement with the array of parameters is treated as one atomic unit. Oracle is an example of a data source that supports array processing capabilities. Another way to implement this feature is for the driver to generate a batch of SQL statements, one SQL statement for each set of parameters in the parameter array, and execute the batch. Arrays of parameters cannot be used with an UPDATE WHERE CURRENT OF statement.

When an array of parameters is processed, individual result sets/row counts (one for each parameter set) can be available, or result sets/rows counts can be rolled up into one. The SQL_PARAM_ARRAY_ROW_COUNTS option in f90SQLGetInfo indicates whether row counts are available for each set of parameters (SQL_PARC_BATCH), or only one row count is available (SQL_PARC_NO_BATCH).

The SQL_PARAM_ARRAY_SELECTS option in f90SQLGetInfo indicates whether a result set is available for each set of parameters (SQL_PAS_BATCH), or only one result set is available (SQL_PAS_NO_BATCH). If the driver does not allow a result set - generating statement to be executed with an array of parameters, SQL_PARAM_ARRAY_SELECTS returns SQL_PAS_NO_SELECT.

For more information, see f90SQLGetInfo.

To support arrays of parameters, the SQL_ATTR_PARAMSET_SIZE statement attribute is set to specify the number of values for each parameter. If the field is greater than 1, the SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR fields of the APD must point to arrays. The cardinality of each array is equal to the value of SQL_ATTR_PARAMSET_SIZE.

The SQL_DESC_ROWS_PROCESSED_PTR field of the APD points to a buffer that contains the number of sets of parameters that have been processed, including error sets. As each set of parameters is processed, the driver stores a new value in the buffer. No number will be returned if this is a null pointer. When arrays of parameters are used, the value pointed to by the SQL_DESC_ROWS_PROCESSED_PTR field of the APD is populated even if SQL_ERROR is returned by the setting function. If SQL_NEED_DATA is returned, the value pointed to by the SQL_DESC_ROWS_PROCESSED_PTR field of the APD is set to the set of parameters that is being processed.

It is driver-defined what occurs when an array of parameters is bound and an UPDATE WHERE CURRENT OF statement is executed.

Column-Wise Parameter Binding

In column-wise binding, the application binds separate parameter and length/indicator arrays to each parameter.

To use column-wise binding, the application first sets the SQL_ATTR_PARAM_BIND_TYPE statement attribute to SQL_PARAM_BIND_BY_COLUMN (this is the default). For each column to be bound, the application performs the following steps:

1. Allocates a parameter buffer array.

2. Allocates an array of length/indicator buffers.

Note: If the application writes directly to descriptors when column-wise binding is used, then separate arrays can be used for length and indicator data.

3. Calls f90SQLBindParameter with the following arguments:

• ValueType is the Fortran type of a single element in the parameter buffer array.
• ParameterType is the SQL type of the parameter.
• ParameterValuePtr is the address of the parameter buffer array.
• BufferLength is the size of a single element in the parameter buffer array. The BufferLength argument is ignored when the data is fixed-length data.
• StrLen_or_IndPtr is the address of the length/indicator array.

For more information about how this information is used, see "ParameterValuePtr Argument" in "Comments" later in this section. For more information about column-wise binding of parameters, see Chapter 5.

Row-Wise Parameter Binding

In row-wise binding, the application defines a structure containing parameter and length/indicator buffers for each parameter to be bound.

To use row-wise binding, the application performs the following steps:

1. Defines a structure to hold a single set of parameters (including both parameter and length/indicator buffers) and allocates an array of these structures.

2. Note: If the application writes directly to descriptors when row-wise binding is used, then separate fields can be used for length and indicator data.

3. Sets the SQL_ATTR_PARAM_BIND_TYPE statement attribute to the size of the structure containing a single set of parameters, or to the size of an instance of a buffer into which the parameters will be bound. The length must include space for all of the bound parameters, and any padding of the structure or buffer to make sure that when the address of a bound parameter is incremented with the specified length, the result will point to the beginning of the same parameter in the next row. When using the Fortran equivalent to the sizeof operator in ANSI C, this behavior is guaranteed. Note however that this function in not included in the ANSI Fortran Standard and is compiler specific.

4. Calls f90SQLBindParameter with the following arguments for each parameter to be bound:

• ValueType is the type of the parameter buffer member to be bound to the column.
• ParameterType is the SQL type of the parameter.
• ParameterValuePtr is the address of the parameter buffer member in the first array element.
• BufferLength is the size of the parameter buffer member.
• StrLen_or_IndPtr is the address of the length/indicator member to be bound.

For more information about how this information is used, see "ParameterValuePtr Argument," later in this section. For more information about row-wise binding of parameters, see the Chapter 5.

Error Information

If a driver does not implement parameter arrays as batches (the SQL_PARAM_ARRAY_ROW_COUNTS option is equal to SQL_PARC_NO_BATCH), error situations are handled as if one statement were executed. If the driver does implement parameter arrays as batches, an application can use the SQL_DESC_ARRAY_STATUS_PTR header field of the IPD to determine which parameter of an SQL statement, or which parameter in an array of parameters, caused f90SQLExecDirect or f90SQLExecute to return an error. This field contains status information for each row of parameter values. If the field indicates that an error has occurred, fields in the diagnostic data structure will indicate the row and parameter number of the parameter that failed. The number of elements in the array will be defined by the SQL_DESC_ARRAY_SIZE header field in the APD, which can be set by the SQL_ATTR_PARAMSET_SIZE statement attribute.

Note: The SQL_DESC_ARRAY_STATUS_PTR header field in the APD is used to ignore parameters. For more information about ignoring parameters, see the next section, "Ignoring a Set of Parameters."

When f90SQLExecute or f90SQLExecDirect returns SQL_ERROR, the elements in the array pointed to by the SQL_DESC_ARRAY_STATUS_PTR field in the IPD will contain SQL_PARAM_ERROR, SQL_PARAM_SUCCESS, SQL_PARAM_SUCCESS_WITH_INFO, SQL_PARAM_UNUSED, or SQL_PARAM_DIAG_UNAVAILABLE.

For each in this array, the diagnostic data structure contains one or more status records. The SQL_DIAG_ROW_NUMBER field of the structure indicates the row number of the parameter values that caused the error. If it is possible to determine the particular parameter in a row of parameters that caused the error, then the parameter number will be entered in the SQL_DIAG_COLUMN_NUMBER field.

SQL_PARAM_UNUSED is entered when a parameter has not been used because an error occurred in an earlier parameter that forced f90SQLExecute or f90SQLExecDirect to abort. For example, if there are 50 parameters and an error occurred while executing the fortieth set of parameters that caused f90SQLExecute or f90SQLExecDirect to abort, then SQL_PARAM_UNUSED is entered in the status array for parameters 41 through 50.

SQL_PARAM_DIAG_UNAVAILABLE is entered when the driver treats arrays of parameters as a monolithic unit, so it does not generate this level of error information.

Some errors in the processing of a single set of parameters cause processing of the subsequent sets of parameters in the array to stop. Other errors do not affect the processing of subsequent parameters. It is driver-defined which errors will stop processing. If processing is not stopped, all parameters in the array are processed, SQL_SUCCESS_WITH_INFO is returned as a result of the error, and the buffer defined by SQL_ATTR_PARAMS_PROCESSED_PTR is set to the total number of sets of parameters processed (as defined by the SQL_ATTR_PARAMSET_SIZE statement attribute), which includes error sets.

Caution: ODBC behavior when an error occurs in the processing of an array of parameters is different in ODBC 3.5 than it was in ODBC 2.x. In ODBC 2.x, the function returned SQL_ERROR and processing ceased. The buffer pointed to by the pirow argument of SQLParamOptions contained the number of the error row. In ODBC 3.5, the function returns SQL_SUCCESS_WITH_INFO and processing may either cease or continue. If it continues, the buffer specified by SQL_ATTR_PARAMS_PROCESSED_PTR will be set to the value of all parameters processed, including those that resulted in an error. This change in behavior may cause problems for existing applications.

When f90SQLExecute or f90SQLExecDirect returns before completing the processing of all parameter sets in a parameter array, such as when SQL_ERROR or SQL_NEED_DATA is returned, the status array contains statuses for those parameters that have already been processed. The location pointed to by the SQL_DESC_ROWS_PROCESSED_PTR field in the IPD contains the row number in the parameter array that caused the SQL_ERROR or SQL_NEED_DATA error code. When an array of parameters is sent to a SELECT statement, the availability of status array values is driver-defined; they may be available after the statement has been executed, or as result sets are fetched.

Ignoring a Set of Parameters

The SQL_DESC_ARRAY_STATUS_PTR field of the APD (as set by the SQL_ATTR_PARAM_STATUS_PTR statement attribute) can be used to indicate that a set of bound parameters in an SQL statement should be ignored. To direct the driver to ignore one or more sets of parameters during execution, an application should perform the following steps:

1. Call f90SQLSetDescField to set the SQL_DESC_ARRAY_STATUS_PTR header field of the APD to point to an array of SQLUSMALLINT values to contain status information. This field can also be set by calling f90SQLSetStmtAttr with an Attribute of SQL_ATTR_PARAM_OPERATION_PTR, which allows an application to set the field without obtaining a descriptor handle.

2. Set each element of the array defined by the SQL_DESC_ARRAY_STATUS_PTR field of the APD to one of two values:

• SQL_PARAM_IGNORE, to indicate that the row is excluded from statement execution.
• SQL_PARAM_PROCEED, to indicate that the row is included in statement execution.

3. Call f90SQLExecDirect or f90SQLExecute to execute the prepared statement.

The following rules apply to the array defined by the SQL_DESC_ARRAY_STATUS_PTR field of the APD:

• The pointer is set to null by default.
• If the pointer is null, then all sets of parameters are used, as if all elements were set to SQL_ROW_PROCEED.
• Setting an element to SQL_PARAM_PROCEED does not guarantee that the operation will use that particular set of parameters.
• SQL_PARAM_PROCEED is defined as 0 in the header file.

An application can set the SQL_DESC_ARRAY_STATUS_PTR field in the APD to point to the same array as that pointed to by the SQL_DESC_ARRAY_STATUS_PTR field in the IRD. This is useful when binding parameters to row data. Parameters then can be ignored according to the status of the row data. The following codes cause a parameter in an SQL statement to be ignored, in addition to SQL_PARAM_IGNORE: SQL_ROW_DELETED, SQL_ROW_UPDATED, and SQL_ROW_ERROR. The following codes cause an SQL statement to proceed, in addition to SQL_PARAM_PROCEED: SQL_ROW_SUCCESS, SQL_ROW_SUCCESS_WITH_INFO, and SQL_ROW_ADDED.

Rebinding Parameters

An application can perform either of two operations to change a binding:

• Call f90SQLBindParameter to specify a new binding for a column that is already bound. The driver overwrites the old binding with the new one.

• Specify an offset to be added to the buffer address that was specified by the binding call to f90SQLBindParameter. For more information, see the next section, "Rebinding with Offsets."

Rebinding with Offsets

Rebinding of parameters is especially useful when an application has a buffer area setup that is capable of containing many parameters, but a call to f90SQLExecDirect or f90SQLExecute uses only a few of the parameters. The remaining space in the buffer area can be used for the next set of parameters by modifying the existing binding by an offset.

The SQL_DESC_BIND_OFFSET_PTR header field in the APD points to the binding offset. If the field is non-null, the driver dereferences the pointer and, if none of the values in the SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR fields is a null pointer, adds the dereferenced value to those fields in the descriptor records at execution time. The new pointer values are used when the SQL statements are executed. The offset remains valid after rebinding. Because SQL_DESC_BIND_OFFSET_PTR is a pointer to the offset, rather than the offset itself, an application can change the offset directly, without having to call f90SQLSetDescField or f90SQLSetDescRec to change the descriptor field. The pointer is set to null by default. The SQL_DESC_BIND_OFFSET_PTR field of the ARD can be set by a call to f90SQLSetDescField or by a call to f90SQLSetStmtAttr with an Attribute of SQL_ATTR_PARAM_BIND_OFFSET_PTR.

The binding offset is always added directly to the values in the SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR fields. If the offset is changed to a different value, the new value is still added directly to the value in each descriptor field. The new offset is not added to the sum of the field value and any earlier offsets.

Descriptors

How a parameter is bound is determined by fields of the APDs and IPDs. The arguments in f90SQLBindParameter are used to set those descriptor fields. The fields also can be set by the f90SQLSetDescField functions, although f90SQLBindParameter is more efficient to use because the application does not have to obtain a descriptor handle to call f90SQLBindParameter.

Caution: Calling f90SQLBindParameter for one statement can affect other statements. This occurs when the ARD associated with the statement is explicitly allocated and is also associated with other statements. Because f90SQLBindParameter modifies the fields of the APD, the modifications apply to all statements with which this descriptor is associated. If this is not the required behavior, the application should dissociate this descriptor from the other statements before calling f90SQLBindParameter.

Conceptually, f90SQLBindParameter performs the following steps in sequence:

1. Calls f90SQLGetStmtAttr to obtain the APD handle.

2. Calls f90SQLGetDescField to get the APD's SQL_DESC_COUNT field, and if the value of the ColumnNumber argument exceeds the value of SQL_DESC_COUNT, calls f90SQLSetDescField to increase the value of SQL_DESC_COUNT to ColumnNumber.

3. Calls f90SQLSetDescField multiple times to assign values to the following fields of the APD:

• Sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of ValueType, except that if ValueType is one of the concise identifiers of a datetime or interval subtype, it sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL, respectively, sets SQL_DESC_CONCISE_TYPE to the concise identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval subcode.
• Sets the SQL_DESC_OCTET_LENGTH field to the value of BufferLength.
• Sets the SQL_DESC_DATA_PTR field to the value of ParameterValue.
• Sets the SQL_DESC_OCTET_LENGTH_PTR field to the value of StrLen_or_Ind.
• Sets the SQL_DESC_INDICATOR_PTR field also to the value of StrLen_or_Ind.

The StrLen_or_Ind parameter specifies both the indicator information and the length for the parameter value.

4. Calls f90SQLGetStmtAttr to obtain the IPD handle.

5. Calls f90SQLGetDescField to get the IPD's SQL_DESC_COUNT field, and if the value of the ColumnNumber argument exceeds the value of SQL_DESC_COUNT, calls f90SQLSetDescField to increase the value of SQL_DESC_COUNT to ColumnNumber.

6. Calls f90SQLSetDescField multiple times to assign values to the following fields of the IPD:

• Sets SQL_DESC_TYPE and SQL_DESC_CONCISE_TYPE to the value of ParameterType, except that if ParameterType is one of the concise identifiers of a datetime or interval subtype, it sets SQL_DESC_TYPE to SQL_DATETIME or SQL_INTERVAL, respectively, sets SQL_DESC_CONCISE_TYPE to the concise identifier, and sets SQL_DESC_DATETIME_INTERVAL_CODE to the corresponding datetime or interval subcode.
• Sets one or more of SQL_DESC_LENGTH, SQL_DESC_PRECISION, and SQL_DESC_DATETIME_INTERVAL_PRECISION, as appropriate for ParameterType.
• Sets SQL_DESC_SCALE to the value of DecimalDigits.

If the call to f90SQLBindParameter fails, the content of the descriptor fields that it would have set in the APD are undefined, and the SQL_DESC_COUNT field of the APD is unchanged. In addition, the SQL_DESC_LENGTH, SQL_DESC_PRECISION, SQL_DESC_SCALE, and SQL_DESC_TYPE fields of the appropriate record in the IPD are undefined and the SQL_DESC_COUNT field of the IPD is unchanged.

Code Example

For examples see f90SQLBulkOperations, f90SQLProcedures, f90SQLPutData, and f90SQLSetPos, Chapter 4, Chapter 5 and Chapter 6.

Related Subroutines