%SSC_OPEN

Open a cursor

value = %SSC_OPEN(dbchannel, cursor, statement, SSQL_SELECT|SSQL_NONSEL,
        [options], [numvars][, var, ...])

Return value

value

This function returns SSQL_NORMAL (success) or SSQL_FAILURE (failure). (i)

Arguments

dbchannel

An internal database channel previously initialized using %SSC_INIT and connected by %SSC_CONNECT. (n)

cursor

Determines whether a cursor will be reused and returns the ID number for the resulting cursor. (n)

If cursor is passed as a value of zero, SQL Connection automatically searches to see if there is already a cached cursor that can be reused for the statement (statement). If there is one, SQL Connection uses this cursor. If not, %SSC_OPEN opens a new cursor and returns the number for the new cursor.
If cursor is passed as a non-zero value, %SSC_OPEN reuses the cursor if it can and returns the same cursor number. If %SSC_OPEN is unable to reuse the cursor, it hard closes the cursor, opens a new cursor, and returns the ID number for the new cursor.

statement

An SQL statement. (a)

SSQL_SELECT

Indicates that statement is a SELECT statement or the name of a stored procedure that returns a result set. SSQL_SELECT causes %SSC_OPEN to open or reopen a database cursor. (n)

SSQL_NONSEL

Indicates that statement is a non-SELECT statement (such as INSERT, UPDATE, or DELETE). SSQL_NONSEL causes %SSC_OPEN to open or reopen a statement cursor. (n)

options

(optional) Sets one or more options used to configure the cursor that is being opened or reopened. See The options argument and Passing multiple options below. (n)

numvars

(optional) The number of variables (var, …) bound to statement. This must be set to the number of passed var arguments. (n)

var

(optional) Host variable to be bound to statement parameters. You can pass more than one var argument (by separating them with commas). The number of var arguments you pass must equal the number passed as numvars. For information on binding host variables, see Using variables to map data. (a, n, or String)

Discussion

This discussion focuses on %SSC_OPEN options and issues. For more information on cursors, including information on cursor types and cursor behavior, see Using cursors with SQL Connection.

%SSC_OPEN opens or reopens a cursor and associates it with an SQL statement, which is specified by statement. The cursor is opened on the database channel specified by dbchannel. Note the following:

The String data type (System.String) is not supported for var for array-based operations.
Do not use SSQL_SELECT and a server-side cursor for non-cursor-related stored procedures, or if the SQL statement includes the EXEC SQL command for a stored procedure that returns no rows (this may cause “No cursor” warnings). Instead, use %SSC_OPEN with SSQL_NONSEL (and %SSC_EXECUTE).
If you use ^VARARGARRAY, note that numvars is the last declared argument for this routine.

Multiple cursors, cursor reuse, and linking statements

You can open multiple cursors concurrently (see Examples below). The maximum number of cursors you can open is set by the maxcur argument for %SSC_INIT, though the number of actual database cursors and statement cursors that can be open concurrently is set by the cursor argument for %SSC_INIT and is limited by your database’s capacity.

You may not need to open a new cursor for each SQL statement. If you reuse the same operation, it is best to reuse a cursor (by passing the cursor ID number as cursor). When a cursor is reused, the application skips the initial step of processing the SQL statement, which is typically a very resource-intensive process. See Reusing cursors for more information.

If you are fetching a row and you plan to perform a positioned update, you can use %SSC_SQLLINK to link the update statement to the open SELECT cursor rather than opening another cursor for the update statement. See %SSC_SQLLINK for more information.

The options argument

The following are the valid values for the options argument. SSQL_LARGECOL applies to both database cursors and statement cursors. Other options apply only to database cursors. The SSQL_SCROLL options instruct %SSC_OPEN create a scrolling cursor. (For SQL Server, these are ODBC API cursor types; see SQL Server database cursor types.) With a scrolling cursor, you can determine which row will be retrieved with the next fetch by setting an SSQL_CMD_SCROLL option (with %SSC_CMD).

Option	What it does
SSQL_SCROLL	Creates a scrolling cursor of the type specified with an SSQL_CURSOR option in a previous call to %SSC_CMD. If no SSQL_CURSOR option has been set, this creates a cursor of the default type for the database. This option can be used only with VTX0, VTX11, or VTX12_SQLNATIVE.
SSQL_SCROLL_ASENSITIVE	Creates a keyset cursor when using SSQL_FORUPDATE (discussed below). Otherwise, it creates a static cursor. This option can be used only with VTX7, VTX11, or VTX12_SQLNATIVE.
SSQL_SCROLL_DYNAMIC	Creates a dynamic scrolling cursor. This option can be used only with VTX11 or VTX12_SQLNATIVE.
SSQL_SCROLL_READONLY	Creates a read-only scrolling keyset cursor. This option can be used only with VTX11 or VTX12_SQLNATIVE.
SSQL_SCROLL_STATIC	Creates an insensitive scrollable static cursor that uses tempdb tables. This option can be used only with VTX7, VTX11, or VTX12_SQLNATIVE.
SSQL_LARGECOL	Enables SQL Connection to use %SSC_LARGECOL to get or put large binary columns or large character columns. This option can be used for database and statement cursors.
SSQL_FORUPDATE	Informs SQL Connection that the SQL statement (statement) passed to %SSC_OPEN contains a FOR UPDATE OF clause. This is required if statement contains a FOR UPDATE OF clause.
SSQL_ONECOL	Explicitly disables prefetch caching.
SSQL_POSITION	Creates a positioned cursor that is positioned at the first record that meets the criteria for the query.
SSQL_STANDARD	Creates a standard (non-positioned) cursor.

Note the following:

You can pass more than one option by joining the options with a plus sign (+), but you can specify only one SSQL_SCROLL option in a call to %SSC_OPEN. See Passing multiple options below.
The SSQL_SCROLL options for %SSC_OPEN override %SSC_CMD cursor type options if they conflict.
When used with SSQL_LARGECOL or SSQL_ONECOL, the SSQL_SCROLL options for %SSC_OPEN disable prefetch caching. If you don’t pass any of these, but do pass SSQL_SELECT and SSQL_STANDARD, SQL Connection uses prefetch caching which increases fetch (%SSC_MOVE) performance. (See Improving network performance with prefetch caching for more information.)
If you use SELECT to select a row and you need to lock the row for update, use positioned mode by setting SSQL_POSITION+SSQL_FORUPDATE (unless you’re accessing a SQL Server database—see the note in Row locking). Positioned cursors allow other positioned operations (such as statements with UPDATE WHERE CURRENT OF on some databases), but note that some databases do not allow positioned mode (e.g., Oracle Rdb).
If you use SSQL_FORUPDATE, your SQL statement must include a FOR UPDATE OF clause. If your database does not use FOR UPDATE OF to invoke row locking, SQL Connection converts the SQL statement to a statement that does invoke row locking. For more information, see Understanding updates and locking.

Passing multiple options

You can pass more than one option in the options argument by joining the options with a plus sign (+). For example, SSQL_STANDARD and SSQL_LARGECOL can be used together:

sts=%ssc_open(dbchn, cur1, sqlp, SSQL_SELECT, SSQL_STANDARD+SSQL_LARGECOL)

The following table shows which options can be combined in a single %SSC_OPEN call.

	SSQL_ STANDARD	SSQL_ POSITION	SSQL_ FORUPDATE	SSQL_ LARGECOL	SSQL_ ONECOL	SSQL_ SCROLL	SSQL_ SCROLL_ READONLY	SSQL_ SCROLL_ DYNAMIC	SSQL_ SCROLL_ ASENSITIVE	SSQL_ SCROLL_ STATIC
SSQL_STANDARD
SSQL_POSITION
SSQL_FORUPDATE
SSQL_LARGECOL
SSQL_ONECOL
SSQL_SCROLL
SSQL_SCROLL_READONLY
SSQL_SCROLL_DYNAMIC
SSQL_SCROLL_ASENSITIVE
SSQL_SCROLL_STATIC

Examples

The following example opens three SQL statement cursors simultaneously.

if (%ssc_connect(dbchn, user))    ;Connects to database
  goto err_exit
        ;Open cursor #1
sqlp = "SELECT deptnum, deptname FROM org WHERE deptnum"
  &      " = 10"
if (%ssc_open(dbchn, cur1, sqlp, SSQL_SELECT, SSQL_STANDARD))
  goto err_exit
        ;Open cursor #2 where bind1 matches with :1
sqlstm = "SELECT deptnum, deptname, manager, division"
  &      " FROM org WHERE deptnum = :1"
if (%ssc_open(dbchn, cur2, sqlstm, SSQL_SELECT,
  &   SSQL_STANDARD, 1, bind1))
  goto err_exit
        ;Open cursor #3
sqlp = "INSERT INTO org (deptnum, deptname, manager,"
  &    " division, stdate, budget) VALUES (:1,:2,:3,:4,"
  &    " :5,:6)"
if (%ssc_open(dbchn, cur3, sqlp, SSQL_NONSEL,
  &   SSQL_STANDARD, 6, deptnum, deptname, manager,
  &   division, stdate, budget))
  goto err_exit
;where deptnum matches with :1, deptname matches with :2, etc.

For another example, see exam_fetch.dbl, which is in the connect\synsqlx subdirectory of the main Synergy/DE installation directory.