SQL Executor

This component is used to execute single or multiple SQL queries on the target database. The component can be configured with SQL statements that will be executed against the target database. The results of the query are sent to other components for further processing. This component is useful for tasks such as data retrieval, data updates, and database schema modifications.

If Multiple Query attribute is checked, each query generates its own result data.

Otherwise, SQL Executor executes whole queries in a single statement. Whether those whole queries are acceptable depends on the database and jdbc driver. The sample below is the result to maria DB and with mysql jdbc driver.

Input

Attribute	Description
System Id	Database system id
Use Data Structure?	Map query result as data structure?
Save To File?	Save the query result into a file backed up list? This option is particularly useful when dealing with large record sizes. High volumes of records can lead to an OutOfMemory error, making this option a valuable tool for managing memory usage and avoiding potential issues.
Query	Query to be executed. Multiple queries are allowed. Queries are separated by semi-colon.
Multiple Query?	If checked, Query data is parsed, delimited by semi colon, and executed one by one. If unchecked, Query data is executed in one statement.
Query Type	Query type Literal - the query is constructed as a literal. Prepared - the query is constructed with ?.
Operation Type	Query or Stored procedure
Output Parameters	If operation type is procedure, output parameters can be assigned.

Query can be generated from table layout.

1) Choose a database.

2) Click generate () button and generate query.

Query can contain parameters.

If Query Type is prepared,

The query is converted into this sql.

select * from source where year = year(curdate()) and zone = ? and today = ?

Otherwise, parameters are replaced with the real values and single quotation for string type values.

select * from source where year = year(curdate()) and zone = 'zone01' and today = '2023-03-01'

If Save To File is checked, and operation is select, then the result set is stored in a file. The list in the memory has the index information. It does not have the data. The real data is stored in a temporary file. This file is generated at the directory which java.io.tmpdir property points to with .fjs extension. The file is deleted after the list is garbage collected. If the expected record size of the input is huge, Save To File should be checked.

This component gets the database connection from the information of system id. But the connection info can be acquired through parameterized way out of ISM.

If these properties exist, this component use these properties instead of system id.

Attribute	Description
Connection String	Custom connection string
User ID	Custom user id
Password	Custom password
Driver Class	Custom JDBC driver class

Thiese custom properties can be used for the databases like FireBird or Cubrid which ISM does not support through System menu.

FireBird	Cubrid

Output

Attribute	Description
ResultCode	Error code from the database
ResultMessage	Error message from the database
ResultCount	Retrieved count for Select, Update/Insert/Delete count for Update/Insert/Delete operations. If multiple queries are executed, ResultCount-index_number will be generated. The index number starts from 0.
ResultArray	Retrieved data set. If multiple queries are executed, ResultArray-index_number will be generated.
DataStructureId	Data structure id of the retrieved data set

If UseDataStructure property is yes, the result set is carried in this parameter - MyResult. The default name of this parameter is DataStructureId and is duplicate with another parameter. The name should be changed if this result set is used in mapping.

DataStructureId -> other name

Example

This example flow simply generates the result set.