Question Re: Incorrect sequence records reading from database in Technology Q&A

Incorrect sequence records reading from database

Former Member — Tue, 12 Nov 2013 12:19:29 GMT

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

Re: Incorrect sequence records reading from database

gagandeep_batra — Tue, 12 Nov 2013 12:39:27 GMT

Hi Sai,

Did you try with:

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

Regards

Gagan

Re: Incorrect sequence records reading from database

Former Member — Tue, 12 Nov 2013 12:41:27 GMT

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-query-to-limit-our-records-to-be-picked-up-by-xi-from-database-to-avoid-huge-message-processing-failed

Regards.

Message was edited by: Iñaki Vila

Re: Incorrect sequence records reading from database

Former Member — Tue, 12 Nov 2013 12:47:56 GMT

Dear Gagan,

I tried that option, but no use.

Thanks,

--Sai

Re: Incorrect sequence records reading from database

gagandeep_batra — Tue, 12 Nov 2013 12:51:43 GMT

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

Re: Incorrect sequence records reading from database

Former Member — Tue, 12 Nov 2013 12:52:14 GMT

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.

Re: Incorrect sequence records reading from database

Former Member — Tue, 12 Nov 2013 13:36:51 GMT

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

Re: Incorrect sequence records reading from database

ambrish_mishra — Tue, 12 Nov 2013 15:15:26 GMT

Hi Sai,

Please check my response in thread below:

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

Ambrish

Re: Incorrect sequence records reading from database

Former Member — Wed, 13 Nov 2013 05:59:11 GMT

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.

Re: Incorrect sequence records reading from database

Former Member — Fri, 15 Nov 2013 10:28:35 GMT

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

Re: Incorrect sequence records reading from database

Former Member — Fri, 15 Nov 2013 10:36:51 GMT

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.

Re: Incorrect sequence records reading from database

Former Member — Fri, 15 Nov 2013 11:31:26 GMT

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

Re: Incorrect sequence records reading from database

Former Member — Fri, 15 Nov 2013 11:37:47 GMT

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

Re: Incorrect sequence records reading from database

Former Member — Fri, 15 Nov 2013 12:19:56 GMT

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

Re: Incorrect sequence records reading from database

ambrish_mishra — Fri, 15 Nov 2013 12:52:12 GMT

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

Re: Incorrect sequence records reading from database

Former Member — Mon, 18 Nov 2013 04:51:09 GMT

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

Re: Incorrect sequence records reading from database

ambrish_mishra — Mon, 18 Nov 2013 06:59:51 GMT

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?

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

Re: Incorrect sequence records reading from database

Former Member — Mon, 18 Nov 2013 12:44:47 GMT

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

Re: Incorrect sequence records reading from database

ambrish_mishra — Mon, 18 Nov 2013 13:18:30 GMT

Hi Sai,

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

Ambrish

Re: Incorrect sequence records reading from database

Former Member — Thu, 12 Dec 2013 08:05:04 GMT

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