Creating Cursors

For the purposes of ODBC cursors, the cursor definition is essentially the SQL statement passed as an argument to SQLExecuteDirect or SQLPrepare/SQLExecute. The following rules apply when creating cursors through the SQL Server driver:

• Server cursors are created only for statements that begin with a SELECT, EXEC[ute] procedure_name, or {call procedure_name} clause. Otherwise, the statement is treated as a Type A statement. Server cursors are not created for an SQL batch. SQL batches are treated as Type A statements.
• When connected to SQL Server version 6.0 and earlier, if you ask for a Dynamic or Keyset cursor and there is not a unique index for every table referenced in the cursor definition, the server creates a static cursor and the driver returns a SQL_SUCCESS_WITH_INFO status.
• SQL Server allows you to create cursors on base tables as well as on views. In addition, cursors can be opened on a query involving joins between multiple tables and/or aggregate operators. You cannot, however, use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in the cursor definition.
• If the cursor definition involves a UNION, UNION ALL, outer join, GROUP BY with/without HAVING, or DISTINCT, a static cursor is always created irrespective of what was requested and the driver returns a SQL_SUCCESS_WITH_INFO status.
• When connected to SQL Server version 6.0 and earlier, if you ask for a Dynamic cursor and the cursor definition contains an ORDER BY clause that does not match a unique index or a subquery, the server creates a Keyset cursor and the driver returns a SQL_SUCCESS_WITH_INFO status.
• If you ask for a concurrency of row versioning and there is not a timestamp column for every table in the cursor definition, the server uses a concurrency based on values and the driver returns a SQL_SUCCESS_WITH_INFO status.
• You will get a cursor on SQLExecDirect (EXEC[ute] procedure_name or {call procedure_name}) only if the procedure contains one SELECT statement and nothing else. Otherwise, SQL Server generates an error message.
• You will automatically get the cursor behavior associated with Type A statements when executing a batch that contains multiple SELECT statements (except if the first statement is a SELECT, EXECUTE, or ODBC canonical procedure invocation, in which case, SQL Server driver generates an error message).

Keyset cursors could also be populated asynchronously (please refer to the SQL Server documentation about sp_configure). Asynchronous keyset population does not affect the cursor-related ODBC APIs except for SQLRowCount.

The following ODBC functions are relevant for creating cursors: