SQL_CALLQUERY
This activity executes a stored procedure on the target database. Use this activity when your stored procedure returns a result set.
If your stored procedure does not return a result set, then you should use the SQL_CALL activity instead. Refer also to the description of the SQL_CALLQRYCSV activity for an alternate way to receive the result set from a stored procedure call.
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):
|
Set parameter values for SQL operation |
|
|
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 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 this activity, to supply the parameter values. Note that the SQL_CALLQUERY 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(?, ?)
SQLMAXROWS : Optional
This parameter may specify a maximum number of rows to be returned. This guards against the possibility of stored procedure calls that select much more data than was intended.
If not specified, a default value of 100 (one hundred) is used. Remember that the SQL database activities are not intended and not usually suitable for high-throughput, high-volume database operations. If you expect that your stored procedure will return a large number of records, then you should possibly consider an alternate implementation, such as using a Transformation Map.
OUTPUT Parameters:
SQLROWS :
Upon successful completion, this parameter will contain the actual number of rows returned by the stored procedure call.
SQLCOLUMN1
SQLCOLUMN2
…
SQLCOLUMN25
These output lists will contain a list of the values for the corresponding column for each row returned by the stored procedure call.
You should specify the name of a variable list that will contain the values for each column returned by your stored procedure, up to a maximum of 25.
You may specify fewer output lists than returned from your stored procedure call. However, if you specify more, then a run-time error will occur, for example
, or