Delete Operation in JDBC Sender Adapter not works

[jegathees_waran]

Hi,

I have one student table which contains the fields ID,Name,BirthMonth,BirthYear,ReadFlag fields. ReadFlag is a character field or lenght 1 which contains only values either 'Y' or ' '.

I want to execute delete operation in this table ie to delete the records which contains the readflag = 'Y'. So, I set the below values for the following parameters.

DELETE FROM student WHERE READFLAG = ' Y '

Query SQL Statement : SELECT * FROM student WHERE readflag = 'Y'

Update SQL Statement : DELETE FROM student WHERE READFLAG = 'Y'

Poll Interval : 60 Seconds.

There are more records in this table which contains readflag = 'Y'. But, the Adapter does not delete those records from the table i.e delete operation is not executed. At the same time, in Comm. Channel monitoring it does not show any error, but the delete operation is not carried out in the table.

I tried after 'COMMIT' the table also. But it does not work. What could be the reason ? or How to use Delete Operation effectively on the table ?

Kindly help me friends to solve this problem.

Thanking you.

Kind Regards,

Jeg.

[Shabarish_Nair · Accepted solution]

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

<i>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).

<b>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.</b></i>

try with repeatable_read or serializable !!!

Also go thru this thread -

[jegathees_waran]

Hi Bhavesh,

I am not able to find the exact reason why the DELETE operation is not working earlier. Column name need not be case sensitive in the SELECT and DELETE operations. I did different tests. It works fine now.

Regards,

Jegatheeswaran P

[Former Member]

Hi,

In JDBC , we need to maintain the case also, it is on AE.

But in Database like oracle/sql you need not to maitain the case for field or stetement..

Regards

Chilla..

[jegathees_waran]

Hi,

Now the problem is solved. Thannks to all to reply my question.

Regards,

Jegatheeswaran P

[bhavesh_kantilal]

Hi,

What exactly was this issue?

Regards

Bhavesh

[prabhu_s2]

y dont u try by changing READFLAG by readflag

[jegathees_waran]

Friends, expecting your help about 'DELETE' operation in JDBC sender adapter ....

Regards,

Jegatheeswaran P

[bhavesh_kantilal]

Column names can be case sensitive. Make sure that the same column name you are using in the Select Stament ( which is working fine ) are used in the Delete as well.

Also look into the Log of your Database. Maybe there is some entry on why the delete is not happening.

Regards

Bhavesh

[prabhu_s2]

<b>'DELETE' operation in JDBC sender adapter </b>

are u using it in the update tab of sender...may be check for the flags u had set or for the data...would have failed becoz of case sensitive issues. alos are the condition satisfied for deletion?

[jegathees_waran]

Hi Bhavesh,

I set already isolation level = 'serializable' in JDBC sender adpater. Then, the user which is mentioned in adapter is having rights to delete the record in table student. We can delete records in the table by pass command in the SQL prompt. Whereas if we specify the same operation is JDBC adapter (Update SQL statement), it is not carried out. that is my doubt. Whether my thinking is correct or not ? (or else could you kindly explain how the DELETE opeartion will work in Sender side JDBC adapter)

Kindly clarify friends.

Thank you.

Regards,

Jegatheeswaran

[bhavesh_kantilal]

Hi,

What is the Transaction level in the Sender JDBC adapter?

Go to the Adavanced Mode in your Sender JDBC adapter and then select the <i>Isolation Level for Transaction</i> and set the value to either <b>Serializable or Repeatable Read.</b>

Also make sure that the User id and password in your sender JDBC adapter has the Delete authroizations on the table.

Regards

Bhavesh

[Former Member]

HI,

If an exception occurs in XI 3.0 the JDBC adapter doesn't actually issue a ROLLBACK. It just closes the connection. The problem is that Oracle will then COMMIT this since the connection was explicitly closed (as opposed to dropped) even if Auto-commit is turned off at the DB level.

SAP has let us know that they will fix this by including the ROLLBACK. It will be included in SP11

Refer SAP FAQ- 831162 Question no 8.

Also refer this-

http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

Regards

Chilla..

[Former Member]

Hi Jeg,

Am not sure, if you can execute a delete operation from UPDATE field in Sender JDBC adapter.

Try this alternative.

Use Update and change the value of READFLAG variable to some other value say X.

In databse, you can create some SP to delete all records with READFLAG value set to X.

Regards,

Jai Shankar