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

[gagandeep_batra]

Hi Sai,

Did you try with:

under "advanced" tab the parameter "Transaction Isolation level" to serializable.

Regards

Gagan

[Former Member]

Dear Gagan,

I tried that option, but no use.

Thanks,

--Sai

[gagandeep_batra]

Hi Sai,

I think there might be problem with you select command also

put  some orderby clause some thing.. or check another parameter with where clause

[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 and ROWNUM < 3

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