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]

When TOP is used with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. See the Examples section that follows in this topic.
TOP cannot be used in an UPDATE and DELETE statements on partitioned views.
TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope). For more information, see ORDER BY Clause (Transact-SQL).

see also TOP (Transact-SQL)

And I'm not sure if jdbc driver for mssql support T-SQL.

I had the same problem once and I didn't solve it. I changed my sql query to another logic, with subqueries.

If interesting my queries were:

select DATE, TOTALTIME, TICKET_NUMBER, SERVICE_GUID,PERSON_GUID,COMPANY_GUID from SAP_GUID where processed=0 and date=(select min(date) from SAP_GUID where processed=0)

and

update SAP_GUID set processed=1 where processed=0 and date=(select min(date) from SAP_GUID where processed=0)