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}

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.

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.