%SSC_EXECUTE

Execute a non‑SELECT statement (no I/O parameters)

WTSupported in traditional Synergy on Windows
WNSupported in Synergy .NET on Windows
USupported on UNIX
VSupported on OpenVMS
value = %SSC_EXECUTE(dbchannel, dbcursor, [option], [ncount][, row_count])

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 logical cursor number within the range from 1 through the maximum number specified by maxcur during %SSC_INIT. The cursor must have been opened by %SSC_OPEN. (n)

option

(optional) The type of operation. If you specify SSQL_LARGECOL, you can combine it with one of the other options by joining the options with a plus sign (+)—for example, SSQL_POSITION+SSQL_LARGECOL. (n)

SSQL_LARGECOL

Use this if you’ll use %SSC_LARGECOL for a large binary or character column. (See the Discussion below.)

SSQL_POSITION

Use this for a linked cursor (i.e., if you use %SSC_SQLLINK).

SSQL_STANDARD

Use this for a non‑linked cursor (i.e., if you don’t use %SSC_SQLLINK). (default)

ncount

(optional) The number of rows to execute. The default value is 1. (n)

Only certain databases (Oracle, Sybase, and SQL Server) support multirow operations. If this argument is used with databases that do not support multirow moves, ncount must be set to 1 (the default value).

row_count

(optional) Returned number of rows affected by executing the SQL statement associated with dbcursor. This count is valid only when value is returned as SSQL_NORMAL. If value is SSQL_NORMAL, a return value of zero for row_count indicates there are no matches for the statement’s WHERE clause. (n)

Discussion

%SSC_EXECUTE executes a non‑SELECT statement and returns the result. (It does not set cursor properties and does not accept a result set.) This function is typically used to insert, delete, and update data. It can also be used to run a non‑parameterized stored procedures in some cases (see Invoking a stored procedure).

Note the following:

Examples

The following examples execute non‑SELECT SQL statements. Note that for the first example, if the SQL statement is valid, but no rows meet the WHERE clause criteria, the function will return SSQL_NORMAL, and rows_returned will be returned as zero.

sqlp = "UPDATE customers SET cust_limit = 5000 WHERE cust_rtype > 1"
if (%ssc_open(dbchn, cur1, sqlp, SSQL_NONSEL))
  goto err_exit
if (%ssc_execute(dbchn, cur1, SSQL_STANDARD,, rows_returned))
  goto err_exit
if (rows_returned) ;If any row met the "cust_rtype" criterion...
.
.
.

The next example drops a table named org:

if (%ssc_open(dbchn, cur1, "DROP TABLE org", SSQL_NONSEL))
  goto err_exit
if (%ssc_execute(dbchn, cur1, SSQL_STANDARD))
  goto err_exit

The following example is for SQL Server:

sqlp = "CREATE TABLE org1 (deptnum int NOT NULL, deptname"
  &      " char(6) NOT NULL, manager int NOT NULL, division"
  &      " char(15) NOT NULL, stdate datetime, budget numeric)"
if (%ssc_open(dbchn, cur1, sqlp, SSQL_NONSEL))
  goto err_exit
                                   ;Execute the SQL in standard mode
if (%ssc_execute(dbchn, cur1, SSQL_STANDARD))
  goto err_exit

The following example is for Oracle:

sts = %ssc_commit(dbchn, SSQL_TXON)       ;Begin transaction mode
sqlp = "INSERT INTO org1 (deptnum, deptname, manager, division, "
  &    "hrdate, salary) VALUES (:1,:2,:3,:4,to_date(:5,"MM/DD/YYYY"),:6)"
                                          ;Open another cursor
if (%ssc_open(dbchn, cur2, sqlp, SSQL_NONSEL, SSQL_STANDARD, 6,
  &             deptnum, deptname, manager,
  &             division, hrdate, salary))
  goto err_exit
for ix from 1 thru MX_REC               ;Do insert
begin                                   ;Load data to bind area
    deptnum = s_deptnum(ix)
    deptname = s_deptname(ix)
    manager = s_manager(ix)
    division = s_division(ix)
    hrdate = s_hrdate(ix)
    salary = s_salary(ix)
                                        ;Execute insert statement
    if (%ssc_execute(dbchn, cur2, SSQL_STANDARD))
      goto err_exit
  end
sts = %ssc_commit(dbchn, SSQL_TXOFF)   ;Commit the change and end
                                       ; transaction mode
if (%ssc_close(dbchn, cur2))
  goto err_exit

For an example of %SSC_EXECUTE that includes an example of a bulk insert, see exam_create_table.dbl in the connect\synsqlx subdirectory of the main Synergy/DE installation directory.