ISM Manual
  • Overview
  • Features
  • Components
  • Directory Structure
  • Startup/Shutdown
  • ISM Admin UI
    • Dashboard
    • Flow
    • Job
      • Schedule
    • Channel
      • AMQP
      • Mqtt
      • sFTP
    • Interface
      • System
      • Data Structure
      • Field Group
      • Field
    • Web Service
    • Utility
    • Admin
    • Result
  • Tasks
    • Control Task
      • Route
      • Split/Join
      • Mapping
    • FTP
      • FTP Input
      • FTP Output
      • FTP Transfer
    • DB
      • SQL Executor
      • SQL Batch Executor
    • File
      • File Input
      • File Output
      • File Validator
      • Record Extractor
    • Excel
      • Excel Reader
      • Excel Writer
    • Flow
      • Flow Execution
      • Wait Sub
    • Web Service
      • REST Client
      • Web service Client
    • PGP
      • Encrypt
      • Decrypt
    • Cloud
      • SharePoint
      • Amazon S3
      • Google Cloud Storage
    • Others
      • Email Sender
      • LDAP Client
      • Function
      • Script
      • Java Class
  • REST Service
  • Trouble Shooting
  • Logging Configuration
  • Implementing a Task
  • Custom Class
  • Frequently Asked Questions (FAQ)
    • What is XNARUM Integration Service Mastery (ISM)?
    • What is the purpose of XNARUM?
    • Can XNARUM be customized to specific integration requirements?
    • Is XNARUM scalable?
    • Does XNARUM provide support and maintenance?
Powered by GitBook
On this page
  • Input
  • Output
  • Example
  1. Tasks
  2. DB

SQL Executor

PreviousDBNextSQL Batch Executor

Last updated 1 year ago

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.

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.

2) Click generate () button and generate query.

Graphical user interface, text, application

Description automatically generated