SQL

SQL File Export

Description

Exports the result of a SQL query to a flat file.

Parameters

Name Mandatory Default Description
SQL Exp Filename Yes Full path to the output file.
SQL Connection No SQL connection used for the query. This connection is automatically set on the action if a metadata link is added on this action.
SQL Transaction Name No SQL transaction name used for the query.
SQL Exp Java Driver No JDBC Driver used to create the connection for the query. This parameter is not needed if a SQL Connection is provided instead.
SQL Exp Java URL No JDBC URL used to create the connection for the query. This parameter is not needed if a SQL Connection is provided instead.
SQL Exp Java User No Database user used to create the connection for the query. This parameter is not needed if a SQL Connection is provided instead.
SQL Exp Java Password No Database user’s password used to create the connection for the query. This parameter is not needed if a SQL Connection is provided instead. This password is encrypted using the encrypt <password> command on the runtime engine console (engine command line tool).
SQL Exp Unencrypted Java Password No Database user’s password used to create the connection for the query. This parameter is not needed if a SQL Connection is provided instead. This password is in plain text.
SQL Exp SQL Query No Query to execute. This query should return records. Alternately, you can provide this query in the action’s code or in a SQL Exp SQL Query File.
SQL Exp SQL Query File No File containing the query to execute. This query should return records. Alternately, you can provide this query in the action’s code or in the SQL Exp SQL Query parameter.
SQL Exp File Format No variable Format of the output file: variable (delimited file) or positional (fixed width).
SQL Exp File Append No false Set to true to append the result of the query to the existing file. Otherwise, overwrite the existing file.
SQL Exp Row Sep On First Row When Append No true If SQL Exp File Append is set to true, then a row separator is added before the first row.
SQL Exp Row Sep on Last Row No false A row separator is added after the last row.
SQL Exp Add Record Number No false Adds a record number at the beginning of each row.
SQL Exp Generate If Empty No false Writes the file even if the query returns no record.
SQL Exp Generate Header No false Adds a header line containing the line of fields at the beginning of the file.
SQL Exp File Encoding No System’s encoding Encoding used for the file.
SQL Exp Field Sep No “;” ASCII field separator for a variable file. It may be composed of several characters. \n, \r, \t and \f can be used to specify special characters.
SQL Exp XField Sep No Hexadecimal field separator for a variable file. It may be composed of several characters.
SQL Exp Row Sep No System’s line break ASCII row separator. It may be composed of several characters. \n, \r, \t and \f can be used to specify special characters.
SQL Exp XRow Sep No Hexadecimal row separator. It may be composed of several characters.
SQL Exp Fetch Size No 1000 Fetch size (number of lines). By default, the runtime engine configuration value if used.
SQL Exp Date Format No yyyy/MM/dd HH:mm:ss.SSS Format for date fields. By default, the runtime engine configuration value if used.
SQL Exp Short Date Format No Format for date fields. By default, the runtime engine configuration value if used. There is no default value. If not specified, the default value will be the same as the Date Format parameter.
SQL Exp Numeric Pad Char No Space Character used to left-pad numbers in case of a positional file.
SQL Exp String Pad Char No Space Character used to right-pad strings in case of a positional file.
SQL Exp String Delimiter No ASCII character used to delimit strings in a variable file.
SQL Exp XString Delimiter No Hexadecimal character used to delimit strings in a variable file.
SQL Exp Local Language No System Language used for writing the file. The default value is the system value for the runtime engine. The language impacts the characters used, for example the character for the decimal separator.
SQL Exp Use Numeric Grouping Char No false Use digit grouping and the digit grouping symbol.
SQL Exp Numeric Grouping Char No System Digit grouping symbol. This symbol is used for example to group thousands, millions, etc.
SQL Exp Numeric Separator Char No System Decimal Symbol.
SQL Exp Boolean As Numeric No true Boolean values “true/false” are converted to “1/0”.
SQL Exp Absolute Numeric No false Use absolute numeric values.
File Buffer Size No 8192 File buffer size in octets.
SQL Exp Max File Size No -1 Maximum size of the files to generate. This size is expressed in octets, but can also be expressed in other units: for example 64K, 5M, 1G. -1 stands for no size limit. If this parameter is set, the action will automatically chunk the data into files of this size, suffixing the file names with an increment starting with 0.

SQL Operation

Description

This action has several functions:

Parameters

Name Mandatory Default Description
SQL Action Type Yes DDL_DML Type of SQL Command. This command may be a standalone SQL command ( DDL_DML), a SELECT command used as the source of a bind link, or an INSERT, UPDATE, DELETE command used as the target of a bind link.
SQL Connection No SQL connection used for the command. This connection is automatically set on the action if a metadata link is added on this action.
SQL Transaction Type No AUTOCOMMIT Type of transaction. AUTOCOMMIT: an autocommit connection is picked up from the connection pool, COMMIT: a commit is issued after the SQL command, NOCOMMIT: no commit is issued after the SQL command, AUTONOMOUS: a separate autocommit connection is created for this command.
SQL Transaction Name No SQL transaction name used for the command.
SQL Transaction Commit Nb No -1 Number of lines after which a commit must be issued. This number if valid only for INSERT, UPDATE and DELETE commands. -1 stands for no commit.
SQL Transaction Isolation No default database value Transaction isolation: TRANSACTION_NONE, TRANSACTION_READ_COMMITTED, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE.
SQL Action Type Stat No Type of statistic. This field allows creation of a named statistic with the number of lines managed by the command. The Stat type name is prefixed with SQL_STAT. For example, if ‘INSERT’ is written in this parameter, a SQL_STAT_INSERT variable will be created
SQL Fetch Size No 1000 Fetch size (number of lines) for SELECT commands. By default, the runtime engine configuration value is used.
SQL Batch Size No 1000 Fetch size (number of lines) for INSERT, UPDATE and DELETE commands. By default, the runtime engine configuration value is used.
SQL Wait Data No false Moves the command into data wait mode. The action waits until the query specified either in the action code or in SQL Wait Count Select returns at least SQL Wait Nb Rows or until the SQL Wait Timeout is reached.
SQL Wait Poll interval No 1000 Polling interval when SQL Wait Data is set to true.
SQL Wait Timeout No Waiting timeout when SQL Wait Data is set to true.
SQL Wait Nb Rows No Number of rows to wait for when SQL Wait Data is set to true.
SQL Wait Count Select No Query returning a number of rows to wait for, when SQL Wait Data is set to true.
SQL Multi Queries No false Activates the Multi-Query mode. In this mode, several statements can be set in the code, separated by the SQL Multi Queries Separator.
SQL Multi Queries Separator No ; Character separating the queries in the code when Multi-Query mode is activated.
SQL Multi Queries Exclusion Patterns No Regular expression defining the lines to exclude from the list of statements. These lines are typically SQL comments.

SQL To Parameters

Description

Publishes the result of a SQL query to a parameter.

Parameters

This action does not take any parameter. It uses the current connection linked to the process.
In the action code, specify the parameter and the SQL query using the syntax below:

<sqlToParameters>
<parameter name="PARAMETER_PATH" type="PARAMETER_TYPE" failsIfNoRecord="FAILS_BOOLEAN" defaultValue="DEFAULT_VALUE">
SQL_QUERY
</parameter>
</sqlToParameters> 

Where

The following example sets the SQL_NB_ROWS integer parameter to the value returned by the SELECT COUNT statement:

<sqlToParameters>
<parameter name="./SQL_NB_ROWS" type="Integer">
Select count(*) from %x{md:physicalPath($REF,'workName')}x%
</parameter>
</sqlToParameters> 

The following example sets the SQL_NB_ROWS integer parameter to the value returned by the SELECT COUNT statement. The parameter is published on the father process :

<sqlToParameters>
<parameter name="../SQL_NB_ROWS" type="Integer">
Select count(*) from %x{md:physicalPath($REF,'workName')}x%
</parameter>
</sqlToParameters> 

Note: It is possible to publish several parameters at the same time by using multiple <parameter>...</parameter> blocs.