database ingress connector

Database Ingress Connector

Version: 17.07

Supported Since: 17.01

What is Database Ingress Connector?

The database ingress connector can be used to read data from a SQL database in a scheduled manner. The SQL query is pre configured and the parameters for the SQL query can be passed through a configuration file. Simple and advance scheduling strategies can be provided using a cron expression.

Database ingress connector can be used with relational SQL database system such as Oracle, MS SQL Server and MySQL.

In order to use the Database Ingress Connector, you must first select the Database Connector dependency from the connector list when you are creating an empty Ultra project. If you have already created a project, you can add the Database Connector dependency via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the Connectors list, select the Database Connector dependency.
database ingress connector 4

Out Ports

Processor

Message will be emitted from this out port with the result set obtained from the database as the message payload

On Exception

The message will be emitted from this out port, if an error occurred while querying the database.

Parameters

* marked fields are mandatory

Data Source *

Basic

Resource bean for the database connection to be used by the connector.

SQL query *

Basic

SQL query with parameters

Ex: select column_name_1 from table_name where column_name_2=@{parameter_1} and column_name_3=@{parameter_2}

If Concurrent Polling Count parameter is configured to have a value greater than 1, consider utilizing row locking in the SQL query to prevent the same set of rows getting picked by more than 1 thread for processing.

Ex: select column_name_1 from table_name where column_name_2=@{parameter_1} and column_name_3=@{parameter_2} for update skip locked

Mapping file

Basic

File path to the DSON configuration mapping file.

Response Timeout

Advanced

Timeout in milliseconds after which the request timeouts

Scheduler Configuration

Scheduling

Bean reference of the scheduler configuration bean which should be declared as a resource in the project.xpml file. By default there is internal scheduler configuration within the framework which will be shared by all the polling connectors. If you need to configure higher level of concurrent processing threads which will query the table, you can configure your own thread pool configuration while declaring the parameters of the scheduler configuration bean as below.

<x:resources>
    <x:resource id="custom-scheduler-config">
        <bean id="schedulerConfigBean" class="org.adroitlogic.x.base.trp.SchedulerConfig">
            <constructor-arg name="name" value="my-custom-scheduler"/>
            <property name="schedulerThreadCount" value="4"/>
            <property name="pollingThreadCoreSize" value="4"/>
            <property name="pollingThreadMaxSize" value="10"/>
            <property name="pollingQueueSize" value="25"/>
            <property name="pollingKeepAliveTime" value="5000"/>
        </bean>
    </x:resource>
</x:resources>

In this configuration,

  • schedulerThreadCount - is the number of threads which will be used to schedule the number of threads to be used to handle scheduled polling tasks in this Database Ingress Connector. Generally this value should be smaller value since polling threads don’t do heavy task in this, rather it’s just initiating the polling task for the scheduling iteration and handover the file fetching and processing task to a separate executor service. This executor service can be configured by using next four parameters of above scheduling configuration bean.

  • pollingThreadCoreSize - is the core size of the Database query thread pool

  • pollingThreadMaxSize - is the maximum number of threads of the Database query thread pool

  • pollingQueueSize - is the queue size of the Database query thread pool

  • pollingKeepAliveTime - is the keep alive time of the Database query thread pool

Polling Cron Expression

Scheduling

Cron expression for the database table querying schedule. Cron expression should be a valid Quartz cron expression since the Framework is underneath using Quartz to extract the schedule from the cron expression.

Polling Start Delay

Scheduling

Delay in milliseconds to start the polling schedule. Any iteration which comes within this time period from the startup time of the framework, won’t be considered as a valid file polling iteration.

Polling Repeat Interval

Scheduling

Interval in milliseconds for the next iteration of the polling schedule. This will be considered if there isn’t a configured cron schedule.

Polling Repeat Count

Scheduling

Number of iterations which should go through the polling schedule. If this is set to 1 which means only the first iteration of the polling schedule will be considered as a valid file polling iteration and all other iterations of the schedule will be ignored. By default, this value is set to -1 which means it will consider all the iterations of the polling schedule as a valid iteration.

Concurrent Polling Count

Scheduling

Maximum number of concurrent threads which can be used to query the table by executing the SQL statement. By default, this value is 1.

If this parameter is configured to have a value greater than 1, consider utilizing row locking in the SQL query to prevent the same set of rows getting picked by more than 1 thread for processing.

Ex: select column_name_1 from table_name where column_name_2=@{parameter_1} and column_name_3=@{parameter_2} for update skip locked

Concurrent Execution Count

Scheduling

Maximum number of concurrent threads which can be used to process the queried result from the table. By default, this value is 4.

Sample Use Case

Prerequisite

First open the project.xpml file of the project and right click on it (on the XML content of the file). From the context menu, select Resource Template as shown in below figure.

database ingress connector 5

After that from the shown dialog box, select Database template.

database ingress connector 6

Next you need to specify the required parameters as shown below. It is mandatory to specify a bean prefix and for that you can specify any value.

database ingress connector 7
Setting up the connector

Now let’s configure a Database Ingress Connector to read a row from a database table for 5 times in a interval of 30 seconds and print the result set in the log file.

Suppose there is a table named as table_1 with 2 columns names as column_1 and column_2. Then the sample integration flow will be as follows.

Image of the flow

Sample configuration of the Database ingress connector for the above flow will be as follows.

Image of the configuration

Here the SQL query should be a SELECT query and other types of queries are not supported by the ingress connector. Query can be a simple SQL query or an advance one with nested queries. Parameters to the query can be passed using the @{parameterName} format.

Now we have configured a datasource for the database to be used and a SQL query with parameters. Then we have to setup the configuration to extract values to replace the parameters. This is done using a mapping file. In this mapping file we have to declare where to extract parameter values from. For the Ingress connector, the parameter values are just hard coded to the mapping file itself (In Database Egress Connector and Database Processing Element more options are available). Format of the mapping file is as follows.

{
  "parameters":[
    {
      "name": "parameter_1",
      "type": "VALUE",
      "value": "value_1"
    },
    {
      "name": "parameter_2",
      "type": "VALUE",
      "value": "value_2"
    }
  ]
}

This mapping file can be generated using the UI provided for mapping generation as well.

In this topic
In this topic
(c) Copyright 2016-2017 AdroitLogic. All rights reserved.
Terms of Use
Contact Us