DELETE Querey in JDBC SENDER

[Former Member]

Hello all,

I am trying the JDBC to file Scenario. There are 5 records in a table, i can see the records in the MONI being successfully picked by the sender jdbc adapter, after being picked up i want those records to be deleted. So i write a DELETE statement in the UPDATE SQL Querey but i cannot see those records deleted from the database. i think i need to specify some COMMIT which i am not sure of, can you please provide me your suggestions for deleting records from database.

Thanks in advance

[bhavesh_kantilal]

Arathi,

Like mentioned by moorthy, keep the transaction isolation level to SERIALIZABLE or REPEATEABLE READ and it will get deleted as a single transaction and make sure that the where clause is correct.

Regards,

Bhavesh

[Former Member]

Hello,

Thanks for your inputs....i tried setting the isolation level to : repeatable_read/serializable but, i guess i might have problem in my SQL sts:

my table is very simple with 5 records and 2 columns

TABLE_1

ID SC_NUM

1 aa

2 bb

3 cc

4 dd

my squery sql st is:

SELECT ID,SCHED_NUM from JHI_LOAD_DELTA

my update st is:

UPDATE ID,SCHED_NUM from JHI_LOAD_DELTA

from your inputs, i find that i have to use a where clause, i am not sure...can you correct me

Thanks

[moorthy]

Hi,

You need to delete the entries right? Then I am not getting about this entry <i>UPDATE ID,SCHED_NUM from JHI_LOAD_DELTA</i>

You need to give DELETE from <table name> WHERE <condition>

I am not sure about syntax here. So try without Where Clause now.

Regards,

Morothy

[Former Member]

hello ,

I am sorry actually i did give the update querey as :

DELETE ID,SCHED_NUM from JHI_LOAD_DELTA

with the isolaton level set to: repetable_read & Serializable and with the auto-commit enabled...still no luck

[moorthy]

Hope your SQL syntax is correct.

And just try to give DELETE <entire table data> . Not fieldwise.( probably DELETE from <Tablename>)

Still same problem, then Try with Where COndition also.

_Moorthy

[Former Member]

Hi krishnamoorthy,

Thanks for your inputs. i just re-did the whole adapter settings and it finally worked.

Thanks again

[moorthy]

Hi Aarthi,

Glad to know..

Please close the thread--

Regards,

Moorthy

[Former Member]

Hi Aarthi,

Let me know steps to delete the records from JDBC Sender Adapter,Could plz send me in detail.

Thanks in adance

pullarao

[sabyasachi_mohapatra3]

Hi Aarthi,

Let me know steps to delete the records from JDBC Sender Adapter,Could plz send me in detail.

Thanks in adance

[Former Member]

Hi,

This is the Delete query

Exp:

USE mydatabase;

DELETE

FROM orders

WHERE customer = 'A+Maintenance';

Regards,

Ravi.

[Former Member]

Hi,

This is the Delete query

Exp:

USE mydatabase;

DELETE

FROM orders

WHERE customer = 'A+Maintenance';

Regards,

Ravi.

[moorthy]

Hi,

Refer SAP FAQ- 831162 Question no 8.

Also refer this-

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

Regards,

Moorthy

[Former Member]

Krishnamoorthy,

Where do i find that SAP FAQ?

Also, i had ben to help and from my understanding i thought that i have to set the advanced parameters,

by setting the transaction isolation level to : read committed and enabled the database auto-committ, but did not work.

Any other suggestions pls.

[moorthy]

SAP Note if from SAP Service Market place. The link is

https://websmp103.sap-ag.de/notes

And the content of this SAP note is -

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

Did you check this -

<i>Processing can only be performed correctly when the Transaction Isolation Level is set to repeatable_read or serializable.</i>

Regards,

Moorthy