Xi JDBC Adapter - Query SQL Statement & Update SQL Statement

[Former Member]

Hi!

I configure the JDBC adapter sender (XI) to take data from Oracle database.

I set the Query and Update SQL Statement in the Processing parameters of the communication channel in this way:

Query SQL Statement :

SELECT * FROM XI_TABLE WHERE STATUS = 'WAIT' ORDER BY ROW_NUM

Update SQL Statement :

UPDATE XI_TABLE SET STATUS = 'DONE', DATE = SYSDATE WHERE STATUS = 'WAIT'

My question is :

If a new record with the field STATUS = 'WAIT' is added to the table (xi_table) during the time between the execution of the query statement and the start of the update statement, what will happen to that record during the update?

There is a way to avoid the update of that record? or to pass to the update statement only the record selected in the query statement?

Please, may you give me some example?

Thanks,

Francesco

[MichalKrawczyk · Accepted solution]

hi,

did you check "Isolation Level for Transaction"

for the sender jdbc adapter?

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards,

michal

[Former Member · Accepted solution]

hi,

This means that Query and Update statement are parts of a unique db transaction?

Thanks,

Francesco

[bhavesh_kantilal · Accepted solution]

Hi,

Yes, if you maintain the correct transaction level in the sender jdbc adapter.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi,

may you please tell me where to set the isolation level too.

Tanks very much,

Francesco

[MichalKrawczyk · Accepted solution]

hi,

in the sender JDBC adapter (communication channel)

Regards,

michal

[bhavesh_kantilal]

Hi,

Adding to michal, setting the Isoloation Level For DB transaction to repeatable read and serializable will solve the issue.

For better understanding takea look at this blog,

/people/yining.mao/blog/2006/09/13/tips-and-tutorial-for-sender-jdbc-adapter

Regards,

Bhavesh