%SSC_OPEN

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

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)

dbcursor

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)

statement

An SQL statement. (a)

SSQL_SELECT

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

SSQL_NONSEL

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

options

(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)

numvars

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

var

(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)

Discussion

%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. See %SSC_SQLLINK.

The options argument

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

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

SSQL_SCROLL_DYNAMIC

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

SSQL_SCROLL_READONLY

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.

SSQL_LARGECOL

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

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, 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; see SSQL_CMD_SCROLL.) 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)

 

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.