%SSC_EXECUTE

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

value = %SSC_EXECUTE(dbchannel, cursor, [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)

cursor

The ID number of the statement cursor or database cursor for the statement. This must be within the range from 1 through the maximum number specified by themaxcur argument for %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 database cursor properties and does not generate 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:

Data is physically bound at %SSC_EXECUTE time (unlike SELECT statements where data is actually bound at %SSC_OPEN or %SSC_REBIND time).
Input variables can be bound using %SSC_OPEN or %SSC_STRDEF. In database terms, this could be considered a direct execute without a previous prepare operation.
If %SSC_EXECUTE follows an %SSC_SQLLINK call (i.e., if you pass SSQL_POSITION), it executes the linked statement rather than the original SELECT statement.
When putting data for a large binary or character column, use the SSQL_LARGECOL option in conjunction with %SSC_LARGECOL. This instructs %SSC_EXECUTE to use the data in the string argument (buf) passed in the call to %SSC_LARGECOL. (If you don’t use SSQL_LARGECOL and %SSC_LARGECOL, you can put no more than 65,535 bytes for a column.)

SSQL_LARGECOL cannot be used with bulk inserts (SSQL_SQL_BULK_INSERT) .

If you have submitted a query and want to use the query for another database, do not use %SSC_EXECUTE to specify a different database. Instead use SSQL_USEDB, an %SSC_CMD option. %SSC_EXECUTE generally causes errors in this situation because it allows cached statements from the original database to be used rather than submitting the statement to the newly specified database.

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.