#792 OIC DB adapter - approaches for processing new rows

                                         

With OIC you have a couple of possibilities to process new DB rows.
I am using a simple table - EMP - for this post, as you can see it has 4 columns -

EMAIL, FNAME, LNAME, ISNEW

Essentially I want to process new EMPs. As you can see, I have already added 2.

Option 1 - DB adapter as Trigger

First option  may be to poll for new/changed emps.
This can be achieved by dropping the DB connection
as a trigger in your OIC integration flow.

As you have already seen, I have used SQLPLUS to create 2 new emps.
These result in the following 2 integration flows -

Now this was very easy to achieve. It is as near to real time as you can get with OIC, so if that is a requirement then this is the way to go.

I did, however, require the ISNEW column with its values 'NEW' and 'PROCESSED'.

We also saw that each new EMP is processed individually, which could result in many OIC integration flows, which, in turn, mean more messages used from your message pack(s).

There is also another approach...

Option 2 - DB adapter as Invoke

Here I have a scheduled job with a dateLastRun parameter.
I will use this to query the EMP table, essentially selecting all emps
with a CREATION_DATE > dateLastRun.

My DB table has an extra column of type TIMESTAMP.

I drop the DB adapter and configure as follows -

Note the #inDateTime parameter above.

The Mapping is as follows -

In this simple example, I just loop over the result, logging the emps.

The final step is to update the dateLastRun schedule parameter.

This took me a bit of time to work out the correct syntax, but here it is -

I add a couple of emps -

I activate and test -

dateLastRun has been updated accordingly -

So what are the benefit of this approach?

Option 2 - Batch oriented, but one can schedule very frequently, e.g. every 10 minutes.
Multiple emps can be processed in 1 integration flow, thus positive from a message pack perspective.

For me, the preferred approach if near real-time processing is not required.