SQL_CALLQRYCSV
This activity executes a stored procedure on the target database, receives a result set and writes the result set directly into the specified file in CSV format.
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_CALLQUERY 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_CALLQRYCSV 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(?, ?)
SQLFILEPATH : Required
This parameter must specify the path and file name of the CSV file to be created or appended by this activity. If the file already exists (and NO is specified or assumed for the SQLAPPEND parameter), then the file will be overwritten by this activity.
SQLAPPEND : Optional
If the specified output file already exists, then you can specify YES for this parameter to cause the activity to append the new results to the end of the existing contents of the file. Otherwise the file is replaced with the new contents. If the specified output file does not already exist, then this parameter is ignored.
SQLCCSID : Optional
This parameter applies only on IBM i servers. It allows you to specify the IBM i CCSID with which the output CSV file is created.
If the parameter is not specified, a default of *DEFAULT is assumed, which instructs the activity to create the output text file using the CCSID for the job in which the activity is executing. (You can also specify the special value of *JOB which has the same effect.)
Otherwise, you should specify the numeric CCSID value required. For example, a value of 1208 means UTF-8. Refer to IBM i documentation for a complete list and description of the available CCSIDs.
NOTE: the assumed or explicit CCSID is applied only when the specified output file does NOT already exist. If the specified output file already exists and is being replaced or appended-to by this activity, then its CCSID will not be changed.
SQLCOLUMNS : Optional
This parameter must specify the number of columns from the expected result set that are to be written to the CSV file, up to a maximum of 25. This, of course, will depend on the stored procedure that you are calling.
You may specify fewer columns than returned from your stored procedure call. However, if you specify more, then a run-time error will occur, for example
, or
COLSEPARATOR : Optional
You may specify the separator character that is used to separate the column values in each line of the CSV output file. The most common form of CSV uses a comma as the separator, and that is the default value for this parameter if you do not specify it.
If a non-comma separator is specified, it should be 1 character in length and can consist of any character.
COLHEADINGS : Optional
If you wish the first row of the output CSV file to contain comma-separated column headings, you may specify the heading line in this parameter. Note you should specify the entire string, including the separators, as a single value – for example:
Heading1,Heading2,Heading3
If you do not specify a value for this parameter, then no heading row will be written to the CSV file. If you specified YES for the SQLAPPEND parameter, then the COLHEADINGS value is not used.
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 and written to the CSV file.