Incorrect sequence records reading from database

[Former Member]

Dear Experts,

Scenario: JDBC to IDOC.

In my production database system the records are getting inserted into the table when ever employee swipes his card,

the data will come as a new entry into table which is present in Production database TABLE_ATTENDANCE.

I configured JDBC adapter at sender side with following QUERY Statement:

Query SQL Statement: SELECT TOP 2 * from TABLE_ATTENDANCE WHERE FLAG = 0

Update SQL Statement: UPDATE TOP (2) TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0

I am facing issues here as below:

1 FirstRecord

2 SecondRecord

3 ThirdRecord

4 FourthRecord

5 FifthRecord

6 SixthRecord

7 SeventhRecord

8 EightRecord

Intitally My jdbc adapter is polling the table in the database and picks first 2 records 1 &2 then meanwhile few records are getting inserted and while fetching for the next poll interval it fetches 6&7 records, but where as it should fetch 3 & 4.

I want to read the records as if the same sequence in my database.

Expecting your inputs on this.

Thanks,

--Sai

[Former Member]

Hi Sai

Please change your query as below and test.

Query SQL Statement: SELECT  * from TABLE_ATTENDANCE WHERE FLAG = 0 and ROWNUM < 3

Update SQL Statement: UPDATE TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0

This should solve your problem. Let me know if there are any issues.

[Former Member]

I believe the above suggested update statment needs to be corrected as it'll update all the records (not just the first two. )

UPDATE TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0 and ROWNUM < 3

Also it would be good if you can add some order by clause to your query incase you have any fields like "swipe in"/"emp ID" in your table

[Former Member]

Unable to resolve the issues.

Can i have some valuable inputs please.

Still i am missing few records

How ROWNUM is useful here? can you elaborate on this please

Thanks,

--Sai

[Former Member]

Hi Sai

Did u give a try with the query I have mentioned previously??

ROWNUM is the uniq number of every row in the data base. So when you run this query

SELECT  * from TABLE_ATTENDANCE WHERE FLAG = 0 and ROWNUM < 3

you will get the first two row with sequence 1 and 2 from list of rows where the flag is 0.

Next use the below query

UPDATE TABLE_ATTENDANCE SET FLAG = 1 WHERE FLAG = 0 and ROWNUM < 3

so it will set the flag 1 for the last two records that is fetched in the select query.

Please give it a try before you search for other valuable inputs.

[Former Member]

HI Indrajit,

its not working..

the issue here is during select there is only 1 record in the database so the rownum<3 will satisfy and fetch only one record

during update if anther record is inserted then rownum<3 will satisfy and updates both records so this is failing

What is the time differienct it takes to execute SELECT & UPDATE query in JDBC adapter?

Thanks,

--Sai