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

[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