%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, 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)

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

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:

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.