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 Batch Executor

PreviousSQL ExecutorNextFile

Last updated 1 year ago

This component is used to execute Create/Update/Delete operations for multiple input records to the target table. It can execute only one or two queries unlike SQL Executor.

The available operations of this component are these.

  • Insert

  • Update

  • Delete

  • Insert & Skip - If insert fails with duplicate error, ignore the error.

  • Update & Skip - If update does not affect any record, ignore that zero update.

  • Insert & Update - If insert fails with duplicate error, update is executed.

  • Update & Insert - If update does not affect any record, insert is executed.

This component can process the records concurrently with ParallelCount property. If ParallelCount is 1, this is single thread execution. If the count is greater than 1, this is parallel execution with multiple threads.

The query used for Insert & Skip to Mysql and Postgresql are not same as other databases.

· The INSERT IGNORE syntax in MySQL allows you to insert data into a table without causing an error if a duplicate key exists. Instead of throwing an error, the statement simply skips the insertion of the row and returns a warning. The update count is set to 0 because no row was actually inserted into the table. This can be useful when you want to insert data into a table but don't want to have to check for duplicates beforehand.

· Postgresql has ON CONFLICT(primary_key_name) DO NOTHING syntax. This does same thing as IGNORE of Mysql.

Most databases allow to commit partial records except the failed records, but Postgresql rolls back entire records if any record fails in a transaction. Postgresql has a feature called Atomicity, which ensures that either all operations of a transaction succeed or none of them do. If any part of a transaction fails, the entire transaction is rolled back and all changes made during the transaction are undone. This means that partial records cannot be committed in Postgresql, and the database will always maintain data consistency.

INSERT IGNORE is used to avoid unnecessary check for duplicates but ON CONFLICT of Postgresql is inevitable for Insert & Skip operation.

The Mapping component is used to transform the input data to match the columns of the target table. The output of the Mapping component is then passed to the SQLBatch component for bulk insertion into the target table.

Input

Attribute
Description

System Id

Database system id

Data Structure Id

Data structure id for table layout. This id is used in mapping.

Table Name

Target table

Timeout

Query timeout. Query timeout is waiting time until one query execution is complete. This is about one execution of single record or batch records.

Query Type

Literal - query is constructed as a literal.

Prepared - query is constructed with ?

Operation Type

SQL Query

CRUD Type

Target operation

Mapping Info

Input record for the operation

Input Media

Input source

  • File

  • Parameter

Input Count

Input record count.

This property specifies the number of input records that the component should read before completing its execution.

If the input data has more records than the specified InputCount, the component will stop reading records after it has reached the specified count. However, if the input data has fewer records than the specified InputCount, the component will stop reading records after it has reached the end of the data.

In summary, the InputCount property determines the maximum number of records that the component will read and execute.

If empty, the entire input records will be read and executed.

Query

User defined query

Parallel Count

Worker count. Default is 1

Like SQL Executor component, this component also has the extended properties for the custom database types.

Output

Attribute
Description

ResultCode

Error code from the database

ResultMessage

Error message from the database

ResultCount

Insert/Update/Delete count

This component does not generate result set like SQL Executor. No select operation is available.

Example

This example is simple DB - DB synchronization.

This flow has two routing paths(__RoutingPath). One is single and the other is parallel. These paths are about ParallelCount. SQLExecutor component retrieves data from the source table, mapping component generates input data for SQLBatchExecutor, and SQLBatchExecutor inserts the records to the target table.

These results are about 300k records which have 400+ bytes respectively.

ParallelCount = 1

ParallelCount = 10

Source database

Target database

Chart, box and whisker chart

Description automatically generated