Home Applications ETL Interoperability Adapter

ETL Interoperability Adapter

This application is not supported by InterSystems Corporation. Please be notified that you use it at your own risk.
3.5
1 reviews
0
Awards
543
Views
28
IPM installs
4
1
Details
Releases
Reviews  (1)
Issues
Articles  (1)
Extend EnsLib.SQL.OutboundAdapter to add batch batch and fetch support on JDBC connection for Ensemble and IRIS.

What's new in this version

Publish on ZPM

BatchSqlOutboundAdapter

Extend EnsLib.SQL.OutboundAdapter to add batch and fetch support on JDBC connection.

Benchmark

On Postgres 11.2, 1 000 000 rows fetched, 100 000 rows inserted, 2 columns.

alt text

Prerequisites

Can be used on IRIS or Ensemble 2017.2+.

Installing

Clone this repository

git clone https://github.com/grongierisc/BatchSqlOutboundAdapter.git

Use Grongier.SQL.SqlOutboundAdapter adaptor.

New methods from the adaptor

  • Method ExecuteQueryBatchParmArray(ByRef pRS As Grongier.SQL.GatewayResultSet, pQueryStatement As %String, pBatchSize As %Integer, ByRef pParms) As %Status
    • pRS is the ResultSet can be use as any EnsLib.SQL.GatewayResultSet
    • pQueryStatement is the SQL query you like to execute
    • pBatchSize is the fetch size JDBC parameter
  • Method ExecuteUpdateBatchParamArray(Output pNumRowsAffected As %Integer, pUpdateStatement As %String, pParms…) As %Status
    • pNumRowsAffected is the number of row inserted
    • pUpdateStatement is teh update/insert SQL statement
    • pParms is Caché Multidimensional Array
      • pParms indicate the number of row in batch
      • pParms(integer) indicate the number of parameters in the row
      • pParms(integer,integerParam) indicate the value of the parameter whose position is integerParam.
      • pParms(integer,integerParam,“SqlType”) indicate the SqlType of the parameter whose position is integerParam, by default it will be $$$SqlVarchar

Example

  • Grongier.Example.SqlSelectOperation show an example of ExecuteQueryBatchParmArray
  • Grongier.Example.SqlSelectOperation show an example of ExecuteUpdateBatchParamArray

Content of this project

This adaptor include :

  • Grongier.SQL.Common
    • No modification, simple extend of EnsLib.SQL.Common
  • Grongier.SQL.CommonJ
    • No modification, simple extend of EnsLib.SQL.CommonJ
  • Grongier.SQL.GatewayResultSet
    • Extension of EnsLib.SQL.GatewayResultSet to gain the ablility to use fetch size.
  • Grongier.SQL.JDBCGateway
    • Use to allow compilation and support on Ensemble 2017.1 and lower
  • Grongier.SQL.OutboundAdapter
    • The new adaptor with :
      • ExecuteQueryBatchParmArray allow SQL query a distant database and specify the JDBC fetchSize
      • ExecuteUpdateBatchParamArray allow insertion in a distant database with JDBC addBatch and executeBatch
  • Grongier.SQL.Snapshot
    • Extend of EnsLib.SQL.Snapshot to handle Grongier.SQL.GatewayResultSet and the fetch size property
Made with
Install
zpm install sql-batch-outbound-adaptor download archive
Version
1.0.207 Apr, 2020
Category
Integration
Works with
EnsembleInterSystems IRIS
First published
09 Apr, 2019
Last checked by moderator
27 Jun, 2023Works