on 02-27-2007 9:38 AM
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.
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 !!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Now the problem is solved. Thannks to all to reply my question.
Regards,
Jegatheeswaran P
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
y dont u try by changing READFLAG by readflag
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Friends, expecting your help about 'DELETE' operation in JDBC sender adapter ....
Regards,
Jegatheeswaran P
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.