%SSC_DEFINE

Define host variables for a SELECT statement

value = %SSC_DEFINE(dbchannel, dbcursor, 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 of an open database cursor (opened with %SSC_OPEN). This must be in the range 1 through the number specified by maxcur when the database channel was initialized (with %SSC_INIT). (n)

numvars

The number of host variables in the SELECT statement, up to the maximum number of columns specified by the maxcol argument in %SSC_INIT. If numvars is negative, the define variables are overwritten rather than appended. (n)

var

One to numvars host variables. The number of var variables passed must equal the value of numvars. (a, n, or String)

Discussion

%SSC_DEFINE defines host variables to variables specified in a SELECT statement. Depending on the numvars setting, the definitions are either appended to the variables for the SELECT statement, or the SELECT statement variables are overwritten with the host variable definitions. %SSC_DEFINE affects only those variables that are used when %SSC_EXECUTE is called.

The total number of defined variables used by %SSC_MOVE must match the number of columns defined by the SELECT statement. For example, if there are 20 host variables in the SELECT statement, you could use one %SSC_DEFINE to append definitions for all 20 variables, or two %SSC_DEFINEs with 10 variables each, or any other combination as long as the number of variables defined by the %SSC_DEFINE call(s) exactly matches the number of variables described by %SSC_OPEN in the SELECT statement before %SSC_MOVE is called:

%SSC_DEFINE generates an error if the number of variables defined by one or more %SSC_DEFINEs exceeds the number of columns defined in the SELECT statement.
%SSC_MOVE generates an error if the number of variables defined by all calls to %SSC_DEFINE does not exactly match the number required by the SQL statement used in %SSC_OPEN.

You can use %SSC_DESCSQL if you are uncertain about the number of variables to define (for example, if you use a SELECT * statement). For more information on defining variables, see Defining variables.

Note the following:

The String data type (System.String) is not supported for var for array-based operations, and is not supported for Synergy .NET.
Do not use the Synergy DBL INIT statement for a String variable or a record that includes a String variable after passing that variable in a call to %SSC_DEFINE (unless you immediately pass it again in another call to %SSC_DEFINE). This will cause a runtime error.

If you use ^VARARGARRAY, note that numvars is the last declared argument for this routine.

Examples

The following example shows how to define four Synergy DBL variables associated with cur1 and two variables associated with cur2.

record order_rec
       ord_num            ,d6
       ord_cust           ,d6
       ord_odate          ,a10
       ord_sdate          ,a10
record customer_rec
       cust_num           ,d6
       cust_name          ,a30
.
.
.
sqlp = "SELECT or_number, or_customer, or_date, orsdate FROM orders"
if (%ssc_open(dbchn, cur1, sqlp, SSQL_SELECT))
  goto err_exit
if (%ssc_define(dbchn, cur1, 4, ord_num, ord_cust, ord_odate, ord_sdate))
  goto err_exit
sqlp = "SELECT or_number, or_customer FROM orders WHERE or_customer  =  101"
if (%ssc_open(dbchn, cur2, sqlp, SSQL_SELECT))
  goto err_exit
if (%ssc_define(dbchn, cur2, 2, cust_num, cust_name)
  goto err_exit