Open a cursor

WTSupported in traditional Synergy on Windows
WNSupported in Synergy .NET on Windows
USupported on UNIX
VSupported on OpenVMS
value = %SSC_OPEN(dbchannel, dbcursor, statement, SSQL_SELECT|SSQL_NONSEL,
       [options], [numvars][, var, ...])

Return value


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



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


The ID number for the cursor. This argument serves two purposes: it returns the ID number for the cursor for statement, and it determines whether %SSC_OPEN will simply open a new cursor for the statement or whether it will attempt to reuse a cursor. (n)


An SQL statement. (a)


The SQL statement type is a SELECT statement or the name of a SELECT stored procedure. (n)


The SQL statement type is a non-SELECT statement (such as INSERT, UPDATE, or DELETE). (n)


(optional) Sets options used to configure a cursor. (All but SSQL_LARGECOL are useful only with cursors for SELECT statements.) You can pass more than one option by joining the options with a plus sign (+). For a list of valid options and details on using them (including a table that lists which options can be used together in the same call), see The options argument. (n)


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


(optional) Host variable to be bound to statement. 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)


%SSC_OPEN opens a cursor and associates it with the passed SQL statement (statement). The cursor is opened on the database channel specified by dbchannel. This topic discusses %SSC_OPEN options and issues, but for more information on cursors, including information on cursor types and specifying cursor behavior with %SSC_OPEN and %SSC_CMD cursor options, see Using cursors with SQL Connection. Note the following:

Multiple cursors, dbcursor, and cursor reuse

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

Note that you may not need to open a new cursor for each SQL statement. If you’re going to reuse the same operation soon, it’s best to reuse a 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.

Additionally, 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.

The options argument

Valid values for the options argument are listed in the table below.


What it does


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, creates a cursor of the default type for the database.

This can be used only with VTX0, VTX11, or VTX12_SQLNATIVE.

SSQL_SCROLL_ASENSITIVE Creates a keyset cursor when using SSQL_FORUPDATE. Otherwise, it creates a static cursor. This can be used only with VTX7, VTX11, or VTX12_SQLNATIVE.


Creates a dynamic scrolling cursor. This can be used only with VTX11 or VTX12_SQLNATIVE.


Creates a read-only scrolling keyset cursor. This can be used only with VTX11 or VTX12_SQLNATIVE.

SSQL_SCROLL_STATIC Creates an insensitive scrollable static cursor (tempdb). This can be used only with VTX7 or VTX11.


Enables SQL Connection to use %SSC_LARGECOL to get or put large binary columns or large character columns.


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.


Explicitly disables prefetch caching.


Creates a positioned cursor that is positioned at the first record that meets the criteria for the query.


Creates a standard (non-positioned) cursor, and when used without other options for the options argument, enables prefetch caching.

The options argument enables you to specify

The SSQL_SCROLL options for %SSC_OPEN specify scrolling cursor types. (For SQL Server, these are ODBC API cursor types.) With a scrolling cursor you can determine which row will be retrieved with the next fetch. (You do this by setting an SSQL_CMD_SCROLL option with %SSC_CMD.) Note the following:

For descriptions of and information on specifying cursor types (using %SSC_OPEN and/or %SSC_CMD), including information on creating a forward-only cursor (there’s no %SSC_OPEN option for this), see Specifying a cursor type.

For the other options (SSQL_LARGECOL, SSQL_FORUPDATE, etc.), note the following:

See the table below for which options can be combined in a single %SSC_OPEN call. 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 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.