JDBC adapter update statment

[maciej_jarecki]

Hello All,

In my sender jdbc adpter configuration i read data from table that contains filed 'status' witch indicate does record has been send to sap. But limitation of this solution is that i read up to ne row and example query looks like this

select up to 1 rows * from table where status = 0 order by id.

and update query

update table set status = 1 where id = ( select id 1 rows * from table where status = 0 order by id )

Can you write your strategy of reading multiple records.

I know that stored procedure can be solution of this problem, but i'm wondering how do you handle with that.

BR

Maciej

[Shabarish_Nair · Accepted solution]

you can indeed read and update multiple records;

Adapter Work Method

·        You must add an indicator that specifies the processing status of each data record in the adapter (data record processed/data record not processed) to the database table.

·        The UPDATE statement must alter exactly those data records that have been selected by the SELECT statement. You can ensure this is the case by using an identical WHERE clause. (See Processing Parameters, SQL Statement for Query, and SQL Statement for Update below).

·        Processing can only be performed correctly when the isolation level for transaction is set to repeatable_read or serializable.

Example

SQL statement for query: SELECT * FROM table WHERE processed = 0;

SQL statement for update: UPDATE table SET processed = 1 WHERE processed = 0;

processed is the indicator in the database.

the above is from SAP help.

follow the query as mentioned and do not forget to set the repeatable_read or serializable in the adapter

http://help.sap.com/saphelp_nw04/helpdata/EN/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

[maciej_jarecki · Accepted solution]

ok but what happen when between select and update statement new record will be addes to table, we still suffer this problem.

[Shabarish_Nair · Accepted solution]

Processing can only be performed correctly when the isolation level for transaction is set to repeatable_read or serializable

that setting will ensure that there will be no conflict if new records are inserted between the select and update.

[Former Member · Accepted solution]

Hi ,

transaction mode comes into play in that case n u need to select one of the paramters as said above

if used serialisation then it will lock the table during the operations so that only selected records will be updated...

you need to go through the database transactions in details for more/better undestanding

HTH

Rajesh