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_member192343]

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)

[ambrish_mishra]

Hi Sai,

Please check my response in thread below:

http://scn.sap.com/thread/3381809

Ambrish

[Former Member]

Ambrish,

Still I am facing the same issue.

Eg: I am reading one set of data and updating another set of data

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

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

In database while reading records using select I have 3 records but during update another 3 records were inserted at realtime for update it fetches 6 and updating its flag.

So, I want to know is there any way to lock the number of records which is selected in SELECT query for UPDATE.

Your inputs are well required here

Thanks,

--Sai

[Former Member]

Hi Sai

Do this two things and give a try.

1. set Transaction Isolation Level to repeatable_read.

2. disconnect from data base after processing each message

[ambrish_mishra]

Hi Sai,

Did you refer to the thread I forwarded to you.

If that does not help, then I can share a Stored procedure way which does what you are mentioning. Locking the number of records selected. Are you open to a S P solution ?

Ambrish

[Former Member]

Dear Ambrish,

Yes I am open to Stored Procedures.

My ultimate goal is to fix this issue.

I just want to know is there any dependencies in using Stored Procedures like

1. Do I need to install any patch or update with in PI server?

2. Where do I need to put my stored procedure code, by default I will be developing in my source database and will check with its running.

3. I see CALL <StoredProcedure> as syntax while executing in SQL, but is the same statement do I need to define in QUREY SQL of jdbc adapter.

Need your valuable inputs here.

Thanks,

--Sai

[ambrish_mishra]

Hi Sai,

Did you refer to the thread I forwarded to you.

Does it work for you.

My response to your questions:

1. Do I need to install any patch or update with in PI server?

No

2. Where do I need to put my stored procedure code, by default I will be developing in my source database and will check with its running.

The stored procedure will reside in the database and will be executed in DB when invoked from PI.

3. I see CALL <StoredProcedure> as syntax while executing in SQL, but is the same statement do I need to define in QUREY SQL of jdbc adapter.

You will just call stored procedure from PI adapter and that's it. It will return the number of rows the same way like a select. I will check the stored procedure if it is suitable to your requirement.

Ambrish

[Former Member]

Hi Ambrish,

I have my stored procedure in my hand right now,

I tested this in my SQL server its working fine as expected.

But while calling from PI its throwing error.

QUERY SQL Statement: CALL SP_UPDATEFLAG;

Here SP_UPDATEFLAG is my stored procedure name.

Can you tell me how to call stored procedure in my JDBC Adapter.

Thanks,

-Sai

[ambrish_mishra]

Hi Sai,

In SQL server, the syntax is EXECUTE dbo.<S P NAME>

Ambrish

[Former Member]

Hi Ambrish,

Sorry to trouble you on this..

My Source Database is SQL Server 2008,

When i try to give my stored procedure as EXECUTE dbo.SP_UPDATEFLAG;

its throwing error in communicatio channel as invalid execute command.

Can you throw some light on how to call this stored procedure.

Thanks,

--Sai

[ambrish_mishra]

Sure I can...

EXECUTE dbo.SP_UPDATEFLAG;

remove the semi-colon. If that does not work, remove dbo.

Hope it works!

Ambrish

[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_member184720]

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

[iaki_vila]

Hi Sai,

Go ahead with Gagandeep Batra's advice, a little tip:

Check Dheeraj Kumar's blog http://scn.sap.com/people/dheeraj.kumar5/blog/2010/04/12/pixi-sender-jdbc-select-query-and-update-qu...

Regards.

Message was edited by: Iñaki Vila

[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.