SQLSetStmtAttr
SQLSetStmtAttr#
SQLSetStmtAttr() sets the attribute related to the statement handle.
SQLSetStmtAttrW() as a Unicode string supports same execution as SQLSetStmtAttr().
Syntax#
SQLRETURN SQLSetStmtAttr (
SQLHSTMT stmt,
SQLINTEGER Attribute,
SQLPOINTER param,
SQLINTEGER StringLength );
Arguments#
| Data Type | Argument | In/Output | Description |
|---|---|---|---|
| SQLHENV | stmt | Input | Statement handle |
| SQLINTEGER | Attribute | Input | Attribute to set, Supported attribute value: - SQL_ATTR_CONCURRENCY - SQL_ATTR_CURSOR_SCROLLABLE - SQL_ATTR_CURSOR_SENSITIVITY - SQL_ATTR_CURSOR_TYPE - SQL_ATTR_PARAM_BIND_TYPE - SQL_ATTR_PARAM_STATUS_PTR - SQL_ATTR_PARAMS_PROCESSED_PTR - SQL_ATTR_PARAMS_ROW_COUNTS_P TR - SQL_ATTR_PARAMS_SET_ROW_COUN TS - SQL_ATTR_PARAMSET_SIZE - SQL_ATTR_PREFETCH_ROWS - SQL_ATTR_ROW_ARRAY_SIZE - SQL_ATTR_ROW_BIND_TYPE - SQL_ATTR_ROW_STATUS_PTR - SQL_ATTR_ROWS_FETCHED_PTR - ALTIBASE_STMT_ATTR_ATOMIC_ARRAY - SQL_ATTR_FETCH_BOOKMARK_PTR - SQL_ATTR_ROW_OPERATION_PTR - SQL_ATTR_USE_BOOKMARKS - SQL_ATTR_CURSOR_HOLD - ALTIBASE_PREPARE_WITH_DESCRIBEPARAM Attribute value which is not currently being supported: - SQL_ATTR_APP_PARAM_DESC - SQL_ATTR_APP_ROW_DESC - SQL_ATTR_ASYNC_ENABLE - SQL_ATTR_ENABLE_AUTO_IPD - SQL_ATTR_IMP_PARAM_DESC - SQL_ATTR_IMP_ROW_DESC - SQL_ATTR_KEYSET_SIZE - SQL_ATTR_MAX_LENGTH - SQL_ATTR_METADATA_ID - SQL_ATTR_NOSCAN - SQL_ATTR_PARAM_BIND_OFFSET_PTR - SQL_ATTR_PARAM_OPERATION_PTR - SQL_ATTR_QUERY_TIMEOUT - SQL_ATTR_RETRIEVE_DATA - SQL_ATTR_ROW_BIND_OFFSET_PTR - SQL_ATTR_ROW_NUMBER - SQL_ATTR_SIMULATE_CURSOR |
| SQLPOINTER | param | Input | Pointer of the value related to the Attribute. Depending on the pointer, Attribute, the param will be a 32-bit integer, the pointer of the Null-terminator, the character string's pointer, the binary pointer, or the value defined in the ODBC. If Attribute is the unique value of the ODBC, param is the integer number with a sign. |
| SQLINTEGER | StringLength | Input | If Attribute has been defined in the ODBC and param indicates the character string or binary buffer, this argument must be the byte length of *param. If Attribute has been defined in the ODBC and param is an integer, this argument is ignored. |
Return Values#
SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR
Description#
The command option for stmt is valid until the option is changed by calling of SQLSetStmtAttr() or till stmt is removed by calling of SQLFreeHandle(). Handle release method: Calling SQL_CLOSE, SQL_UNBIND, or SQL_RESET_PARAMS with SQLFreeStmt() does not reset the statement attribute.
To use a column-wise binding, set SQL_ATTR_PARAM_BIND_TYPE in Arguments Attribute of an application function SQLSetStmtAttr() and set SQL_PARAM_BIND_BY_COLUMN in param. ARRAY_SIZE changes only PARAM_SIZE at every execution moment. PARAM_STATUS_PTR is the vaule to return if each column will be executed, and specifies SQLSMALLINT array. If succeeds, SQL_SUCCESS, SQL_SUCCESS_WITH_INFO will be returned, and if fails, then SQL_PARAM_UNUSED will be returned respectively.
For Macro values: SQL_PARAM_SUCCESS is 0SQL_PARAM_ERROR is 5, SQL_PARAM_SUCCESS_WITH_INFO is 6SQL_PARAM_UNUSED is 7
SQLSetStmtAttr(stmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE, ARRAY_SIZE, 0);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, 0);
Designates the pointer of the variables to store the number of columns processed by PARAMS_PROCESSED_PTR. The pointer type of SQLINTEGER. Then, execute SQLBindParameter() like before.
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0);
Then, executing SQLBINDParameter().
When using the row-wise binding, define the size of the structure in PARAM_Bind_Type, unlike in the column-wise binding.
SQLSetStmtAttr(stmt, SQL_ATTR_PARAM_BIND_TYPE, sizeof(struct…));
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE, ARRAY_SIZE, 0);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, 0);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0);
Then, executing SQLBINDParameter().
Statement Attributes#
| Attribute | Contents |
|---|---|
| SQL_ATTR_CONCURRENCY | SQLUINTEGER value specifying the temporary processing of the cursor. - SQL_CONCUR_READ_ONLY: The cursor supports readonly. Updating is not allowed. - SQL_CONCUR_ROWVER: The concurrency is controlled with row versions. - SQL_CONCUR_LOCK and SQL_CONCUR_VALUES are not supported. |
| SQL_ATTR_CURSOR_SCROLLABLE | The 32-bit integer value which specifies whether or not the open cursor associated with the statement handle is scrollable. |
| SQL_ATTR_CURSOR_SENSITIVITY | The 32-bit integer value which specifies whether or not data modified by another cursor is visible to the open cursor associated with the statement handle. SQL_INSENSITIVE or SQL_SENSITIVE is supported. SQL_UNSPECIFIED is not supported. |
| SQL_ATTR_CURSOR_TYPE | SQLUINTEGER indicating the cursor type. - SQL_CURSOR_FORWARD_ONLY: The cursor only proceeds forward. - SQL_CURSOR_STATIC: The data located in the result set are static. - SQL_CURSOR_KEYSET_DRIVEN: The membership and order of rows included in the cursor are fixed. The Altibase CLI driver supports pure keyset-driven cursors. The SQL_KEYSET_SIZE statement attribute is ignored. |
| SQL_ATTR_PARAM_BIND_TYPE | Setting value which is necessary for array binding To retrieve the data by the column-wise binding, SQL_PARAM_BIND_BY_COLUMN (default) is set in this field. To retrieve the data by the row-wise binding, the length of the structure or the length of the buffer to which the dynamic parameter will be bound will be set. This length must include the bound parameter space. |
| SQL_ATTR_PARAM_STATUS_PTR | This field is requested only when PARAMSET_SIZE is higher than 1. Status values are as follows: - SQL_PARAM_SUCCESS: A SQL statement has been successfully executed. The macro value is 0. - SQL_PARAM_SUCCESS_WITH_INFO: A SQL statement has been successfully executed.: however, the warning data can be viewed from the diagnosis data structure. The macro value is 5. - SQL_PARAM_ERROR: Error occurs during the execution of parameters. additional error information can be viewed on the diagnosis data structure. The macro value is 6. - SQL_PARAM_UNUSED: This parameter set is not used partly because an error has occurred preventing the previous parameter from further proceeding. The macro value is 7. |
| SQL_ATTR_PARAMS_PROCESSED_PTR | (Necessary for array binding) Indicates the buffer that return the number of parameters including the errors. In case of the NULL pointer, no data will be returned. If SQL_SUCCESS or SQL_SUCCESS_WITH_INFO is not returned upon calling of SQLExecDirect() or SQLExecute(), the contents of the buffer will not be defined. |
| SQL_ATTR_PARAMS_ROW_COUNTS_PTR | (Necessary for array binding) Sets the buffer that returns the following values as the result of SQLExecute to array binding. - SQL_SUCCESS: When a SQL statement was successfully executed for all array elements SQL_ERROR When even one array element fails to execute SQL statement. - SQL_NO_DATA: When no array element was changed (inputted or deleted) |
| SQL_ATTR_PARAMS_SET_ROW_COUNTS | - (Necessary for array binding) SQL_ROW_COUNTS_ON: Returns the number of records changed by each array element. In other words, if there are changed records, the number of records will be returned. If there is no record changed, 0 will be returned. In case an error occurs, SQL_USHRT_MAX (65534) will be returned. - SQL_ROW_COUNTS_OFF: Existing behaviors of attribute SQL_ATTR_PARAM_STATUS_PTR will be maintained. |
| SQL_ATTR_PARAMSET_SIZE | (Necessary for array binding) SQLUINTEGER value that indicates the number of values for each parameter. |
| SQL_ATTR_PREFETCH_ROWS | Specifies the number of rows to fetch from the server in one fetch operation. If this attribute is set to 0, the CLI driver fetches the maximum size of data that can be contained in one network packet from the server. A value between 0 and 2147483647 can be specified for this attribute. |
| SQL_ATTR_ROW_ARRAY_SIZE | (Necessary setting value at the time of the array fetch in the SELECT statement) SQLUINTEGER that indicates the number of rows returned by calling each SQLFetch(). |
| SQL_ATTR_ROW_BIND_TYPE | (Necessary setting value at the time of the array fetch in the SELECT statement) SQLUINTEGER that sets the direction of the binding to be used upon calling of SQLFetch(). The column-wise binding is searched by setting SQL_BIND_BY_COLUMN. The row-wise binding searches the data by setting the length of the structure or the length of the buffer to which the results columns will be bound. In case the length is specified, the space for the columns to be bound must be included. |
| SQL_ATTR_ROW_STATUS_PTR | (Necessary setting value at the time of the array fetch in the SELECT statement) The size of the array is same as the number of rows of the row set. In this attribute, the NULL pointer can be set. In this case, the Altibase CLI driver does not return the column status value. |
| SQL_ATTR_ROWS_FETCHED_PTR | (Necessary setting value at the time of the array fetch in the SELECT statement) SQLUINTEGER* value indicating the buffer that returns the number of fetched rows after SQLFetch() is called |
| ALTIBASE_STMT_ATTR_ATOMIC_ARRAY | This is Altibase only attribute indicating to execute Atomic Array Insert. Array Insert can execute the entire statements respectively, whereas Atomic Array Insert can execute several statements at one execution and this results in them executed as a single statement in other words. You may specify ALTIBASE_STMT_ATTR_ATOMIC_ARRAY in Attribute and SQL_TRUE/SQL_FALSE in the param argument. If you choose SQL_TRUE, you can execute Atomic Array Insert. However, you must specify Array Size in order that Atomic Array Insert has better performance than the existing Array Insert does.(SQL_ATTR_PARAMSET_SIZE) And you can save result values with using the following attributes. - SQL_ATTR_PARAM_STATUS_PRT : Real result values are saved only in the first row and the rest is successfully processed. - SQL_ATTR_PARAMS_PROCESSED_PTR : Real result values are saved only in the first row and the rest of them are ignored. |
| SQL_ATTR_FETCH_BOOKMARK_PTR | The pointer which points to a buffer which stores a binary bookmark value. When SQLFetchScroll is called with SQL_FETCH_BOOKMARK, the driver scrolls the row using the bookmark value set for this attribute. The default value of this attribute is the null pointer. The bookmark value set for this attribute is not used for delete, update or fetch by bookmark operations using SQLBulkOperations; SQLBulkOperations uses bookmarks cached in rowset buffers. |
| SQL_ATTR_ROW_OPERATION_PTR | The pointer which points to the array of SQLUSMALLINT values in order to specify which row to ignore or operate on during bulk operations using SQLSetPos. - SQL_ROW_IGNORE: Row to be excluded from bulk operations. - SQL_ROW_PROCEED: Row to be included in bulk operations. The value of this array is not applied during SQLBulkOperations. |
| SQL_ATTR_USE_BOOKMARKS | The SQLULEN value that specifies whether or not to use bookmarks. - SQL_UB_OFF: The default value SQL_UB_ON or SQL_UB_FIXED: Bookmarks with a fixed length of 4 bytes are used. - SQL_UB_VARIABLE: Bookmarks of variable length are used. |
| SQL_ATTR_CURSOR_HOLD | The SQLULEN value which controls the effect of transaction completion on the open cursor. - SQL_CURSOR_HOLD_ON: The cursor is not closed when the transaction is committed. - SQL_CURSOR_HOLD_OFF: The cursor is closed when the transaction is committed. The default value is SQL_CURSOR_HOLD_OFF. |
| ALTIBASE_PREFETCH_ASYNC | Improving fetch performance by performing prefetch asynchronously. Only available for TCP and SSL connections. - ALTIBASE_PREFETCH_ASYNC_OFF: Performs a prefetch synchronously (default). - ALTIBASE_PREFETCH_ASYNC_PREFERRED: Performs a prefetch asynchronously. Asynchronous prefetch can be set for each statement, but only one statement per connection is performed asynchronously. |
| ALTIBASE_PREFETCH_AUTO_TUNING | If a prefetch is performed asynchronously, specify whether to auto-tuning according to the network status. Auto-tuning is a function that automatically adjusts the number of prefetch rows according to network conditions. This feature is only available on Linux. - ALTIBASE_PREFETCH_AUTO_TUNING_OFF: Do not use auto-tuning (default on non-Linux OS) - ALTIBASE_PREFETCH_AUTO_TUNING_ON: Use auto-tuning. (Default on Linux) |
| ALTIBASE_PREPARE_WITH_DESCRIBEPARAM | This property is to reduce network I/O cost by requesting parameter information when preparing. If you need parameter information, you can use it to improve performance. - ALTIBASE_PREPARE_WITH_DESCRIBEPARAM_OFF: Do not request parameter information when preparing. (Default) - ALTIBASE_PREPARE_WITH_DESCRIBEPARAM_ON: Request parameter information when preparing together. |
Diagonsis#
| SQLSTATE | Description | Comments |
|---|---|---|
| HY000 | General error | |
| 24000 | Invalid cursor state | |
| HY010 | Funciton sequence error | |
| HY011 | Attribute cannot be set now | |
| HY013 | Memory management error | |
| HYC00 | Use of unsupported attribute | The value specified for the argument Attribute is not supported by the driver. |
| HY009 | Invalid use of null pointer | |
| HY017 | Invalid use of an automatically allocated descriptor handle | |
| 01S02 | Option value changed | |
| HY024 | Invalid attribute value | The value specified for the given Attribute is invalid. |
| HY024 | Invalid array size |
Example#
< Refer to: $ALTIBASE_HOME/sample/SQLCLI/demo_ex4.cpp >
SQLSetStmtAttr(stmt, SQL_ATTR_PARAM_BIND_TYPE, (void*)sizeof(demo_ex4_data), 0);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE, (void*)10, 0);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMS_PROCESSED_PTR,(void*) &processed_ptr, 0);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAM_STATUS_PTR, (void*)status, 0);
You may use Automic Array Insert.
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) array_size, 0); // Specify Array Size.
SQLSetStmtAttr(stmt, ALTIBASE_STMT_ATTR_ATOMIC_ARRAY, (SQLPOINTER) SQL_TRUE, 0); // Specify Atomic attribute.
The following is an example code which specifies the cursor type.
SQLAllocStmt(sDbc, &sStmt);
SQLSetStmtAttr(sStmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, NULL);
SQLSetStmtAttr(sStmt, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER) sRowStatus, NULL);
/* ... */
SQLExecDirect(sStmt, (SQLCHAR *)"SELECT t1.* FROM t1", SQL_NTS);
while (1)
{
sRC = SQLFetch(sStmt);
if (! SQL_SUCCEEDED(sRC))
{
break;
}
if (sRowStatus == SQL_ROW_DELETED)
{
/* hole */
}
else
{
/* todo */
}
}
...