SQL_CALL

SQL_CALL

This activity executes a stored procedure on the target database.  Use this activity when your stored procedure does not return a result set.  It may, optionally return a single result that can be received in the SQLRESULT output parameter of this activity.

If your stored procedure returns a result set, then you should use the SQL_CALLQUERY or SQL_CALLQRYCSV activity instead.

This activity supports the use of SQL parameter markers (usually designated by a question mark) in the SQL statement.  If your solution uses this capability, you must first execute one of the following activities to supply the parameter value(s):

SQL_PARAMS	Set parameter values for SQL operation
SQL_PARAMSCSV	Set parameter values for SQL operation from CSV

 

 

Note that only input (IN) parameters for an SQL stored procedure are presently supported.  You cannot pass or receive OUT and INOUT stored procedure call parameters.

For more information about the SQL database activities, refer to the description of the SQL_CONNECT activity.  For a list of supplied working (*) examples using the SQL database activities, refer to:

Example Processing Sequences using the SQL database activities

INPUT Parameters:

SQLHANDLE : Required

This parameter must specify the connection handle value that identifies the SQL connection upon which this activity should operate.  The connection handle value is returned by the SQL_CONNECT activity.

SQLCALL : Required

This parameter must specify the name of the stored procedure (which may be qualified with a library or schema name) along with any parameters (or parameter markers) that it requires.  The CALL keyword should not be included.  It should usually be in the form:

<library or schema>.procedure(<parameters>)

OR

<library or schema>/procedure(<parameters>)

(depending on the syntax used by your database and JDBC driver.)

The SQL statement may make use of parameter markers (usually designated by a question mark).  You must use either the SQL_PARAMS or the SQL_PARAMSCSV activity before this activity, to supply the parameter values.  Note that the SQL_CALL activity will make use of only one set (or "row") of parameter values.

The following is an example that might be specified in this parameter to call the SQL stored procedure DXTUTSQL01, passing it two parameter values that have previously been supplied using either the SQL_PARAMS or the SQL_PARAMSCSV activity:

DXTUTSQL01(?, ?)

SQLRETURNING : Optional

This parameter specifies the expected data type of the return value, if any, from the stored procedure.  You can choose from the following values:

*NONE
*CHAR
*STRING
*SMALLINT
*INTEGER
*FLOAT
*DOUBLE
*DECIMAL
*NUMERIC

However, the datatypes possible depend on what your target database and JDBC driver can support.  And, in any particular instance, of course, it depends on the definition of the stored procedure.

The default value is *INTEGER.  If your database is on an IBM i server, note that presently the IBM i database only supports a data type of integer for values returned from a stored procedure.  If your stored procedure does not return a value, then you can specify *NONE to signify this.

OUTPUT Parameters:

SQLRESULT :

If successful, this output parameter will contain the return value, if any, from the stored procedure.