Update query not working in the JDBC sender Communication channel

[Former Member]

Hi,

We are working on JDBC to File scenario. As per the configuration, XI should pick the data from SQL database every 20 secs and should update the corresponding flag. We are using subquery in the select and update statement as both header and detail tables are involved.

Now the issue is, select query is working fine but update statement is not working as expected. It is somehow updating some other records rather than doing for the ones selected by the adapter.

Moreover logSQLstatement is also not working. Hence we are unable to identify the records which are getting updated.

Please advise.

[Former Member]

Hi Rumi,

Try using the below statements in jdbc adapter configuration,

SQL : Select <<Var>> from Tablename where flag='1'

UPdate: Update tablename set flag='0' where flag='1'

When the flag status is 0 then the rows are posted to PI.

regards,

Divya

[Former Member]

Hi,

Check the isolation levels selected at the adapter properties...

depending of the option you selected the results may vary...

HTH

Rajesh

[pedro_baroni3]

Hi Rumi,

See Question 8. Transaction Handling (Sender) in [SAP Note 831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 JDBC Adapter|https://websmp130.sap-ag.de/sap(bD1wdCZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=831162].

8.  Transaction Handling (Sender)

Q: If I have the following configured in a JDBC Sender:

Select Query:
SELECT column FROM TABLENAME WHERE FLAG = "TRUE"

Update Query:
UPDATE TABLENAME SET FLAG = "FALSE" WHERE FLAG = "TRUE"

How do I know that the JDBC adapter will not update newly added rows (rows that were 
added between the time that the SELECT and UPDATE queries were executed) that were
not read in the initial SELECT query?

A: The SELECT and the UPDATE are run in the same DB transaction, i.e. both statements 
have the same view on the database.

Make sure that both statements use the same WHERE clause. An additional 
requirement for the correct operation of this scenario is the configuration of 
an appropriate transaction isolation level on the database 
(i.e., repeatable_read or serializable). You might also consider using a 
"SELECT FOR UPDATE" statement instead of a plain SELECT statement to 
ensure proper locking on the database. "SELECT FOR UPDATE" 
is not supported in MS SQL database. In this case please make use of an 
appropriate transaction isolation level on the database. For more details 
please contact your DB vendors.

After, see Transaction Handling Issues in [SAP Note 1039779 - JDBC Adapter issues(Escape character,Transaction handling)|https://websmp130.sap-ag.de/sap(bD1wdCZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1039779].

Best Regards.

Pedro Baroni

[former_member854360]

sender channel what update query are you providing?