SQLSetScrollOptions (ODBC 1.0, Level 2)
SQLSetScrollOptions sets options that control the behavior of cursors associated with an hstmt. SQLSetScrollOptions allows the application to specify the type of cursor behavior desired in three areas: concurrency control, sensitivity to changes made by other transactions, and rowset size.
Note In ODBC 2.0, SQLSetScrollOptions has been superceded by the SQL_CURSOR_TYPE, SQL_CONCURRENCY, SQL_KEYSET_SIZE, and SQL_ROWSET_SIZE statement options. ODBC 2.0 drivers must support this function for backwards compatibility; ODBC 2.0 applications should only call this function in ODBC 1.0 drivers.
If an application calls SQLSetScrollOptions, a driver must be able to return the values of the aforementioned statement options with SQLGetStmtOption. For more information, see SQLSetStmtOption.
Syntax
RETCODE SQLSetScrollOptions(hstmt, fConcurrency, crowKeyset, crowRowset)
The SQLSetScrollOptions function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UWORD
|
fConcurrency
|
Input
|
Specifies concurrency control for the cursor and must be one of the following values: |
|
|
|
SQL_CONCUR_READ_ONLY: Cursor is read-only. No updates are allowed. |
|
|
|
SQL_CONCUR_LOCK: Cursor uses the lowest level of locking sufficient to ensure that the row can be updated. |
|
|
|
SQL_CONCUR_ROWVER: Cursor uses optimistic concurrency control, comparing row versions, such as SQLBase ROWID or Sybase TIMESTAMP. |
|
|
|
SQL_CONCUR_VALUES: Cursor uses optimistic concurrency control, comparing values. |
SDWORD
|
crowKeyset
|
Input
|
Number of rows for which to buffer keys. This value must be greater than or equal to crowRowset or one of the following values:
SQL_SCROLL_FORWARD_ONLY: The cursor only scrolls forward.
SQL_SCROLL_STATIC: The data in the result set is static.
SQL_SCROLL_KEYSET_DRIVEN: The driver saves and uses the keys for every row in the result set.
SQL_SCROLL_DYNAMIC: The driver sets crowKeyset to the value of crowRowset.
If crowKeyset is a value greater than crowRowset, the value defines the number of rows in the keyset that are to be buffered by the driver. This reflects a mixed scrollable cursor; the cursor is keyset driven within the keyset and dynamic outside of the keyset. |
UWORD
|
crowRowset
|
Input
|
Number of rows in a rowset. crowRowset defines the number of rows fetched by each call to SQLExtendedFetch; the number of rows that the application buffers. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLSetScrollOptions returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLSetScrollOptions 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.
SQLSTATE
|
Error
|
Description |
01000
|
General warning
|
Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
S1000
|
General error
|
An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause. |
S1001
|
Memory allocation failure
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1010
|
Function sequence error
|
(DM) The specified hstmt was in a prepared or executed state. The function must be called before calling SQLPrepare or SQLExecDirect.
(DM) An asynchronously executing function was called for the hstmt and was still executing when this function was called.
(DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
S1107
|
Row value out of range
|
(DM) The value specified for the argument crowKeyset was less than 1, but was not equal to SQL_SCROLL_FORWARD_ONLY, SQL_SCROLL_STATIC, SQL_SCROLL_KEYSET_DRIVEN, or SQL_SCROLL_DYNAMIC. |
|
|
(DM) The value specified for the argument crowKeyset is greater than 0, but less than crowRowset. |
|
|
(DM) The value specified for the argument crowRowset was 0. |
S1108
|
Concurrency option out of range
|
(DM) The value specified for the argument fConcurrency was not equal to SQL_CONCUR_READ_ONLY, SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER, or SQL_CONCUR_VALUES. |
S1C00
|
Driver not capable
|
The driver or data source does not support the concurrency control option specified in the argument fConcurrency. |
|
|
The driver does not support the cursor model specified in the argument crowKeyset. |
Comments
If an application calls SQLSetScrollOptions for an hstmt, it must do so before it calls SQLPrepare or SQLExecDirect or creating a result set with a catalog function.
The application must specify a buffer in a call to SQLBindCol that is large enough to hold the number of rows specified in crowRowset.
If the application does not call SQLSetScrollOptions, crowRowset has a default value of 1, crowKeyset has a default value of SQL_SCROLL_FORWARD_ONLY, and fConcurrency equals SQL_CONCUR_READ_ONLY.
For more information concerning scrollable cursors, see "Using Block and Scrollable Cursors" in Chapter 7, "Retrieving Results."
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Positioning the cursor in a rowset
|
SQLSetPos (extension) |
Setting a statement option
|
SQLSetStmtOption |
SQLSetStmtOption (ODBC 1.0, Level 1)
SQLSetStmtOption sets options related to an hstmt. To set an option for all statements associated with a specific hdbc, an application can call SQLSetConnectOption.
Syntax
RETCODE SQLSetStmtOption(hstmt, fOption, vParam)
The SQLSetStmtOption function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UWORD
|
fOption
|
Input
|
Option to set, listed in "Comments." |
UDWORD
|
vParam
|
Input
|
Value associated with fOption. Depending on the value of fOption, vParam will be a 32-bit integer value or point to a null-terminated character string. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLSetStmtOption returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLSetStmtOption 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.
SQLSTATE
|
Error
|
Description |
01000
|
General warning
|
Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
01S02
|
Option value changed
|
The driver did not support the specified value of the vParam argument and substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.) |
08S01
|
Communication link failure
|
The communication link between the driver and the data source to which the driver was connected failed before the function completed processing. |
24000
|
Invalid cursor state
|
The fOption was SQL_CONCURRENCY, SQL_CURSOR_TYPE, SQL_SIMULATE_CURSOR, or SQL_USE_BOOKMARKS and the cursor was open. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
S1000
|
General error
|
An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause. |
S1001
|
Memory allocation failure
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1009
|
Invalid argument value
|
Given the specified fOption value, an invalid value was specified for the argument vParam. (The Driver Manager returns this SQLSTATE only for statement options that accept a discrete set of values, such as SQL_ASYNC_ENABLE. For all other statement options, the driver must verify the value of the argument vParam.) |
S1010
|
Function sequence error
|
(DM) An asynchronously executing function was called for the hstmt and was still executing when this function was called.
(DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
S1011
|
Operation invalid at this time
|
The fOption was SQL_CONCURRENCY, SQL_CURSOR_TYPE, SQL_SIMULATE_CURSOR, or SQL_USE_BOOKMARKS and the statement was prepared. |
S1092
|
Option type out of range
|
(DM) The value specified for the argument fOption was in the block of numbers reserved for ODBC connection and statement options, but was not valid for the version of ODBC supported by the driver. |
S1C00
|
Driver not capable
|
The value specified for the argument fOption was a valid ODBC statement option for the version of ODBC supported by the driver, but was not supported by the driver.
The value specified for the argument fOption was in the block of numbers reserved for driver-specific connection and statement options, but was not supported by the driver. |
Comments
Statement options for an hstmt remain in effect until they are changed by another call to SQLSetStmtOption or the hstmt is dropped by calling SQLFreeStmt with the SQL_DROP option. Calling SQLFreeStmt with the SQL_CLOSE, SQL_UNBIND, or SQL_RESET_PARAMS options does not reset statement options.
Some statement options support substitution of a similar value if the data source does not support the specified value of vParam. In such cases, the driver returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed). For example, if fOption is SQL_CONCURRENCY, vParam is SQL_CONCUR_ROWVER, and the data source does not support this, the driver substitutes SQL_CONCUR_VALUES. To determine the substituted value, an application calls SQLGetStmtOption.
The currently defined options and the version of ODBC in which they were introduced are shown below; it is expected that more will be defined to take advantage of different data sources. Options from 0 to 999 are reserved by ODBC; driver developers must reserve values greater than or equal to SQL_CONNECT_OPT_DRVR_START for driver-specific use.
The format of information set with vParam depends on the specified fOption. SQLSetStmtOption accepts option information in one of two different formats: a null-terminated character string or a 32-bit integer value. The format of each is noted in the option’s description. This format applies to the information returned for each option in SQLGetStmtOption. Character strings pointed to by the vParam argument of SQLSetStmtOption have a maximum length of SQL_MAX_OPTION_STRING_LENGTH bytes (excluding the null termination byte).
fOption
|
vParam Contents
|
SQL_ASYNC_ENABLE
(ODBC 1.0)
|
A 32-bit integer value that specifies whether a function called with the specified hstmt is executed asynchronously:
SQL_ASYNC_ENABLE_OFF = Off (the default)
SQL_ASYNC_ENABLE_ON = On
Once a function has been called asynchronously, no other functions can be called on the hstmt or the hdbc associated with the hstmt except for the original function, SQLAllocStmt, SQLCancel, or SQLGetFunctions, until the original function returns a code other than SQL_STILL_EXECUTING. Any other function called on the hstmt returns SQL_ERROR with an SQLSTATE of S1010 (Function sequence error). Functions can be called on other hstmts. For more information, see "Executing Functions Asynchronously" in Chapter 6.
|
|
The following functions can be executed asynchronously:
|
|
SQLColAttributes
SQLColumnPrivileges
SQLColumns
SQLDescribeCol
SQLDescribeParam
SQLExecDirect
SQLExecute
SQLExtendedFetch
SQLFetch
SQLForeignKeys
SQLGetData
SQLGetTypeInfo
SQLMoreResults
|
SQLNumParams
SQLNumResultCols
SQLParamData
SQLPrepare
SQLPrimaryKeys
SQLProcedureColumns
SQLProcedures
SQLPutData
SQLSetPos
SQLSpecialColumns
SQLStatistics
SQLTablePrivileges
SQLTables |
SQL_BIND_TYPE
(ODBC 1.0)
|
A 32-bit integer value that sets the binding orientation to be used when SQLExtendedFetch is called on the associated hstmt. Column-wise binding is selected by supplying the defined constant SQL_BIND_BY_COLUMN for the argument vParam. Row-wise binding is selected by supplying a value for vParam specifying the length of a structure or an instance of a buffer into which result columns will be bound.
The length specified in vParam must include space for all of the bound columns and any padding of the structure or buffer to ensure that when the address of a bound column is incremented with the specified length, the result will point to the beginning of the same column in the next row. When using the sizeof operator with structures or unions in ANSI C, this behavior is guaranteed.
Column-wise binding is the default binding orientation for SQLExtendedFetch.
|
SQL_CONCURRENCY
(ODBC 2.0)
|
A 32-bit integer value that specifies the cursor concurrency:
SQL_CONCUR_READ_ONLY = Cursor is read-only. No updates are allowed.
SQL_CONCUR_LOCK = Cursor uses the lowest level of locking sufficient to ensure that the row can be updated.
SQL_CONCUR_ROWVER = Cursor uses optimistic concurrency control, comparing row versions, such as SQLBase ROWID or Sybase TIMESTAMP.
SQL_CONCUR_VALUES = Cursor uses optimistic concurrency control, comparing values.
The default value is SQL_CONCUR_READ_ONLY. This option cannot be specified for an open cursor and can also be set through the fConcurrency argument in SQLSetScrollOptions.
If the specified concurrency is not supported by the data source, the driver substitutes a different concurrency and returns SQLSTATE 01S02 (Option value changed). For SQL_CONCUR_VALUES, the driver substitutes SQL_CONCUR_ROWVER, and vice versa. For SQL_CONCUR_LOCK, the driver substitutes, in order, SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES.
|
SQL_CURSOR_TYPE
(ODBC 2.0)
|
A 32-bit integer value that specifies the cursor type:
SQL_CURSOR_FORWARD_ONLY = The cursor only scrolls forward.
SQL_CURSOR_STATIC = The data in the result set is static.
SQL_CURSOR_KEYSET_DRIVEN = The driver saves and uses the keys for the number of rows specified in the SQL_KEYSET_SIZE statement option.
SQL_CURSOR_DYNAMIC = The driver only saves and uses the keys for the rows in the rowset.
The default value is SQL_CURSOR_FORWARD_ONLY. This option cannot be specified for an open cursor and can also be set through the crowKeyset argument in SQLSetScrollOptions.
If the specified cursor type is not supported by the data source, the driver substitutes a different cursor type and returns SQLSTATE 01S02 (Option value changed). For a mixed or dynamic cursor, the driver substitutes, in order, a keyset-driven or static cursor. For a keyset-driven cursor, the driver substitutes a static cursor.
|
SQL_KEYSET_SIZE
(ODBC 2.0)
|
A 32-bit integer value that specifies the number of rows in the keyset for a keyset-driven cursor. If the keyset size is 0 (the default), the cursor is fully keyset-driven. If the keyset size is greater than 0, the cursor is mixed (keyset-driven within the keyset and dynamic outside of the keyset). The default keyset size is 0.
If the specified size exceeds the maximum keyset size, the driver substitutes that size and returns SQLSTATE 01S02 (Option value changed).
SQLExtendedFetch returns an error if the keyset size is greater than 0 and less than the rowset size.
|
SQL_MAX_LENGTH
(ODBC 1.0)
|
A 32-bit integer value that specifies the maximum amount of data that the driver returns from a character or binary column. If vParam is less than the length of the available data, SQLFetch or SQLGetData truncates the data and returns SQL_SUCCESS. If vParam is 0 (the default), the driver attempts to return all available data.
If the specified length is less than the minimum amount of data that the data source can return (the minimum is 254 bytes on many data sources), or greater than the maximum amount of data that the data source can return, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).
This option is intended to reduce network traffic and should only be supported when the data source (as opposed to the driver) in a multiple-tier driver can implement it. To truncate data, an application should specify the maximum buffer length in the cbValueMax argument in SQLBindCol or SQLGetData.
Note In ODBC 1.0, this statement option only applied to SQL_LONGVARCHAR and SQL_LONGVARBINARY columns.
|
SQL_MAX_ROWS
(ODBC 1.0)
|
A 32-bit integer value corresponding to the maximum number of rows to return to the application for a SELECT statement. If vParam equals 0 (the default), then the driver returns all rows.
This option is intended to reduce network traffic. Conceptually, it is applied when the result set is created and limits the result set to the first vParam rows.
If the specified number of rows exceeds the number of rows that can be returned by the data source, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).
|
SQL_NOSCAN
(ODBC 1.0)
|
A 32-bit integer value that specifies whether the driver does not scan SQL strings for escape clauses:
SQL_NOSCAN_OFF = The driver scans SQL strings for escape clauses (the default).
SQL_NOSCAN_ON = The driver does not scan SQL strings for escape clauses. Instead, the driver sends the statement directly to the data source.
|
SQL_QUERY_
TIMEOUT
(ODBC 1.0)
|
A 32-bit integer value corresponding to the number of seconds to wait for an SQL statement to execute before returning to the application. If vParam equals 0 (the default), then there is no time out.
If the specified timeout exceeds the maximum timeout in the data source or is smaller than the minimum timeout, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).
Note that the application need not call SQLFreeStmt with the SQL_CLOSE option to reuse the hstmt if a SELECT statement timed out.
|
SQL_RETRIEVE_DATA
(ODBC 2.0)
|
A 32-bit integer value:
SQL_RD_ON = SQLExtendedFetch retrieves data after it positions the cursor to the specified location. This is the default.
SQL_RD_OFF = SQLExtendedFetch does not retrieve data after it positions the cursor.
By setting SQL_RETRIEVE_DATA to SQL_RD_OFF, an application can verify if a row exists or retrieve a bookmark for the row without incurring the overhead of retrieving rows.
|
SQL_ROWSET_SIZE
(ODBC 2.0)
|
A 32-bit integer value that specifies the number of rows in the rowset. This is the number of rows returned by each call to SQLExtendedFetch. The default value is 1.
If the specified rowset size exceeds the maximum rowset size supported by the data source, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).
This option can be specified for an open cursor and can also be set through the crowRowset argument in SQLSetScrollOptions.
|
SQL_SIMULATE_
CURSOR
(ODBC 2.0)
|
A 32-bit integer value that specifies whether drivers that simulate positioned update and delete statements guarantee that such statements affect only one single row.
To simulate positioned update and delete statements, most drivers construct a searched UPDATE or DELETE statement containing a WHERE clause that specifies the value of each column in the current row. Unless these columns comprise a unique key, such a statement may affect more than one row.
To guarantee that such statements affect only one row, the driver determines the columns in a unique key and adds these columns to the result set. If an application guarantees that the columns in the result set comprise a unique key, the driver is not required to do so. This may reduce execution time.
SQL_SC_NON_UNIQUE = The driver does not guarantee that simulated positioned update or delete statements will affect only one row; it is the application’s responsibility to do so. If a statement affects more than one row, SQLExecute or SQLExecDirect returns SQLSTATE 01000 (General warning).
SQL_SC_TRY_UNIQUE = The driver attempts to guarantee that simulated positioned update or delete statements affect only one row. The driver always executes such statements, even if they might affect more than one row, such as when there is no unique key. If a statement affects more than one row, SQLExecute or SQLExecDirect returns SQLSTATE 01000 (General warning).
SQL_SC_UNIQUE = The driver guarantees that simulated positioned update or delete statements affect only one row. If the driver cannot guarantee this for a given statement, SQLExecDirect or SQLPrepare returns an error.
If the specified cursor simulation type is not supported by the data source, the driver substitutes a different simulation type and returns SQLSTATE 01S02 (Option value changed). For SQL_SC_UNIQUE, the driver substitutes, in order, SQL_SC_TRY_UNIQUE or SQL_SC_NON_UNIQUE. For SQL_SC_TRY_UNIQUE, the driver substitutes SQL_SC_NON_UNIQUE.
If a driver does not simulate positioned update and delete statements, it returns SQLSTATE S1C00 (Driver not capable).
|
SQL_USE_
BOOKMARKS
(ODBC 2.0)
|
A 32-bit integer value that specifies whether an application will use bookmarks with a cursor:
SQL_UB_OFF = Off (the default)
SQL_UB_ON = On
To use bookmarks with a cursor, the application must specify this option with the SQL_UB_ON value before opening the cursor.
|
Code Example
See SQLExtendedFetch.
Related Functions
For information about
|
See |
Canceling statement processing
|
SQLCancel |
Returning the setting of a connection option
|
SQLGetConnectOption (extension) |
Returning the setting of a statement option
|
SQLGetStmtOption (extension) |
Setting a connection option
|
SQLSetConnectOption (extension) |
SQLSpecialColumns (ODBC 1.0, Level 1)
SQLSpecialColumns retrieves the following information about columns within a specified table:
- The optimal set of columns that uniquely identifies a row in the table.
- Columns that are automatically updated when any value in the row is updated by a transaction.
Syntax
RETCODE SQLSpecialColumns(hstmt, fColType, szTableQualifier, cbTableQualifier, szTableOwner, cbTableOwner, szTableName, cbTableName, fScope, fNullable)
The SQLSpecialColumns function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UWORD
|
fColType
|
Input
|
Type of column to return. Must be one of the following values:
SQL_BEST_ROWID: Returns the optimal column or set of columns that, by retrieving values from the column or columns, allows any row in the specified table to be uniquely identified. A column can be either a pseudocolumn specifically designed for this purpose (as in Oracle ROWID or Ingres TID) or the column or columns of any unique index for the table.
SQL_ROWVER: Returns the column or columns in the specified table, if any, that are automatically updated by the data source when any value in the row is updated by any transaction (as in SQLBase ROWID or Sybase TIMESTAMP). |
UCHAR FAR *
|
szTableQualifier
|
Input
|
Qualifier name for the table. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have qualifiers. |
SWORD
|
cbTableQualifier
|
Input
|
Length of szTableQualifier. |
UCHAR FAR *
|
szTableOwner
|
Input
|
Owner name for the table. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have owners. |
SWORD
|
cbTableOwner
|
Input
|
Length of szTableOwner. |
UCHAR FAR *
|
szTableName
|
Input
|
Table name. |
SWORD
|
cbTableName
|
Input
|
Length of szTableName. |
UWORD
|
fScope
|
Input
|
Minimum required scope of the rowid. The returned rowid may be of greater scope. Must be one of the following:
SQL_SCOPE_CURROW: The rowid is guaranteed to be valid only while positioned on that row. A later reselect using rowid may not return a row if the row was updated or deleted by another transaction.
SQL_SCOPE_TRANSACTION: The rowid is guaranteed to be valid for the duration of the current transaction.
SQL_SCOPE_SESSION: The rowid is guaranteed to be valid for the duration of the session (across transaction boundaries). |
UWORD
|
fNullable
|
Input
|
Determines whether to return special columns that can have a NULL value. Must be one of the following:
SQL_NO_NULLS: Exclude special columns that can have NULL values.
SQL_NULLABLE: Return special columns even if they can have NULL values. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLSpecialColumns returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLSpecialColumns 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.
SQLSTATE
|
Error
|
Description |
01000
|
General warning
|
Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
08S01
|
Communication link failure
|
The communication link between the driver and the data source to which the driver was connected failed before the function completed processing. |
24000
|
Invalid cursor state
|
(DM) A cursor was open on the hstmt and SQLFetch or SQLExtendedFetch had been called.
A cursor was open on the hstmt but SQLFetch or SQLExtendedFetch had not been called. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
S1000
|
General error
|
An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause. |
S1001
|
Memory allocation failure
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing function (not this one) was called for the hstmt and was still executing when this function was called.
(DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
S1090
|
Invalid string or buffer length
|
(DM) The value of one of the length arguments was less than 0, but not equal to SQL_NTS. |
|
|
The value of one of the length arguments exceeded the maximum length value for the corresponding qualifier or name. The maximum length of each qualifier or name may be obtained by calling SQLGetInfo with the fInfoType values: SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, or SQL_MAX_TABLE_NAME_LEN. |
S1097
|
Column type out of range
|
(DM) An invalid fColType value was specified. |
S1098
|
Scope type out of range
|
(DM) An invalid fScope value was specified. |
S1099
|
Nullable type out of range
|
(DM) An invalid fNullable value was specified. |
S1C00
|
Driver not capable
|
A table qualifier was specified and the driver or data source does not support qualifiers.
A table owner was specified and the driver or data source does not support owners. |
|
|
The combination of the current settings of the SQL_CONCURRENCY and SQL_CURSOR_TYPE statement options was not supported by the driver or data source. |
S1T00
|
Timeout expired
|
The timeout period expired before the data source returned the requested result set. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT. |
Comments
SQLSpecialColumns is provided so that applications can provide their own custom scrollable-cursor functionality, similar to that provided by SQLExtendedFetch and SQLSetStmtOption.
When the fColType argument is SQL_BEST_ROWID, SQLSpecialColumns returns the column or columns that uniquely identify each row in the table. These columns can always be used in a select-list or WHERE clause. However, SQLColumns does not necessarily return these columns. For example, SQLColumns might not return the Oracle ROWID pseudo-column ROWID. If there are no columns that uniquely identify each row in the table, SQLSpecialColumns returns a rowset with no rows; a subsequent call to SQLFetch or SQLExtendedFetch on the hstmt returns SQL_NO_DATA_FOUND.
If the fColType, fScope, or fNullable arguments specify characteristics that are not supported by the data source, SQLSpecialColumns returns a result set with no rows (as opposed to the function returning SQL_ERROR with SQLSTATE S1C00 (Driver not capable)). A subsequent call to SQLFetch or SQLExtendedFetch on the hstmt will return SQL_NO_DATA_FOUND.
SQLSpecialColumns returns the results as a standard result set, ordered by SCOPE. The following table lists the columns in the result set.
The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual length of the COLUMN_NAME column, an application can call SQLGetInfo with the SQL_MAX_COLUMN_NAME_LEN option.
Column Name
|
Data Type
|
Comments |
SCOPE
|
Smallint
|
Actual scope of the rowid. Contains one of the following values:
SQL_SCOPE_CURROW
SQL_SCOPE_TRANSACTION
SQL_SCOPE_SESSION
NULL is returned when fColType is SQL_ROWVER. For a description of each value, see the description of fScope in the "Syntax" section above. |
COLUMN_NAME
|
Varchar(128)
not NULL
|
Column identifier. |
DATA_TYPE
|
Smallint
not NULL
|
SQL data type. This can be an ODBC SQL data type or a driver-specific SQL data type. For a list of valid ODBC SQL data types, see "SQL Data Types" in Appendix D, "Data Types." For information about driver-specific SQL data types, see the driver’s documentation. |
TYPE_NAME
|
Varchar(128)
not NULL
|
Data source–dependent data type name; for example, "CHAR", "VARCHAR", "MONEY", "LONG VARBINARY", or "CHAR ( ) FOR BIT DATA". |
PRECISION
|
Integer
|
The precision of the column on the data source. NULL is returned for data types where precision is not applicable. For more information concerning precision, see "Precision, Scale, Length, and Display Size," in Appendix D, "Data Types." |
LENGTH
|
Integer
|
The length in bytes of data transferred on an SQLGetData or SQLFetch operation if SQL_C_DEFAULT is specified. For numeric data, this size may be different than the size of the data stored on the data source. This value is the same as the PRECISION column for character or binary data. For more information, see "Precision, Scale, Length, and Display Size," in Appendix D, "Data Types." |
SCALE
|
Smallint
|
The scale of the column on the data source. NULL is returned for data types where scale is not applicable. For more information concerning scale, see "Precision, Scale, Length, and Display Size," in Appendix D, "Data Types." |
PSEUDO_COLUMN
|
Smallint
|
Indicates whether the column is a pseudo-column, such as Oracle ROWID:
SQL_PC_UNKNOWN
SQL_PC_PSEUDO
SQL_PC_NOT_PSEUDO
Note For maximum interoperability, pseudo-columns should not be quoted with the identifier quote character returned by SQLGetInfo. |
Note The PSEUDO_COLUMN column was added in ODBC 2.0. ODBC 1.0 drivers might return a different, driver-specific column with the same column number.
Once the application retrieves values for SQL_BEST_ROWID, the application can use these values to reselect that row within the defined scope. The SELECT statement is guaranteed to return either no rows or one row.
If an application reselects a row based on the rowid column or columns and the row is not found, then the application can assume that the row was deleted or the rowid columns were modified. The opposite is not true: even if the rowid has not changed, the other columns in the row may have changed.
Columns returned for column type SQL_BEST_ROWID are useful for applications that need to scroll forwards and backwards within a result set to retrieve the most recent data from a set of rows. The column or columns of the rowid are guaranteed not to change while positioned on that row.
The column or columns of the rowid may remain valid even when the cursor is not positioned on the row; the application can determine this by checking the SCOPE column in the result set.
Columns returned for column type SQL_ROWVER are useful for applications that need the ability to check if any columns in a given row have been updated while the row was reselected using the rowid. For example, after reselecting a row using rowid, the application can compare the previous values in the SQL_ROWVER columns to the ones just fetched. If the value in a SQL_ROWVER column differs from the previous value, the application can alert the user that data on the display has changed.
Code Example
For a code example of a similar function, see SQLColumns.
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Returning the columns in a table or tables
|
SQLColumns (extension) |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Fetching a row of data
|
SQLFetch |
Returning the columns of a primary key
|
SQLPrimaryKeys (extension) |
SQLStatistics (ODBC 1.0, Level 1)
SQLStatistics retrieves a list of statistics about a single table and the indexes associated with the table. The driver returns the information as a result set.
Syntax
RETCODE SQLStatistics(hstmt, szTableQualifier, cbTableQualifier,
szTableOwner, cbTableOwner, szTableName, cbTableName, fUnique, fAccuracy)
The SQLStatistics function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UCHAR FAR *
|
szTableQualifier
|
Input
|
Qualifier name. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have qualifiers. |
SWORD
|
cbTableQualifier
|
Input
|
Length of szTableQualifier. |
UCHAR FAR *
|
szTableOwner
|
Input
|
Owner name. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have owners. |
SWORD
|
cbTableOwner
|
Input
|
Length of szTableOwner. |
UCHAR FAR *
|
szTableName
|
Input
|
Table name. |
SWORD
|
cbTableName
|
Input
|
Length of szTableName. |
UWORD
|
fUnique
|
Input
|
Type of index: SQL_INDEX_UNIQUE or SQL_INDEX_ALL. |
UWORD
|
fAccuracy
|
Input
|
The importance of the CARDINALITY and PAGES columns in the result set:
SQL_ENSURE requests that the driver unconditionally retrieve the statistics.
SQL_QUICK requests that the driver retrieve results only if they are readily available from the server. In this case, the driver does not ensure that the values are current. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLStatistics returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLStatistics 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.
SQLSTATE
|
Error
|
Description |
01000
|
General warning
|
Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
08S01
|
Communication link failure
|
The communication link between the driver and the data source to which the driver was connected failed before the function completed processing. |
24000
|
Invalid cursor state
|
(DM) A cursor was open on the hstmt and SQLFetch or SQLExtendedFetch had been called.
A cursor was open on the hstmt but SQLFetch or SQLExtendedFetch had not been called. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
S1000
|
General error
|
An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause. |
S1001
|
Memory allocation failure
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing function (not this one) was called for the hstmt and was still executing when this function was called.
(DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
S1090
|
Invalid string or buffer length
|
(DM) The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. |
|
|
The value of one of the name length arguments exceeded the maximum length value for the corresponding qualifier or name. |
S1100
|
Uniqueness option type out of range
|
(DM) An invalid fUnique value was specified. |
S1101
|
Accuracy option type out of range
|
(DM) An invalid fAccuracy value was specified. |
S1C00
|
Driver not capable
|
A table qualifier was specified and the driver or data source does not support qualifiers.
A table owner was specified and the driver or data source does not support owners. |
|
|
The combination of the current settings of the SQL_CONCURRENCY and SQL_CURSOR_TYPE statement options was not supported by the driver or data source. |
S1T00
|
Timeout expired
|
The timeout period expired before the data source returned the requested result set. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT. |
Comments
SQLStatistics returns information about a single table as a standard result set, ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, and SEQ_IN_INDEX. The result set combines statistics information for the table with information about each index. The following table lists the columns in the result set.
Note SQLStatistics might not return all indexes. For example, an Xbase driver might only return indexes in files in the current directory. Applications can use any valid index, regardless of whether it is returned by SQLStatistics.
The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual lengths of the TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, and COLUMN_NAME columns, an application can call SQLGetInfo with the SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN options.
Column Name
|
Data Type
|
Comments |
TABLE_QUALIFIER
|
Varchar(128)
|
Table qualifier identifier of the table to which the statistic or index applies; NULL if not applicable to the data source. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have qualifiers. |
TABLE_OWNER
|
Varchar(128)
|
Table owner identifier of the table to which the statistic or index applies; NULL if not applicable to the data source. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have owners. |
TABLE_NAME
|
Varchar(128)
not NULL
|
Table identifier of the table to which the statistic or index applies. |
NON_UNIQUE
|
Smallint
|
Indicates whether the index prohibits duplicate values:
TRUE if the index values can be nonunique.
FALSE if the index values must be unique.
NULL is returned if TYPE is SQL_TABLE_STAT. |
INDEX_QUALIFIER
|
Varchar(128)
|
The identifier that is used to qualify the index name doing a DROP INDEX; NULL is returned if an index qualifier is not supported by the data source or if TYPE is SQL_TABLE_STAT. If a non-null value is returned in this column, it must be used to qualify the index name on a DROP INDEX statement; otherwise the TABLE_OWNER name should be used to qualify the index name. |
INDEX_NAME
|
Varchar(128)
|
Index identifier; NULL is returned if TYPE is SQL_TABLE_STAT. |
TYPE
|
Smallint
not NULL
|
Type of information being returned:
SQL_TABLE_STAT indicates a statistic for the table.
SQL_INDEX_CLUSTERED indicates a clustered index.
SQL_INDEX_HASHED indicates a hashed index.
SQL_INDEX_OTHER indicates another type of index. |
SEQ_IN_INDEX
|
Smallint
|
Column sequence number in index (starting with 1); NULL is returned if TYPE is SQL_TABLE_STAT. |
COLUMN_NAME
|
Varchar(128)
|
Column identifier. If the column is based on an expression, such as SALARY + BENEFITS, the expression is returned; if the expression cannot be determined, an empty string is returned. If the index is a filtered index, each column in the filter condition is returned; this may require more than one row. NULL is returned if TYPE is SQL_TABLE_STAT. |
COLLATION
|
Char(1)
|
Sort sequence for the column; "A" for ascending; "D" for descending; NULL is returned if column sort sequence is not supported by the data source or if TYPE is SQL_TABLE_STAT. |
CARDINALITY
|
Integer
|
Cardinality of table or index; number of rows in table if TYPE is SQL_TABLE_STAT; number of unique values in the index if TYPE is not SQL_TABLE_STAT; NULL is returned if the value is not available from the data source. |
PAGES
|
Integer
|
Number of pages used to store the index or table; number of pages for the table if TYPE is SQL_TABLE_STAT; number of pages for the index if TYPE is not SQL_TABLE_STAT; NULL is returned if the value is not available from the data source, or if not applicable to the data source. |
FILTER_CONDITION
|
Varchar(128)
|
If the index is a filtered index, this is the filter condition, such as SALARY > 30000; if the filter condition cannot be determined, this is an empty string.
NULL if the index is not a filtered index, it cannot be determined whether the index is a filtered index, or TYPE is SQL_TABLE_STAT. |
Note The FILTER_CONDITION column was added in ODBC 2.0. ODBC 1.0 drivers might return a different, driver-specific column with the same column number.
If the row in the result set corresponds to a table, the driver sets TYPE to SQL_TABLE_STAT and sets NON_UNIQUE, INDEX_QUALIFIER, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, and COLLATION to NULL. If CARDINALITY or PAGES are not available from the data source, the driver sets them to NULL.
Code Example
For a code example of a similar function, see SQLColumns.
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Fetching a row of data
|
SQLFetch |
Returning the columns of foreign keys
|
SQLForeignKeys (extension) |
Returning the columns of a primary key
|
SQLPrimaryKeys (extension) |
SQLTablePrivileges (ODBC 1.0, Level 2)
SQLTablePrivileges returns a list of tables and the privileges associated with each table. The driver returns the information as a result set on the specified hstmt.
Syntax
RETCODE SQLTablePrivileges(hstmt, szTableQualifier, cbTableQualifier,
szTableOwner, cbTableOwner, szTableName, cbTableName)
The SQLTablePrivileges function accepts the following arguments.
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UCHAR FAR *
|
szTableQualifier
|
Input
|
Table qualifier. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have qualifiers. |
SWORD
|
cbTableQualifier
|
Input
|
Length of szTableQualifier. |
UCHAR FAR *
|
szTableOwner
|
Input
|
String search pattern for owner names. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have owners. |
SWORD
|
cbTableOwner
|
Input
|
Length of szTableOwner. |
UCHAR FAR *
|
szTableName
|
Input
|
String search pattern for table names. |
|
SWORD
|
cbTableName
|
Input
|
Length of szTableName. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLTablePrivileges returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLTablePrivileges 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.
SQLSTATE
|
Error
|
Description |
01000
|
General warning
|
Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
08S01
|
Communication link failure
|
The communication link between the driver and the data source to which the driver was connected failed before the function completed processing. |
24000
|
Invalid cursor state
|
(DM) A cursor was open on the hstmt and SQLFetch or SQLExtendedFetch had been called.
A cursor was open on the hstmt but SQLFetch or SQLExtendedFetch had not been called. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
S1000
|
General error
|
An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause. |
S1001
|
Memory allocation failure
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing function (not this one) was called for the hstmt and was still executing when this function was called.
(DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
S1090
|
Invalid string or buffer length
|
(DM) The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. |
|
|
The value of one of the name length arguments exceeded the maximum length value for the corresponding qualifier or name. |
S1C00
|
Driver not capable
|
A table qualifier was specified and the driver or data source does not support qualifiers.
A table owner was specified and the driver or data source does not support owners. |
|
|
A string search pattern was specified for the table owner, table name, or column name and the data source does not support search patterns for one or more of those arguments.
The combination of the current settings of the SQL_CONCURRENCY and SQL_CURSOR_TYPE statement options was not supported by the driver or data source. |
S1T00
|
Timeout expired
|
The timeout period expired before the data source returned the result set. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT. |
Comments
The szTableOwner and szTableName arguments accept search patterns. For more information about valid search patterns, see "Search Pattern Arguments" earlier in this chapter.
SQLTablePrivileges returns the results as a standard result set, ordered by TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, and PRIVILEGE. The following table lists the columns in the result set.
Note SQLTablePrivileges might not return privileges for all tables. For example, an Xbase driver might only return privileges for files (tables) in the current directory. Applications can use any valid table, regardless of whether it is returned by SQLTablePrivileges.
The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual lengths of the TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME columns, an application can call SQLGetInfo with the SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, and SQL_MAX_TABLE_NAME_LEN options.
Column Name
|
Data Type
|
Comments |
TABLE_QUALIFIER
|
Varchar(128)
|
Table qualifier identifier; NULL if not applicable to the data source. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have qualifiers. |
TABLE_OWNER
|
Varchar(128)
|
Table owner identifier; NULL if not applicable to the data source. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have owners. |
TABLE_NAME
|
Varchar(128)
not NULL
|
Table identifier. |
GRANTOR
|
Varchar(128)
|
Identifier of the user who granted the privilege; NULL if not applicable to the data source. |
GRANTEE
|
Varchar(128)
not NULL
|
Identifier of the user to whom the privilege was granted. |
PRIVILEGE
|
Varchar(128)
not NULL
|
Identifies the table privilege. May be one of the following or a data source–specific privilege.
SELECT: The grantee is permitted to retrieve data for one or more columns of the table.
INSERT: The grantee is permitted to insert new rows containing data for one or more columns into to the table.
UPDATE: The grantee is permitted to update the data in one or more columns of the table.
DELETE: The grantee is permitted to delete rows of data from the table.
REFERENCES: The grantee is permitted to refer to one or more columns of the table within a constraint (for example, a unique, referential, or table check constraint).
The scope of action permitted the grantee by a given table privilege is data source–dependent. For example, the UPDATE privilege might permit the grantee to update all columns in a table on one data source and only those columns for which the grantor has the UPDATE privilege on another data source. |
IS_GRANTABLE
|
Varchar(3)
|
Indicates whether the grantee is permitted to grant the privilege to other users; "YES", "NO", or NULL if unknown or not applicable to the data source. |
Code Example
For a code example of a similar function, see SQLColumns.
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Returning privileges for a column or columns
|
SQLColumnPrivileges (extension) |
Returning the columns in a table or tables
|
SQLColumns (extension) |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Fetching a row of data
|
SQLFetch |
Returning table statistics and indexes
|
SQLStatistics (extension) |
Returning a list of tables in a data source
|
SQLTables (extension) |
SQLTables (ODBC 1.0, Level 1)
SQLTables returns the list of table names stored in a specific data source. The driver returns the information as a result set.
Syntax
RETCODE SQLTables(hstmt, szTableQualifier, cbTableQualifier, szTableOwner, cbTableOwner, szTableName, cbTableName, szTableType, cbTableType)
The SQLTables function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle for retrieved results. |
UCHAR FAR *
|
szTableQualifier
|
Input
|
Qualifier name. If a driver supports qualifiers for some tables but not for others, such as when a driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have qualifiers. |
SWORD
|
cbTableQualifier
|
Input
|
Length of szTableQualifier. |
UCHAR FAR *
|
szTableOwner
|
Input
|
String search pattern for owner names. |
SWORD
|
cbTableOwner
|
Input
|
Length of szTableOwner. |
UCHAR FAR *
|
szTableName
|
Input
|
String search pattern for table names. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have owners. |
SWORD
|
cbTableName
|
Input
|
Length of szTableName. |
UCHAR FAR *
|
szTableType
|
Input
|
List of table types to match. |
SWORD
|
cbTableType
|
Input
|
Length of szTableType. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR or SQL_INVALID_HANDLE.
Diagnostics
When SQLTables returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLTables 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.
SQLSTATE
|
Error
|
Description |
01000
|
General warning
|
Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
08S01
|
Communication link failure
|
The communication link between the driver and the data source to which the driver was connected failed before the function completed processing. |
24000
|
Invalid cursor state
|
(DM) A cursor was open on the hstmt and SQLFetch or SQLExtendedFetch had been called.
A cursor was open on the hstmt but SQLFetch or SQLExtendedFetch had not been called. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
S1000
|
General error
|
An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause. |
S1001
|
Memory allocation failure
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing function (not this one) was called for the hstmt and was still executing when this function was called.
(DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
S1090
|
Invalid string or buffer length
|
(DM) The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. |
|
|
The value of one of the name length arguments exceeded the maximum length value for the corresponding qualifier or name. |
S1C00
|
Driver not capable
|
A table qualifier was specified and the driver or data source does not support qualifiers.
A table owner was specified and the driver or data source does not support owners. |
|
|
A string search pattern was specified for the table owner or table name and the data source does not support search patterns for one or more of those arguments.
The combination of the current settings of the SQL_CONCURRENCY and SQL_CURSOR_TYPE statement options was not supported by the driver or data source. |
S1T00
|
Timeout expired
|
The timeout period expired before the data source returned the requested result set. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT. |
Comments
SQLTables lists all tables in the requested range. A user may or may not have SELECT privileges to any of these tables. To check accessibility, an application can:
- Call SQLGetInfo and check the SQL_ACCESSIBLE_TABLES info value.
- Call SQLTablePrivileges to check the privileges for each table.
Otherwise, the application must be able to handle a situation where the user selects a table for which SELECT privileges are not granted.
The szTableOwner and szTableName arguments accept search patterns. For more information about valid search patterns, see "Search Pattern Arguments" earlier in this chapter.
To support enumeration of qualifiers, owners, and table types, SQLTables defines the following special semantics for the szTableQualifier, szTableOwner, szTableName, and szTableType arguments:
- If szTableQualifier is a single percent character (%) and szTableOwner and szTableName are empty strings, then the result set contains a list of valid qualifiers for the data source. (All columns except the TABLE_QUALIFIER column contain NULLs.)
- If szTableOwner is a single percent character (%) and szTableQualifier and szTableName are empty strings, then the result set contains a list of valid owners for the data source. (All columns except the TABLE_OWNER column contain NULLs.)
- If szTableType is a single percent character (%) and szTableQualifier, szTableOwner, and szTableName are empty strings, then the result set contains a list of valid table types for the data source. (All columns except the TABLE_TYPE column contain NULLs.)
If szTableType is not an empty string, it must contain a list of comma-separated, values for the types of interest; each value may be enclosed in single quotes (') or unquoted. For example, "'TABLE','VIEW'" or "TABLE, VIEW". If the data source does not support a specified table type, SQLTables does not return any results for that type.
SQLTables returns the results as a standard result set, ordered by TABLE_TYPE, TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME. The following table lists the columns in the result set.
Note SQLTables might not return all qualifiers, owners, or tables. For example, an Xbase driver, for which a qualifier is a directory, might only return the current directory instead of all directories on the system. It might also only return files (tables) in the current directory. Applications can use any valid qualifier, owner, or table, regardless of whether it is returned by SQLTables.
The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual lengths of the TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME columns, an application can call SQLGetInfo with the SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, and SQL_MAX_TABLE_NAME_LEN options.
Column Name
|
Data Type
|
Comments |
TABLE_QUALIFIER
|
Varchar(128)
|
Table qualifier identifier; NULL if not applicable to the data source. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have qualifiers. |
TABLE_OWNER
|
Varchar(128)
|
Table owner identifier; NULL if not applicable to the data source. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have owners. |
TABLE_NAME
|
Varchar(128)
|
Table identifier. |
TABLE_TYPE
|
Varchar(128)
|
Table type identifier; one of the following: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" or a data source – specific type identifier. |
REMARKS
|
Varchar(254)
|
A description of the table. |
Code Example
For a code example of a similar function, see SQLColumns.
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Returning privileges for a column or columns
|
SQLColumnPrivileges (extension) |
Returning the columns in a table or tables
|
SQLColumns (extension) |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Fetching a row of data
|
SQLFetch |
Returning table statistics and indexes
|
SQLStatistics (extension) |
Returning privileges for a table or tables
|
SQLTablePrivileges (extension) |
SQLTransact (ODBC 1.0, Core)
SQLTransact requests a commit or rollback operation for all active operations on all hstmts associated with a connection. SQLTransact can also request that a commit or rollback operation be performed for all connections associated with the henv.
Syntax
RETCODE SQLTransact(henv, hdbc, fType)
The SQLTransact function accepts the following arguments.
Type
|
Argument
|
Use
|
Description |
HENV
|
henv
|
Input
|
Environment handle. |
HDBC
|
hdbc
|
Input
|
Connection handle. |
UWORD
|
fType
|
Input
|
One of the following two values:
SQL_COMMIT
SQL_ROLLBACK |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLTransact returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLTransact 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.
SQLSTATE
|
Error
|
Description |
01000
|
General warning
|
Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
08003
|
Connection not open
|
(DM) The hdbc was not in a connected state. |
08007
|
Connection failure during transaction
|
The connection associated with the hdbc failed during the execution of the function and it cannot be determined whether the requested COMMIT or ROLLBACK occurred before the failure. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hdbc does not support the function. |
S1000
|
General error
|
An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause. |
S1001
|
Memory allocation failure
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing function was called for an hstmt associated with the hdbc and was still executing when SQLTransact was called.
(DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for an hstmt associated with the hdbc and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
S1012
|
Invalid transaction operation code
|
(DM) The value specified for the argument fType was neither SQL_COMMIT nor SQL_ROLLBACK. |
S1C00
|
Driver not capable
|
The driver or data source does not support the ROLLBACK operation. |
Comments
If hdbc is SQL_NULL_HDBC and henv is a valid environment handle, then the Driver Manager will attempt to commit or roll back transactions on all hdbcs that are in a connected state. The Driver Manager calls SQLTransact in the driver associated with each hdbc. The Driver Manager will return SQL_SUCCESS only if it receives SQL_SUCCESS for each hdbc. If the Driver Manager receives SQL_ERROR on one or more hdbcs, it will return SQL_ERROR to the application. To determine which connection(s) failed during the commit or rollback operation, the application can call SQLError for each hdbc.
Note The Driver Manager does not simulate a global transaction across all hdbcs and therefore does not use two-phase commit protocols.
If hdbc is a valid connection handle, henv is ignored and the Driver Manager calls SQLTransact in the driver for the hdbc.
If hdbc is SQL_NULL_HDBC and henv is SQL_NULL_HENV, SQLTransact returns SQL_INVALID_HANDLE.
If fType is SQL_COMMIT, SQLTransact issues a commit request for all active operations on any hstmt associated with an affected hdbc. If fType is SQL_ROLLBACK, SQLTransact issues a rollback request for all active operations on any hstmt associated with an affected hdbc. If no transactions are active, SQLTransact returns SQL_SUCCESS with no effect on any data sources.
If the driver is in manual-commit mode (by calling SQLSetConnectOption with the SQL_AUTOCOMMIT option set to zero), a new transaction is implicitly started when an SQL statement that can be contained within a transaction is executed against the current data source.
To determine how transaction operations affect cursors, an application calls SQLGetInfo with the SQL_CURSOR_ROLLBACK_BEHAVIOR and SQL_CURSOR_COMMIT_BEHAVIOR options.
If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_DELETE, SQLTransact closes and deletes all open cursors on all hstmts associated with the hdbc and discards all pending results. SQLTransact leaves any hstmt present in an allocated (unprepared) state; the application can reuse them for subsequent SQL requests or can call SQLFreeStmt to deallocate them.
If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_CLOSE, SQLTransact closes all open cursors on all hstmts associated with the hdbc. SQLTransact leaves any hstmt present in a prepared state; the application can call SQLExecute for an hstmt associated with the hdbc without first calling SQLPrepare.
If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_PRESERVE, SQLTransact does not affect open cursors associated with the hdbc. Cursors remain at the row they pointed to prior to the call to SQLTransact.
For drivers and data sources that support transactions, calling SQLTransact with either SQL_COMMIT or SQL_ROLLBACK when no transaction is active will return SQL_SUCCESS (indicating that there is no work to be committed or rolled back) and have no effect on the data source.
Drivers or data sources that do not support transactions (SQLGetInfo fOption SQL_TXN_CAPABLE is 0) are effectively always in autocommit mode. Therefore, calling SQLTransact with SQL_COMMIT will return SQL_SUCCESS. However, calling SQLTransact with SQL_ROLLBACK will result in SQLSTATE S1C00 (Driver not capable), indicating that a rollback can never be performed.
Code Example
See SQLParamOptions.
Related Functions
For information about
|
See |
Returning information about a driver or data source
|
SQLGetInfo (extension) |
Freeing a statement handle
|
SQLFreeStmt |
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.