SQL_PARAMS

This activity sets parameter values to be used subsequently by one of the following SQL database activities:

SQL_CALL Execute an SQL stored procedure SQL_CALLQRYCSV Query database using an SQL stored procedure to CSV SQL_CALLQUERY Query database using an SQL stored procedure SQL_UPDATE Update database using SQL

The parameter values are used in the SQL statement used by one of the above activities in place of any parameter markers (usually designated by a question mark) that are specified in the SQL statement.

Note that any parameter values that have been set will be cleared after executing any of the above activities or the FOR_EACH_SQL_QUERY, SQL_QUERY or SQL_QUERYTOCSV activities.  If you need to use the same set of parameters more than once, then you will need to repeat this activity before each activity that uses the parameters.

Note that using SQL_PARAMS or SQL_PARAMSCSV and then subsequently NOT using the corresponding parameter markers in the following SQL database activity (such as SQL_UPDATE) may yield a run-time error.  This behavior may depend on the actual SQL database engine being used.  For example, when used with the IBM i DB2 database you may receive:

ERROR – Descriptor index not valid

Refer also to the description of the SQL_PARAMSCSV activity, which performs a similar function but permits the parameter values to be supplied from a CSV file.

For more information about the SQL database activities, refer to the description of the SQL_CONNECT activity.

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.

SQLPARAM1
SQLPARAM2
…
SQLPARAM25: Optional

Use these parameters to supply one or more variable lists, up to a maximum of 25, each "row" of which supplies one set of the parameter values to be used.  Each of the lists supplied should have the same number of entries.

A subsequent SQL_CALL, SQL_CALLQRYCSV or SQL_CALLQUERY will use only one set (or "row) of parameter values.

In the case of a subsequent SQL_UPDATE activity, the requested operation (such as insert, update or delete) specified for the activity will be performed once for each set of entries (or "row") of parameter values.

OUTPUT Parameters:

There are no output parameters.