JDBC sender - Processing

[Former Member]

Hi All,

For JDBC sender, query is SELECT * FROM CERT.CERTIFICATION WHERE ROW_STATUS IN ('D', 'I', 'U') AND ROWNUM = 1.

And UpdateSQL Statement has CALL CERTIFICATION.SAP_CLEANUP.CLEANUP().

The update SQL statement is for calling a procedure "CERTIFICATION.SAP_CLEANUP.CLEANUP()" in Oracle side for setting the ROW_STATUS back to initial flag which is 'A' means should not pick by PI JDBC adapter.

So, whenever a record is selected from the certification table, we have to update the flag row_status back to 'A'.

Here, we are facing an issue. Sometimes, the call procedure statement is updating some other row and that record will not be processed by PI. It's very difficult to debug and find the solution as it happens only sometimes.

Is there any other way to update the row_status of the record which got picked up by PI.

Appreciate!!!

-Mike

[Former Member · Accepted solution]

Mike,

Use this UPDATE statement -

UPDATE CERT.CERTIFICATION  SET ROW_STATUS = 'A'  WHERE ROW_STATUS IN ('D', 'I', 'U') and  ROWNUM >= (select min(ROWNUM) from CERT.CERTIFICATION where ROW_STATUS IN ('D', 'I', 'U')) and   ROWNUM <= (select min(ROWNUM) from CERT.CERTIFICATION where ROW_STATUS IN ('D', 'I', 'U'))

This will make sure that only those records, which has been picked, will be flagged.

Hope this helps.

Regards,

Neetesh

[Former Member · Accepted solution]

Thanks guys for all help. Appreciate. Forum rocks always

-Mike

[former_member185881]

Hi

In JDBC Sender Communication Channel use value serializable under Advanced tab --> Additional Parameters --> Transaction Isolation Level = serializable

In your SQL Sender query whatever where clause you are using, use the same where clause in your Update query.

Regards

Dheeraj Kumar

[Former Member]

What is this serializable option about?/?

Anyways, thanks guys for all the replies

-Mike

[former_member185881]

Hi

Serializable means if your XI is connected to the database to that particular table then any other appication will not be able to connect it and cant modify anything in that table, untill and unless your XI realeases a connection.

For more information search SDN help for this.

Regards

Dheeraj Kumar

[Former Member]

Hi,

Put Order By clause in Select & Update queries. Please ensure that same record is identified so you can point to oldest record using Order By clause. This will solve ur problem.

Regards

Sushil

[Former Member]

Hi,

Use the same query for the update also as used in selection process...

so that it will ensure the updation to the records selected only...

provided you are not doing any other functionality in the SP used...

HTH

Rajesh

[Former Member]

HI

Try using the below query in Update sql statement

Update CERT.CERTIFICATION set ROW_STATUS = 'A' where ID in (SELECT ID FROM CERT.CERTIFICATION WHERE ROW_STATUS IN ('D', 'I', 'U') AND ROWNUM = 1)

Here ID -- Primary key

Regards

Ramg