Create a DB Poller Service

The DB poller service fetches records from a database table at predefined intervals and applies business logic on each fetched record.

For example, each time a new customer is added to the customer table in Oracle DB, we need to communicate the event to other applications in the ecosystem using Apache Kafka.

The DB poller service provides a solution to this scenario by querying the Customer table every 10 minutes and returning the result set to the method flow. The method flow engine maps each result-set record to a Kafka message and sends it to Kafka for further processing.

Step 1. Set a DB poller project

First, create the DB Poller project and set the database connection details. DB Poller can connect to several databases, such as Oracle, DB400, DB2 - Z/OS, and MSSQL.

Create a new project.
Display the Contracts tab.
Create a bottom up contract.
On the contract header, click and select DB Poller.
Click on the contract header and select Configure DB Poller:
Set the properties that configure the connection to the polled database.
- DB Type: the database type, for example, Oracle.
- DB URL: the database URL, for example, jdbc:oracle:thin:@solutions.openlegacy.com:1521/ORADOC.OPENLEGACY
- Username: the database username, for example, admin0
- Password: the database password, for example, P@ssw0rd

Step 2. Set the DB polling mechanism

After you select a database and enter its connection details, set the polling frequency and provide the queries used for reading and writing to and from the database.

Create a new method under the contract you have just created.
On the new method page, Click adjacent to the new method name and then select Configure DB Poller. The Configure DB Poller dialog appears:
In the Cron Expression field, set the execution frequency of the poller query. For example, to poll the database every 10 minutes, enter: */10 ****
In the Poller Query field, enter the SQL query that polls the database. For example, to poll the customer table for all customers whose isNew property is true enter:
```
SELECT TOP 1000 * FROM Customer WHERE isNew = 'yes'
```
In the first few executions, the query returns a result set consisting of 1000 records per execution until all the 'yes' table rows have been selected. In later executions, the query returns only data records that were added to the table since the previous execution.
The application sends the result set rows to the method flow. Each row is fed in sequence to the flow input step, which is preconfigured to include fields with the same name as the row-column names. The data is then processed by all the flow steps until it reaches the final output step.
Optional: In the Row Processing Query field, enter an SQL statement which is executed each time a result set row completes the method flow run.
For example, after the method flow has processed a row, we would like to toggle the isNew property to No in the relevant Customer table row. To achieve this, enter the following SQL statement in Row Processing Query.
```
UPDATE Customer SET isNew='No' WHERE customerID  = ?
```
The ? placeholder represents values passed back from the method flow output step fields. The output field name has to be identical to the query's table-column name.
Optional: In the End Processing Query field, enter an SQL query which is executed after all the result set rows completed the method flow.
For example, we might prefer to set the isNew property to No for all rows returned by the last execution using only one SQL statement. The query in this use case would be:
```
UPDATE Customer SET isNew='No' WHERE customerID  = ?
```
In this use case, the Row Processing Query field remains empty, and the query in the End Processing Query field is doing all the work in one run.

Step 3. Create the row processing method flow

The next step after setting the DB Poller definitions is the creation of the method flow that processes the rows returned by the poller query.

Set the Input step fields to include fields with identical names to the poller query result set field names.
Create a module based on the database connector with an asset based on the poller query. Then copy the asset fields to the method flow input step.
For example, create a module from the Oracle DB connector and add an asset to it with the poller query as the value of the asset --query property. Then add the module to the project and copy the asset fields to the input step.
Add the remaining steps to the method flow according to your use case.
For example, take the scenario in which the contents of a new customer database record is sent through Kafka to another endpoint for further processing. To implement this scenario add a Kafka Invoke asset step to the method flow and map its fields to the input step fields.
Add the remaining steps to the method flow according to your use case.

Step 4. Generate the DB poller service

Generate the DB poller service with the spring-java-db-poller generator to create a low-code Java application.