JDBC Sender Adapter.

[lnirmala]

Hi all,

My doubt is regarding the JDBC sender adapter.

Inorder to prevent JDBC adapter from reading the same records from database there is an option to maintain a flag in the data base table.Once the record is read this flag can be updated and so next time when the JDBC adapter polls the database table the same record wont be read.

But in my scenario there is not flag field at the client database and the maintenance of this additional field in client database is strictly prohibited.

Could someone provide me with another way to implement data integrity so that records wont be read more than once.

Thanks,

Lekshmi.

[lnirmala]

Hi Unni,

As mentioned in you reply:

Have a table (row_ref) which has just one column say rownumber assume initial value as 0

select query

*select * from your_table where rownum > (select rownumber from row_ref)*

update query

update row_ref set rownumber = (select max rownumber - 1 from table your_table)

Suppose i am creating a new table row_ref where i am keeping track of the number of records in orginal database table.

Take for example while executing select statement if there are multiple record inserts are happening in database table and then update statement is being executed.

What will be the result of

select max rownumber - 1 from table your_table

It will return maximum rownumber from the database table which may include many records which were not read by PI adapter.

So next time when JDBC adapter is reading records will it miss those records inserted at the same time when it was processig the previous record?

Please correct me if i am wrong.

Thanks,

Lekshmi.

[Former Member]

Hi Lekshmi,

You are correct, there is a possibility of losing data unless you use the update with a dynamic variable. Please see the following thread where they have used BPM to update with a dynamic variable.

[|]

However, you should not be complicating the scenario this way, instead move the same logic to a stored procedure and execute.

So in your query instead of selecting * from your_table, use

select required_field, rownum from your table where rownum >= (select rownumber from rowref)

Before your return result set update the max rownum to the rowref table from the above cursor. You are pretty safe from losing data.

You can also check this weblog where you can apply same principle and execute an update that will not update anything. Key is that the rownum must be updated to row_ref table. Again Oracle 9i +...

[https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/5984] [original link is broken] [original link is broken] [original link is broken];

Hope it helps.

Unni

[Former Member]

Hi Lekshmi,

For stored Procedures these are few helpful area:

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

Regards,

[Former Member]

Lekshmi,

You can adopt this approach :-

1. Create a staging table exactly similar to your table's schema plus a flag field.

2. Instead of SQL queries use stored procedure approach . Inside stored procedure not only read the client table and pass the values to XI . In addition to this insert the same records with "Read" flag in your staging table .

3. For next polling read the staging table first and than clients table and pass only those records which is not having entry in staging table

Adv : 1. Using staging table with flag will help you control the interface . Like sending the data again by changing the flag value at staging table level.

Major disadv : Data redundancy, Heavy load on your database

Once your staging table will grow , the interface will become slower with time .

Regards,

[lnirmala]

Hi all,

Thanks for your replies.

As mentioned there are some clients reluctant to make any change at their side.So i was just checking whether i can find out a solution as i once heard that we can make some changes from our side in RWB for JDBC sender adater.

So was just checking out whether there is any possibility like that.

Thanks,

Lekshmi.

[Former Member]

Lekshmi,

Forget about XI !!!

My question to your client is how they would have catered same situation at their end.? Means if they need to read the new records from a table on a timely basis without modifying the present structure . I would love know their approach !!

If something needs to be done at either end, client side or at service provider side, for the solution it should be in place . I came across few clients specifically Indian clients from public sector very reluctant to make any changes at their side.

For this type of client we put our requirement very firmly and with proper justification .

Will suggest you the same.

Regards,

[lnirmala]

Hi all,

Other than flag field and stored procedure is there any setting which can be done in PI side i.e.some configurations ,which will prevent the JDBC adapter from reading the already processed records.

Thanks,

Lekshmi.

[prateek]

Setting in PI cannot ensure picking of records from database. Something has to be done at database side.

Another thing could be use of a intermediate table, which your database team populates and PI picks up data from that table. This intermediate table can then have additional field for update.

Regards,

Prateek

[Former Member]

hi,

I think, (this is poor approach and I am not sure it will workout also) you can ask them to put into to a temporary file in their DB (whenever new records are inserted to that table) and control the JDBC sender channel whenever they insert the records to that temp file by using external control (Or if they provide a field to you then happily you can use it).

Or

I think their will be some field empty in the database table, ask them to use that for your updating purpose.

Regards,

Venu V

[Former Member]

Hi,

I have 2 approach

1. Have a shadow table with additional column in the new table. Put a a trigger on the actual table that would insert in to your shadow table. Read the shadow table using XI. (Not sure if your client is ok putting a trigger on this table, since they already rejected a column, try your luck)

Is your database Oracle? If yes, read thru.

2. Have a table (row_ref) which has just one column say rownumber assume initial value as 0

select query

select * from your_table where rownum > (select rownumber from row_ref)

update query

update row_ref set rownumber = (select max rownumber - 1 from table your_table)

I am assuming if the client is ok coming with a new table. Please note the option 2 will work only if the database is Oracle.

Regards

Unni

[prateek]

This is strange. There has to be some parameter in database based on which you may differentiate new records from other. If you think your database team could provide you a query to fetch such record, then you may ask for it or ask for a Stored procedure doing this.

Regards,

Prateek

[former_member192295]

Hi,

Flag field is easiest way to prevent duplicate records, if this is not possible ask you db admin to give data in flat file and read that file by using file adapter. Refer below link for more help

/people/sandeep.jaiswal/blog/2008/05/13/adapter-module-to-stop-processing-of-duplicate-file-ftp-location