prguide.htm

SQLRowCount (ODBC 1.0, Core)
SQLSetConnectOption (ODBC 1.0, Level 1)
SQLSetCursorName (ODBC 1.0, Core)
SQLSetParam (ODBC 1.0, Deprecated)
SQLSetPos (ODBC 1.0, Level 2)

SQLRowCount (ODBC 1.0, Core)

SQLRowCount returns the number of rows affected by an UPDATE, INSERT, or DELETE statement or by a SQL_UPDATE, SQL_ADD, or SQL_DELETE operation in SQLSetPos.

Syntax

RETCODE SQLRowCount(hstmt, pcrow)

The SQLRowCount function accepts the following arguments.

Type

Argument

Use

Description

HSTMT

hstmt

Input

Statement handle.

SDWORD FAR *

pcrow

Output

For UPDATE, INSERT, and DELETE statements and for the SQL_UPDATE, SQL_ADD, and SQL_DELETE operations in SQLSetPos, pcrow is the number of rows affected by the request or –1 if the number of affected rows is not available.

For other statements and functions, the driver may define the value of pcrow. For example, some data sources may be able to return the number of rows returned by a SELECT statement or a catalog function before fetching the rows.

Note Many data sources cannot return the number of rows in a result set before fetching them; for maximum interoperability, applications should not rely on this behavior.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLRowCount 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
SQLRowCount 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 function was called prior to calling SQLExecute, SQLExecDirect, SQLSetPos for the hstmt. (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.

Comments

If the last executed statement associated with hstmt was not an UPDATE, INSERT, or DELETE statement, or if the fOption argument in the previous call to SQLSetPos was not SQL_UPDATE, SQL_ADD, or SQL_DELETE, the value of pcrow is driver-defined.

Related Functions

For information about	See
Executing an SQL statement	SQLExecDirect
Executing a prepared SQL statement	SQLExecute

SQLSetConnectOption (ODBC 1.0, Level 1)

SQLSetConnectOption sets options that govern aspects of connections.

Syntax

RETCODE SQLSetConnectOption(hdbc, fOption, vParam)

The SQLSetConnectOption function accepts the following arguments:

Type	Argument	Use	Description
HDBC	hdbc	Input	Connection 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 SQLSetConnectOption 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 SQLSetConnectOption 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.

The driver can return SQL_SUCCESS_WITH_INFO to provide information about the result of setting an option. For example, setting SQL_ACCESS_MODE to read-only during a transaction might cause the transaction to be committed. The driver could use SQL_SUCCESS_WITH_INFO — and information returned with SQLError — to inform the application of the commit action.

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.)
08002	Connection in use	The argument fOption was SQL_ODBC_CURSORS and the driver was already connected to the data source.
08003	Connection not open	An fOption value was specified that required an open connection, but the hdbc was not in a connected state.
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.
IM001	Driver does not support this function	(DM) The driver associated with the hdbc does not support the function.
IM009	Unable to load translation DLL	The driver was unable to load the translation DLL that was specified for the connection. This error can only be returned when fOption is SQL_TRANSLATE_DLL.
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 connection and statement options that accept a discrete set of values, such as SQL_ACCESS_MODE or SQL_ASYNC_ENABLE. For all other connection and statement options, the driver must verify the value of the argument vParam.)
S1010	Function sequence error	(DM) An asynchronously executing function was called for an hstmt associated with the hdbc and was still executing when SQLSetConnectOption 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. (DM) SQLBrowseConnect was called for the hdbc and returned SQL_NEED_DATA. This function was called before SQLBrowseConnect returned SQL_SUCCESS_WITH_INFO or SQL_SUCCESS.
S1011	Operation invalid at this time	The argument fOption was SQL_TXN_ISOLATION and a transaction was open.
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 connection or 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.

When fOption is a statement option, SQLSetConnectOption can return any SQLSTATEs returned by SQLSetStmtOption.

Comments

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.

An application can call SQLSetConnectOption and include a statement option. The driver sets the statement option for any hstmts associated with the specified hdbc and establishes the statement option as a default for any hstmts later allocated for that hdbc. For a list of statement options, see SQLSetStmtOption.

All connection and statement options successfully set by the application for the hdbc persist until SQLFreeConnect is called on the hdbc. For example, if an application calls SQLSetConnectOption before connecting to a data source, the option persists even if SQLSetConnectOption fails in the driver when the application connects to the data source; if an application sets a driver-specific option, the option persists even if the application connects to a different driver on the hdbc.

Some connection and 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_PACKET_SIZE and vParam exceeds the maximum packet size, the driver substitutes the maximum size. To determine the substituted value, an application calls SQLGetConnectOption (for connection options) or SQLGetStmtOption (for statement options).

The format of information set through vParam depends on the specified fOption. SQLSetConnectOption will accept 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. Character strings pointed to by the vParam argument of SQLSetConnectOption have a maximum length of SQL_MAX_OPTION_STRING_LENGTH bytes (excluding the null termination byte).

fOption	vParam Contents
SQL_ACCESS_MODE (ODBC 1.0)	A 32-bit integer value. SQL_MODE_READ_ONLY is used by the driver or data source as an indicator that the connection is not required to support SQL statements that cause updates to occur. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. The driver is not required to prevent such statements from being submitted to the data source. The behavior of the driver and data source when asked to process SQL statements that are not read-only during a read-only connection is implementation defined. SQL_MODE_READ_WRITE is the default.
SQL_AUTOCOMMIT (ODBC 1.0)	A 32-bit integer value that specifies whether to use auto-commit or manual-commit mode: SQL_AUTOCOMMIT_OFF = The driver uses manual-commit mode, and the application must explicitly commit or roll back transactions with SQLTransact. SQL_AUTOCOMMIT_ON = The driver uses auto-commit mode. Each statement is committed immediately after it is executed. This is the default. Note that changing from manual-commit mode to auto-commit mode commits any open transactions on the connection. Important Some data sources delete the access plans and close the cursors for all hstmts on an hdbc each time a statement is committed; autocommit mode can cause this to happen after each statement is executed. For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR information types in SQLGetInfo.
SQL_CURRENT_QUALIFIER (ODBC 2.0)	A null-terminated character string containing the name of the qualifier to be used by the data source. For example, in SQL Server, the qualifier is a database, so the driver sends a USE database statement to the data source, where database is the database specified in vParam. For a single-tier driver, the qualifier might be a directory, so the driver changes its current directory to the directory specified in vParam.
SQL_LOGIN_TIMEOUT (ODBC 1.0)	A 32-bit integer value corresponding to the number of seconds to wait for a login request to complete before returning to the application. The default is driver-dependent and must be nonzero. If vParam is 0, the timeout is disabled and a connection attempt will wait indefinitely. If the specified timeout exceeds the maximum login timeout in the data source, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).
SQL_ODBC_CURSORS (ODBC 2.0)	A 32-bit option specifying how the Driver Manager uses the ODBC cursor library: SQL_CUR_USE_IF_NEEDED = The Driver Manager uses the ODBC cursor library only if it is needed. If the driver supports the SQL_FETCH_PRIOR option in SQLExtendedFetch, the Driver Manager uses the scrolling capabilities of the driver. Otherwise, it uses the ODBC cursor library. SQL_CUR_USE_ODBC = The Driver Manager uses the ODBC cursor library. SQL_CUR_USE_DRIVER = The Driver Manager uses the scrolling capabilities of the driver. This is the default setting.
SQL_OPT_TRACE (ODBC 1.0)	A 32-bit integer value telling the Driver Manager whether to perform tracing: SQL_OPT_TRACE_OFF = Tracing off (the default) SQL_OPT_TRACE_ON = Tracing on When tracing is on, the Driver Manager writes each ODBC function call to the trace file. On Windows and WOW, the Driver Manager writes to the trace file each time any application calls a function. On Windows NT, the Driver Manager writes to the trace file only for the application that turned tracing on. Note When tracing is on, the Driver Manager can return SQLSTATE IM013 (Trace file error) from any function. An application specifies a trace file with the SQL_OPT_TRACEFILE option. If the file already exists, the Driver Manager appends to the file. Otherwise, it creates the file. If tracing is on and no trace file has been specified, the Driver Manager writes to the file \SQL.LOG. On Windows NT, tracing should only be used for a single application or each application should specify a different trace file. Otherwise, two or more applications will attempt to open the same trace file at the same time, causing an error. If the Trace keyword in the [ODBC] section of the ODBC.INI file (or registry) is set to 1 when an application calls SQLAllocEnv, tracing is enabled. On Windows and WOW, it is enabled for all applications; on Windows NT it is enabled only for the application that called SQLAllocEnv.
SQL_OPT_TRACEFILE (ODBC 1.0)	A null-terminated character string containing the name of the trace file. The default value of the SQL_OPT_TRACEFILE option is specified with the TraceFile keyname in the [ODBC] section of the ODBC.INI file (or registry).
SQL_PACKET_SIZE (ODBC 2.0)	A 32-bit integer value specifying the network packet size in bytes. Note Many data sources either do not support this option or can only return the network packet size. If the specified size exceeds the maximum packet size or is smaller than the minimum packet size, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).
SQL_QUIET_MODE (ODBC 2.0)	A 32-bit window handle (hwnd). If the window handle is a null pointer, the driver does not display any dialog boxes. If the window handle is not a null pointer, it should be the parent window handle of the application. The driver uses this handle to display dialog boxes. This is the default. If the application has not specified a parent window handle for this option, the driver uses a null parent window handle to display dialog boxes or return in SQLGetConnectOption. Note The SQL_QUIET_MODE connection option does not apply to dialog boxes displayed by SQLDriverConnect.

SQL_TRANSLATE_OPTION (ODBC 1.0)	A 32-bit flag value that is passed to the translatation DLL. This option may only be specified if the driver has connected to the data source. The valid values are: SQL_SOLID_XLATOPT_DEFAULT = The application uses the default character set conversion for the operating system used. SQL_SOLID_XLATOPT_NOCNV = No conversion is done. The characters are stored as they are given. SQL_SOLID_XLATOPT_ANSI = The charaters are considered to belong to ANSI (ISO Latin 1) character set. This character set is used i.e. in MS Windows. SQL_SOLID_XLATOPT_PCOEM = This character set is used i.e. in MS DOS and OS/2. SQL_SOLID_XLATOPT_7BITSCAND = This character set is used i.e. in VAX/VMS.
SQL_TXN_ISOLATION (ODBC 1.0)	A 32-bit bitmask that sets the transaction isolation level for the current hdbc. An application must call SQLTransact to commit or roll back all open transactions on an hdbc, before calling SQLSetConnectOption with this option. The valid values for vParam can be determined by calling SQLGetInfo with fInfoType equal to SQL_TXN_ISOLATION_OPTIONS. The following terms are used to define transaction isolation levels: Dirty Read Transaction 1 changes a row. Transaction 2 reads the changed row before transaction 1 commits the change. If transaction 1 rolls back the change, transaction 2 will have read a row that is considered to have never existed. Nonrepeatable Read Transaction 1 reads a row. Transaction 2 updates or deletes that row and commits this change. If transaction 1 attempts to reread the row, it will receive different row values or discover that the row has been deleted. Phantom Transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 inserts a row that matches the search criteria. If transaction 1 reexecutes the statement that read the rows, it receives a different set of rows. vParam must be one of the following values: SQL_TXN_READ_UNCOMMITTED = Dirty reads, nonrepeatable reads, and phantoms are possible. SQL_TXN_READ_COMMITTED = Dirty reads are not possible. Nonrepeatable reads and phantoms are possible. SQL_TXN_REPEATABLE_READ = Dirty reads and nonrepeatable reads are not possible. Phantoms are possible. SQL_TXN_SERIALIZABLE = Transactions are serializable. Dirty reads, nonrepeatable reads, and phantoms are not possible. SQL_TXN_VERSIONING = Transactions are serializable, but higher concurrency is possible than with SQL_TXN_SERIALIZABLE. Dirty reads are not possible. Typically, SQL_TXN_SERIALIZABLE is implemented by using locking protocols that reduce concurrency and SQL_TXN_VERSIONING is implemented by using a non-locking protocol such as record versioning. Oracle’s Read Consistency isolation level is an example of SQL_TXN_VERSIONING.

Data Translation

Data translation will be performed for all data flowing between the driver and the data source.

The translation option (set with the SQL_TRANSLATE_OPTION option) can be any 32-bit value. Its meaning depends on the translation DLL being used. A new option can be set at any time. The new option will be applied to the next exchange of data following the call to SQLSetConnectOption. A default translation DLL may be specified for the data source in its data source specification in the ODBC.INI file or registry. The default translation DLL is loaded by the driver at connection time. A translation option (SQL_TRANSLATE_OPTION) may be specified in the data source specification as well.

To change the translation DLL for a connection, an application calls SQLSetConnectOption with the SQL_TRANSLATE_DLL option after it has connected to the data source. The driver will attempt to load the specified DLL and, if the attempt fails, return SQL_ERROR with the SQLSTATE IM009 (Unable to load translation DLL).

If no translation DLL has been specified in the ODBC initialization file or by calling SQLSetConnectOption, the driver will not attempt to translate data. Any value set for the translation option will be ignored.

Code Example

See SQLConnect and SQLParamOptions.

Related Functions

For information about	See
Returning the setting of a connection option	SQLGetConnectOption (extension)
Returning the setting of a statement option	SQLGetStmtOption (extension)
Setting a statement option	SQLSetStmtOption (extension)

SQLSetCursorName (ODBC 1.0, Core)

SQLSetCursorName associates a cursor name with an active hstmt. If an application does not call SQLSetCursorName, the driver generates cursor names as needed for SQL statement processing.

Syntax

RETCODE SQLSetCursorName(hstmt, szCursor, cbCursor)

The SQLSetCursorName function accepts the following arguments.

Type	Argument	Use	Description
HSTMT	hstmt	Input	Statement handle.
UCHAR FAR *	szCursor	Input	Cursor name.
SWORD	cbCursor	Input	Length of szCursor.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLSetCursorName 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 SQLSetCursorName 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.)
24000	Invalid cursor state	The statement corresponding to hstmt was already in an executed or cursor-positioned state.
34000	Invalid cursor name	The cursor name specified by the argument szCursor was invalid. For example, the cursor name exceeded the maximum length as defined by the driver.
3C000	Duplicate cursor name	The cursor name specified by the argument szCursor already exists.
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	(DM) The argument szCursor was a null pointer.
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.
S1090	Invalid string or buffer length	(DM) The argument cbCursor was less than 0, but not equal to SQL_NTS.

Comments

The only ODBC SQL statements that use a cursor name are a positioned update and delete (for example, UPDATE table-name ...WHERE CURRENT OF cursor-name). If the application does not call SQLSetCursorName to define a cursor name, on execution of a SELECT statement the driver generates a name that begins with the letters SQL_CUR and does not exceed 18 characters in length.

All cursor names within the hdbc must be unique. The maximum length of a cursor name is defined by the driver. For maximum interoperability, it is recommended that applications limit cursor names to no more than 18 characters.

A cursor name that is set either explicitly or implicitly remains set until the hstmt with which it is associated is dropped, using SQLFreeStmt with the SQL_DROP option.

Code Example

In the following example, an application uses SQLSetCursorName to set a cursor name for an hstmt. It then uses that hstmt to retrieve results from the EMPLOYEE table. Finally, it performs a positioned update to change the name of 25-year-old John Smith to John D. Smith. Note that the application uses different hstmts for the SELECT and UPDATE statements.

For more code examples, see SQLSetPos.

#define NAME_LEN 30
HSTMT     hstmtSelect,
HSTMT     hstmtUpdate;
UCHAR     szName[NAME_LEN];
SWORD     sAge;
SDWORD    cbName;
SDWORD    cbAge;
/* Allocate the statements and set the cursor name */
SQLAllocStmt(hdbc, &hstmtSelect);
SQLAllocStmt(hdbc, &hstmtUpdate);
SQLSetCursorName(hstmtSelect, "C1", SQL_NTS);
/* SELECT the result set and bind its columns to local storage */
SQLExecDirect(hstmtSelect,

              "SELECT NAME, AGE FROM EMPLOYEE FOR UPDATE",

              SQL_NTS);
SQLBindCol(hstmtSelect, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName);
SQLBindCol(hstmtSelect, 2, SQL_C_SSHORT, &sAge, 0, &cbAge);
/* Read through the result set until the cursor is      */
/* positioned on the row for the 25-year-old John Smith */
do
   retcode = SQLFetch(hstmtSelect);
while ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) &&

       (strcmp(szName, "Smith, John") != 0 || sAge != 25));
/* Perform a positioned update of John Smith's name */
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
   SQLExecDirect(hstmtUpdate,

    "UPDATE EMPLOYEE SET NAME=\"Smith, John D.\" WHERE CURRENT OF C1",

      SQL_NTS);
}

Related Functions

For information about	See
Executing an SQL statement	SQLExecDirect
Executing a prepared SQL statement	SQLExecute
Returning a cursor name	SQLGetCursorName
Setting cursor scrolling options	SQLSetScrollOptions (extension)

SQLSetParam (ODBC 1.0, Deprecated)

In ODBC 2.0, the ODBC 1.0 function SQLSetParam has been replaced by SQLBindParameter. For more information, see SQLBindParameter.

SQLSetPos (ODBC 1.0, Level 2)

SQLSetPos sets the cursor position in a rowset and allows an application to refresh, update, delete, or add data to the rowset.

Syntax

RETCODE SQLSetPos(hstmt, irow, fOption, fLock)

The SQLSetPos function accepts the following arguments:

Type	Argument	Use	Description
HSTMT	hstmt	Input	Statement handle.
UWORD	irow	Input	Position of the row in the rowset on which to perform the operation specified with the fOption argument. If irow is 0, the operation applies to every row in the rowset. For additional information, see "Comments."
UWORD	fOption	Input	Operation to perform: SQL_POSITION SQL_REFRESH SQL_UPDATE SQL_DELETE SQL_ADD For more information, see "Comments."
UWORD	fLock	Input	Specifies how to lock the row after performing the operation specified in the fOption argument. SQL_LOCK_NO_CHANGE SQL_LOCK_EXCLUSIVE SQL_LOCK_UNLOCK For more information, see "Comments."

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLSetPos 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 SQLSetPos 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.)
01004	Data truncated	The argument fOption was SQL_ADD or SQL_UPDATE and the value specified for a character or binary column exceeded the maximum length of the associated table column. (Function returns SQL_SUCCESS_WITH_INFO.) The argument fOption was SQL_ADD or SQL_UPDATE and the fractional part of the value specified for a numeric column was truncated. (Function returns SQL_SUCCESS_WITH_INFO.) The argument fOption was SQL_ADD or SQL_UPDATE and a timestamp value specified for a date or time column was truncated. (Function returns SQL_SUCCESS_WITH_INFO.)
01S01	Error in row	The irow argument was 0 and an error occurred in one or more rows while performing the operation specified with the fOption argument. (Function returns SQL_SUCCESS_WITH_INFO.)
01S03	No rows updated or deleted	The argument fOption was SQL_UPDATE or SQL_DELETE and no rows were updated or deleted. (Function returns SQL_SUCCESS_WITH_INFO.)
01S04	More than one row updated or deleted	The argument fOption was SQL_UPDATE or SQL_DELETE and more than one row was updated or deleted. (Function returns SQL_SUCCESS_WITH_INFO.)
21S02	Degree of derived table does not match column list	The argument fOption was SQL_ADD or SQL_UPDATE and no columns were bound with SQLBindCol.
22003	Numeric value out of range	The argument fOption was SQL_ADD or SQL_UPDATE and the whole part of a numeric value was truncated.
22005	Error in assignment	The argument fOption was SQL_ADD or SQL_UPDATE and a value was incompatible with the data type of the associated column.
22008	Datetime field overflow	The argument fOption was SQL_ADD or SQL_UPDATE and a date, time, or timestamp value was, respectively, an invalid date, time, or timestamp.
23000	Integrity constraint violation	The argument fOption was SQL_ADD or SQL_UPDATE and a value was NULL for a column defined as NOT NULL in the associated column or some other integrity constraint was violated. The argument fOption was SQL_ADD and a column that was not bound with SQLBindCol is defined as NOT NULL or has no default.
24000	Invalid cursor state	(DM) The hstmt was in an executed state but no result set was associated with the hstmt. (DM) A cursor was open on the hstmt but SQLFetch or SQLExtendedFetch had not been called. A cursor was open on the hstmt and SQLExtendedFetch had been called, but the cursor was positioned before the start of the result set or after the end of the result set. The argument fOption was SQL_DELETE, SQL_REFRESH, or SQL_UPDATE and the cursor was positioned before the start of the result set or after the end of the result set.
42000	Syntax error or access violation	The driver was unable to lock the row as needed to perform the operation requested in the argument fOption. The driver was unable to lock the row as requested in the argument fLock.
IM001	Driver does not support this function	(DM) The driver associated with the hstmt does not support the function.
S0023	No default for column	The fOption argument was SQL_ADD and a column that was not bound did not have a default value and could not be set to NULL. The fOption argument was SQL_ADD, the length specified in the pcbValue buffer bound by SQLBindCol was SQL_IGNORE, and the column did not have a default value.
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.
S1009	Invalid argument value	(DM) The value specified for the argument fOption was invalid. (DM) The value specified for the argument fLock was invalid. The argument irow was greater than the number of rows in the rowset and the fOption argument was not SQL_ADD. The value specified for the argument fOption was SQL_ADD, SQL_UPDATE, or SQL_DELETE, the value specified for the argument fLock was SQL_LOCK_NO_CHANGE, and the SQL_CONCURRENCY statement option was SQL_CONCUR_READ_ONLY.
S1010	Function sequence error	(DM) The specified hstmt was not in an executed state. The function was called without first calling SQLExecDirect, SQLExecute, or a catalog function. (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	The fOption argument was SQL_ADD or SQL_UPDATE, a data value was a null pointer, and the column length value was not 0, SQL_DATA_AT_EXEC, SQL_IGNORE, SQL_NULL_DATA, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET. The fOption argument was SQL_ADD or SQL_UPDATE, a data value was not a null pointer, and the column length value was less than 0, but not equal to SQL_DATA_AT_EXEC, SQL_IGNORE, SQL_NTS, or SQL_NULL_DATA, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET.
S1107	Row value out of range	The value specified for the argument irow was greater than the number of rows in the rowset and the fOption argument was not SQL_ADD.
S1109	Invalid cursor position	The cursor associated with the hstmt was defined as forward only, so the cursor could not be positioned within the rowset. See the description for the SQL_CURSOR_TYPE option in SQLSetStmtOption. The fOption argument was SQL_REFRESH, SQL_UPDATE, or SQL_DELETE and the value in the rgfRowStatus array for the row specified by the irow argument was SQL_ROW_DELETED or SQL_ROW_ERROR.
S1C00	Driver not capable	The driver or data source does not support the operation requested in the fOption argument or the fLock argument.
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

irow Argument

The irow argument specifies the number of the row in the rowset on which to perform the operation specified by the fOption argument. If irow is 0, the operation applies to every row in the rowset. Except for the SQL_ADD operation, irow must be a value from 0 to the number of rows in the rowset. For the SQL_ADD operation, irow can be any value; generally it is either 0 (to add as many rows as there are in the rowset) or the number of rows in the rowset plus 1 (to add the data from an extra row of buffers allocated for this purpose).

Note In the C language, arrays are 0-based, while the irow argument is 1-based. For example, to update the fifth row of the rowset, an application modifies the rowset buffers at array index 4, but specifies an irow of 5.

All operations except for SQL_ADD position the cursor on the row specified by irow; the SQL_ADD operation does not change the cursor position. The following operations require a cursor position:

Positioned update and delete statements.

Calls to SQLGetData.

Calls to SQLSetPos with the SQL_DELETE, SQL_REFRESH, and SQL_UPDATE options.

For example, if the cursor is positioned on the second row of the rowset, a positioned delete statement deletes that row; if it is positioned on the entire rowset (irow is 0), a positioned delete statement deletes every row in the rowset.

An application can specify a cursor position when it calls SQLSetPos. Generally, it calls SQLSetPos with the SQL_POSITION or SQL_REFRESH operation to position the cursor before executing a positioned update or delete statement or calling SQLGetData.

fOption Argument

The fOption argument supports the following operations. To determine which options are supported by a data source, an application calls SQLGetInfo with the SQL_POS_OPERATIONS information type.

*fOption* Argument	Operation
SQL_POSITION	The driver positions the cursor on the row specified by irow. This is the same as the FALSE value of this argument in ODBC 1.0.
SQL_REFRESH	The driver positions the cursor on the row specified by irow and refreshes data in the rowset buffers for that row. For more information about how the driver returns data in the rowset buffers, see the descriptions of row-wise and column-wise binding in SQLExtendedFetch. This is the same as the TRUE value of this argument in ODBC 1.0.
SQL_UPDATE	The driver positions the cursor on the row specified by irow and updates the underlying row of data with the values in the rowset buffers (the rgbValue argument in SQLBindCol). It retrieves the lengths of the data from the number-of-bytes buffers (the pcbValue argument in SQLBindCol). If the length of any column is SQL_IGNORE, the column is not updated. After updating the row, the driver changes the rgfRowStatus array specified in SQLExtendedFetch to SQL_ROW_UPDATED.
SQL_DELETE	The driver positions the cursor on the row specified by irow and deletes the underlying row of data. It changes the rgfRowStatus array specified in SQLExtendedFetch to SQL_ROW_DELETED. After the row has been deleted, positioned update and delete statements, calls to SQLGetData and calls to SQLSetPos with fOption set to anything except SQL_POSITION are not valid for the row. Whether the row remains visible depends on the cursor type. For example, deleted rows are visible to static and keyset-driven cursors but invisible to dynamic cursors.
SQL_ADD	The driver adds a new row of data to the data source. Where the row is added to the data source and whether it is visible in the result set is driver-defined. The driver retrieves the data from the rowset buffers (the rgbValue argument in SQLBindCol) according to the value of the irow argument. It retrieves the lengths of the data from the number-of-bytes buffers (the pcbValue argument in SQLBindCol). Generally, the application allocates an extra row of buffers for this purpose. For columns not bound to the rowset buffers, the driver uses default values (if they are available) or NULL values (if default values are not available). For columns with a length of SQL_IGNORE, the driver uses default values. If irow is less than or equal to the rowset size, the driver changes the rgfRowStatus array specified in SQLExtendedFetch to SQL_ROW_ADDED after adding the row. At this point, the rowset buffers do not match the cursors for the row. To restore the rowset buffers to match the data in the cursor, an application calls SQLSetPos with the SQL_REFRESH option. This operation does not affect the cursor position.

fLock Argument

The fLock argument provides a way for applications to control concurrency and simulate transactions on data sources that do not support them. Generally, data sources that support concurrency levels and transactions will only support the SQL_LOCK_NO_CHANGE value of the fLock argument.

The fLock argument specifies the lock state of the row after SQLSetPos has been executed. To simulate a transaction, an application uses the SQL_LOCK_RECORD macro to lock each of the rows in the transaction. It then uses the SQL_UPDATE_RECORD or SQL_DELETE_RECORD macro to update or delete each row; the driver may temporarily change the lock state of the row while performing the operation specified by the fOption argument. Finally, it uses the SQL_LOCK_RECORD macro to unlock each row. For an example of how an application might do this, see the second code example. Note that if the driver is unable to lock the row either to perform the requested operation or to satisfy the fLock argument, it returns SQL_ERROR and SQLSTATE 42000 (Syntax error or access violation).

Although the fLock argument is specified for an hstmt, the lock accords the same privileges to all hstmts on the connection. In particular, a lock that is acquired by one hstmt on a connection can be unlocked by a different hstmt on the same connection.

A row locked through SQLSetPos remains locked until the application calls SQLSetPos for the row with fLock set to SQL_LOCK_UNLOCK or the application calls SQLFreeStmt with the SQL_CLOSE or SQL_DROP option.

The fLock argument supports the following types of locks. To determine which locks are supported by a data source, an application calls SQLGetInfo with the SQL_LOCK_TYPES information type.

*fLock* Argument	Lock Type
SQL_LOCK_NO_CHANGE	The driver or data source ensures that the row is in the same locked or unlocked state as it was before SQLSetPos was called. This value of fLock allows data sources that do not support explicit row-level locking to use whatever locking is required by the current concurrency and transaction isolation levels. This is the same as the FALSE value of the fLock argument in ODBC 1.0.
SQL_LOCK_EXCLUSIVE	The driver or data source locks the row exclusively. An hstmt on a different hdbc or in a different application cannot be used to acquire any locks on the row. This is the same as the TRUE value of the fLock argument in ODBC 1.0.
SQL_LOCK_UNLOCK	The driver or data source unlocks the row.

For the add, update, and delete operations in SQLSetPos, the application uses the fLock argument as follows:

To guarantee that a row does not change after it is retrieved, an application calls SQLSetPos with fOption set to SQL_REFRESH and fLock set to SQL_LOCK_EXCLUSIVE.

If the application sets fLock to SQL_LOCK_NO_CHANGE, the driver guarantees an update, or delete operation will succeed only if the application specified SQL_CONCUR_LOCK for the SQL_CONCURRENCY statement option.

If the application specifies SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES for the SQL_CONCURRENCY statement option, the driver compares row versions or values and rejects the operation if the row has changed since the application fetched the row.

If the application specifies SQL_CONCUR_READ_ONLY for the SQL_CONCURRENCY statement option, the driver rejects any update or delete operation.

For more information about the SQL_CONCURRENCY statement option, see SQLSetStmtOption.

Using SQLSetPos

Before an application calls SQLSetPos, it must:

1. If the application will call SQLSetPos with fOption set to SQL_ADD or SQL_UPDATE, call SQLBindCol for each column to specify its data type and associate storage for the column’s data and length.

2. Call SQLExecDirect, SQLExecute, or a catalog function to create a result set.

3. Call SQLExtendedFetch to retrieve the data.

To delete data with SQLSetPos, an application:

Calls SQLSetPos with irow set to the number of the row to delete.

An application can pass the value for a column either in the rgbValue buffer or with one or more calls to SQLPutData. Columns whose data is passed with SQLPutData are known as data-at-execution columns. These are commonly used to send data for SQL_LONGVARBINARY and SQL_LONGVARCHAR columns and can be mixed with other columns.

To update or add data with SQLSetPos, an application:

1. Places values in the rgbValue and pcbValue buffers bound with SQLBindCol:

For normal columns, the application places the new column value in the rgbValue buffer and the length of that value in the pcbValue buffer. If the row is being updated and the column is not to be changed, the application places SQL_IGNORE in the pcbValue buffer.

For data-at-execution columns, the application places an application-defined value, such as the column number, in the rgbValue buffer. The value can be used later to identify the column. It places the result of the SQL_LEN_DATA_AT_EXEC(length) macro in the pcbValue buffer. If the SQL data type of the column 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 SQLGetInfo, length is the number of bytes of data to be sent for the parameter; otherwise, it must be a nonnegative value and is ignored.

2. Calls SQLSetPos or uses an SQLSetPos macro to update or add the row of data.

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

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

3. Calls SQLParamData to retrieve the address of the rgbValue buffer for the first data-at-execution column to be processed. The application retrieves the application-defined value from the rgbValue buffer.

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

Data-at-execution parameters are parameters in an SQL statement for which data will be sent with SQLPutData when the statement is executed with SQLExecDirect or SQLExecute. They are bound with SQLBindParameter. The value returned by SQLParamData is a 32-bit value passed to SQLBindParameter in the rgbValue argument.

Data-at-execution columns are columns in a rowset for which data will be sent with SQLPutData when a row is updated or added with SQLSetPos. They are bound with SQLBindCol. The value returned by SQLParamData is the address of the row in the rgbValue buffer that is being processed.

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

5. Calls SQLParamData again to signal that all data has been sent for the column.

If there are more data-at-execution columns, SQLParamData returns SQL_NEED_DATA and the address of the rgbValue buffer for the next data-at-execution column to be processed. The application repeats steps 4 and 5.

If there are no more data-at-execution columns, the process is complete. If the statement was executed successfully, SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO; if the execution failed, it returns SQL_ERROR. At this point, SQLParamData can return any SQLSTATE that can be returned by SQLSetPos.

After SQLSetPos returns SQL_NEED_DATA, and before data is sent for all data-at-execution columns, the operation is canceled, or an error occurs in SQLParamData or SQLPutData, the application can only call SQLCancel, SQLGetFunctions, SQLParamData, or SQLPutData with the hstmt or the hdbc associated with the hstmt. If it calls any other function with the hstmt or the hdbc associated with the hstmt, the function returns SQL_ERROR and SQLSTATE S1010 (Function sequence error).

If the application calls SQLCancel while the driver still needs data for data-at-execution columns, the driver cancels the operation; the application can then call SQLSetPos again; canceling does not affect the cursor state or the current cursor position. If the application calls SQLParamData or SQLPutData after canceling the operation, the function returns SQL_ERROR and SQLSTATE S1008 (Operation canceled).

Performing Bulk Operations

If the irow argument is 0, the driver performs the operation specified in the fOption argument for every row in the rowset. If an error occurs that pertains to the entire rowset, such as SQLSTATE S1T00 (Timeout expired), the driver returns SQL_ERROR and the appropriate SQLSTATE. The contents of the rowset buffers are undefined and the cursor position is unchanged.

If an error occurs that pertains to a single row, the driver:

Sets the element in the rgfRowStatus array for the row to SQL_ROW_ERROR.

Posts SQLSTATE 01S01 (Error in row) in the error queue.

Posts one or more additional SQLSTATEs for the error after SQLSTATE 01S01 (Error in row) in the error queue.

After it has processed the error or warning, the driver continues the operation for the remaining rows in the rowset and returns SQL_SUCCESS_WITH_INFO. Thus, for each row that returned an error, the error queue contains SQLSTATE 01S01 (Error in row) followed by zero or more additional SQLSTATEs.

If the driver returns any warnings, such as SQLSTATE 01004 (Data truncated), it returns warnings that apply to the entire rowset or to unknown rows in the rowset before it returns the error information that applies to specific rows. It returns warnings for specific rows along with any other error information about those rows.

SQLSetPos Macros

As an aid to programming, the following macros for calling SQLSetPos are defined in the SQLEXT.H file.

Macro name	Function call
SQL_POSITION_TO(hstmt, irow)	SQLSetPos(hstmt, irow, SQL_POSITION, SQL_LOCK_NO_CHANGE)
SQL_LOCK_RECORD(hstmt, irow, fLock)	SQLSetPos(hstmt, irow, SQL_POSITION, fLock)
SQL_REFRESH_RECORD(hstmt, irow, fLock)	SQLSetPos(hstmt, irow, SQL_REFRESH, fLock)
SQL_UPDATE_RECORD(hstmt, irow)	SQLSetPos(hstmt, irow, SQL_UPDATE, SQL_LOCK_NO_CHANGE)
SQL_DELETE_RECORD(hstmt, irow)	SQLSetPos(hstmt, irow, SQL_DELETE, SQL_LOCK_NO_CHANGE)
SQL_ADD_RECORD(hstmt, irow)	SQLSetPos(hstmt, irow, SQL_ADD, SQL_LOCK_NO_CHANGE)

Code Example

In the following example, an application allows a user to browse the EMPLOYEE table and update employee birthdays. The cursor is keyset-driven with a rowset size of 20 and uses optimistic concurrency control comparing row versions. After each rowset is fetched, the application prints them and allows the user to select and update an employee’s birthday. The application uses SQLSetPos to position the cursor on the selected row and performs a positioned update of the row. (Error handling is omitted for clarity.)

#define ROWS 20
#define NAME_LEN 30
#define BDAY_LEN 11
UCHAR  szName[ROWS][NAME_LEN], szBirthday[ROWS][BDAY_LEN], szReply[3];
SDWORD cbName[ROWS], cbBirthday[ROWS];
UWORD  rgfRowStatus[ROWS];
UDWORD crow, irow;
HSTMT  hstmtS, hstmtU;
SQLSetStmtOption(hstmtS, SQL_CONCURRENCY, SQL_CONCUR_ROWVER);

SQLSetStmtOption(hstmtS, SQL_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN);
SQLSetStmtOption(hstmtS, SQL_ROWSET_SIZE, ROWS);
SQLSetCursorName(hstmtS, "C1", SQL_NTS);
SQLExecDirect(hstmtS,

           "SELECT NAME, BIRTHDAY FROM EMPLOYEE FOR UPDATE OF BIRTHDAY",

           SQL_NTS);
SQLBindCol(hstmtS, 1, SQL_C_CHAR, szName, NAME_LEN, cbName);
SQLBindCol(hstmtS, 1, SQL_C_CHAR, szBirthday, BDAY_LEN,

           cbBirthday);
while (SQLExtendedFetch(hstmtS, FETCH_NEXT, 0, &crow, rgfRowStatus) !=

       SQL_ERROR) {
   for (irow = 0; irow < crow; irow++) {
      if (rgfRowStatus[irow] != SQL_ROW_DELETED)
         printf("%d %-*s %*s\n", irow, NAME_LEN-1, szName[irow],

                BDAY_LEN-1, szBirthday[irow]);
   }
   while (TRUE) {
      printf("\nRow number to update?");
      gets(szReply);
      irow = atoi(szReply);
      if (irow > 0 && irow <= crow) {
         printf("\nNew birthday?");
         gets(szBirthday[irow-1]);
         SQLSetPos(hstmtS, irow, SQL_POSITION, SQL_LOCK_NO_CHANGE);
         SQLPrepare(hstmtU,

            "UPDATE EMPLOYEE SET BIRTHDAY=? WHERE CURRENT OF C1",

            SQL_NTS);
         SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT,

                          SQL_C_CHAR, SQL_DATE,

                          BDAY_LEN, 0, szBirthday, 0, NULL);
         SQLExecute(hstmtU);
      } else if (irow == 0) {
         break;
      }
   }
}

In the following code fragment, an application simulates a transaction for rows 1 and 2. It locks the rows, updates them, then unlocks them. The code uses the SQLSetPos macros.

/* Lock rows 1 and 2                                      */
SQL_LOCK_RECORD(hstmt, 1, SQL_LOCK_EXCLUSIVE);
SQL_LOCK_RECORD(hstmt, 2, SQL_LOCK_EXCLUSIVE);
/* Modify the rowset buffers for rows 1 and 2 (not shown).*/
/* Update rows 1 and 2.                                   */
SQL_UPDATE_RECORD(hstmt, 1);
SQL_UPDATE_RECORD(hstmt, 2);
/* Unlock rows 1 and 2                                    */
SQL_LOCK_RECORD(hstmt, 1, SQL_LOCK_UNLOCK);
SQL_LOCK_RECORD(hstmt, 2, SQL_LOCK_UNLOCK);

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)
Setting a statement option	SQLSetStmtOption (extension)

SQLRowCount (ODBC 1.0, Core)

Syntax

Returns

Diagnostics

Comments

Related Functions

SQLSetConnectOption (ODBC 1.0, Level 1)

Syntax

Returns

Diagnostics

Comments

Data Translation

Code Example

Related Functions

SQLSetCursorName (ODBC 1.0, Core)

Syntax

Returns

Diagnostics

Comments

Code Example

Related Functions

SQLSetParam (ODBC 1.0, Deprecated)

SQLSetPos (ODBC 1.0, Level 2)

Syntax

Returns

Diagnostics

Comments

irow Argument

fOption Argument

fLock Argument

Using SQLSetPos

Performing Bulk Operations

SQLSetPos Macros

Code Example

Related Functions