Previous Page | Next Page

Data Set Options for Relational Databases

CURSOR_TYPE= Data Set Option



Specifies the cursor type for read only and updatable cursors.
Default value: LIBNAME setting
Valid in: DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support: DB2 under UNIX and PC Hosts, Microsoft SQL Server, ODBC, OLE DB

Syntax
Syntax Description
Details
See Also

Syntax

CURSOR_TYPE=DYNAMIC | FORWARD_ONLY | KEYSET_DRIVEN | STATIC

Syntax Description

DYNAMIC

specifies that the cursor reflects all changes that are made to the rows in a result set as you move the cursor. The data values and the membership of rows in the cursor can change dynamically on each fetch. This is the default for the DB2 under UNIX and PC Hosts, Microsoft SQL Server, and ODBC interfaces. For OLE DB details, see "Details."

FORWARD_ONLY [not valid for OLE DB]

specifies that the cursor functions like a DYNAMIC cursor except that it supports only sequential fetching of rows.

KEYSET_DRIVEN

specifies that the cursor determines which rows belong to the result set when the cursor is opened. However, changes that are made to these rows are reflected as you move the cursor.

STATIC

specifies that the cursor builds the complete result set when the cursor is opened. No changes made to the rows in the result set after the cursor is opened are reflected in the cursor. Static cursors are read-only.


Details

Not all drivers support all cursor types. An error is returned if the specified cursor type is not supported. The driver is allowed to modify the default without an error. See your database documentation for more information.

When no options have been set yet, here are the initial DBMS-specific defaults.

DB2 for UNIX and PC Microsoft SQL Server ODBC OLE DB
KEYSET_DRIVEN DYNAMIC FORWARD_ONLY FORWARD_ONLY

Here are the operation-specific defaults.

Operation DB2 for UNIX and PC Microsoft SQL Server ODBC OLE DB
insert

(UPDATE_SQL=NO)

KEYSET_DRIVEN DYNAMIC KEYSET_DRIVEN FORWARD_ONLY
read

(such as PROC PRINT)

driver default driver default

(FORWARD_ONLY)

update

(UPDATE_SQL=NO)

KEYSET_DRIVEN DYNAMIC KEYSET_DRIVEN FORWARD_ONLY
CONNECTION=GLOBAL

CONNECTION=SHARED


DYNAMIC
DYNAMIC

OLE DB: Here are the OLE DB properties that are applied to an open row set. For details, see your OLE DB programmer reference documentation.

CURSOR_TYPE= OLE DB Properties Applied
FORWARD_ONLY/DYNAMIC (see "Details") DBPROP_OTHERINSERT=TRUE, DBPROP_OTHERUPDATEDELETE=TRUE
KEYSET_DRIVEN DBPROP_OTHERINSERT=FALSE, DBPROP_OTHERUPDATEDELETE=TRUE
STATIC DBPROP_OTHERINSERT=FALSE, DBPROP_OTHERUPDATEDELETE=FALSE


See Also

To assign this option to a group of relational DBMS tables or views, see the CURSOR_TYPE= LIBNAME Option.

KEYSET_SIZE= Data Set Option [only Microsoft SQL Server and ODBC]

Previous Page | Next Page | Top of Page