%SSC_EXECIO

Execute a stored procedure with I/O parameters

value = %SSC_EXECIO(dbchannel, stcursor, [ncount], [numvars][, type, var, arg][, ...])

Return value

value

This function returns one of the following: the return result of a stored procedure, an error code returned by the database, SSQL_NORMAL (success), or SSQL_FAILURE (failure). (Negative values are considered an error.) (i)

Arguments

dbchannel

An internal database channel previously initialized using %SSC_INIT and connected by %SSC_CONNECT. (n)

stcursor

The ID number for a statement cursor. This must be within the range from 1 through the maximum number specified by the maxcur argument for %SSC_INIT call. The cursor must have been opened by %SSC_OPEN. (n)

ncount

(optional) The number of rows to execute. The default value is 1. Only certain databases (Oracle, SQL Server, and Sybase) support multirow operations. If this argument is used with a database that does not support multirow move, ncount must be set to 1. (n)

numvars

(optional) The number of host variables for sending to or receiving from the stored procedure. (The maxcol argument, as specified in %SSC_INIT, specifies the maximum value for numvars.) If you pass numvars, you must also include the type, var, and arg arguments. The number of times you include the type, var, arg series must equal the value of numvars. (n)

type

(optional) The type of the first host variable. (Required if numvars is passed.) If you pass SSQL_EXBINARY, you must also pass one of the other types and connect the two with a plus sign (+)—for example, SSQL_EXBINARY+SSQL_OUTPUT. (n)

SSQL_INPUT	Input
SSQL_OUTPUT	Output
SSQL_INOUT	Input and output
SSQL_OUTDATE	Output date field (to conform to pre-7.1 zoned conversion rules)
SSQL_EXBINARY	A binary column (instructs %SSC_EXECIO to leave the data as is; see the Discussion below)

var

(optional) The first host variable. (Required if numvars is passed.) (a, n, or String)

arg

(optional) The name of the argument in the stored procedure. (Required if numvars is passed.) (a)

Discussion

%SSC_EXECIO executes stored procedures that use input/output parameters. It does not does not accept a result set. For more information on using stored procedures with SQL Connection, see Running stored procedures from SQL Connection.

Note the following:

When retrieving data into alpha fields, %SSC_EXECIO converts binary zeros to spaces and trims trailing spaces unless you use the SSQL_EXBINARY option. If you use this option, %SSC_EXECIO passes the data as is.
When sending alpha variable data to a SQL Server or Oracle database, %SSC_EXECIO trims trailing spaces unless you use the SSQL_EXBINARY option. If you use this option, %SSC_EXECIO preserves the original length.
Using %SSC_INDICATOR for SSQL_INOUT parameters is not possible because the indicator used internally in SQL Connection cannot be set to –1, or the INOUT bound data would be interpreted as null. SQL Connection depends on the initial state of the indicator variable set to –1 to be able to detect rows that are null or not fetched.
Be careful not to confuse the return value with the SSQL_NOMORE return value for %SSC_MOVE. If you need to know if the rows were actually transferred as a result of a stored procedure reading data, you should return the row count (or number of rows returned) as one of your passed parameters. This way you can tell if your stored procedure was successful. %SSC_EXECIO differs from %SSC_EXECUTE and %SSC_MOVE in this area regarding row count.
If you pass ncount as a number greater than 1, you must use arrays for the host/bind variables; otherwise, an error is generated.
Do not use a String (System.String) variable for var for SSQL_OUTPUT or SSQL_INOUT if the returned data could be larger than the size of the variable.
The String data type (System.String) is not supported in Synergy .NET.
If you use ^VARARGARRAY, numvars is the last declared argument for this routine.
%SSC_EXECIO does not support columns larger than 65,535 bytes.

Examples

The Connectivity Series distribution includes example programs (in the connect\synsqlx directory) that use %SSC_EXECIO with stored procedures:

For an Oracle example, see stp_ora.dbl.
For SQL Server examples, see stp_odbc.dbl.
For MySQL, see stp_mysql.dbl.