JDBC Sender adapter - Select followed by Delete

[Shabarish_Nair]

Folks,

Need your thoughts !!

We have a scenario where we are reading data from a DB (Oracle 8i). After a select 'ALL', we are deleting all the records (that were selected)

My question is;

1. After we select all the records, suppose another record is inserted to the table, will the delete statement delete the new record also?

2. How can it be ensured that only the selected records (at that instance) are deleted and no new records (that ahve not yet been selected) remain intact?

I am looking at the usage of the advanced parameters repeatable_read or serializable option but would like to know the implications.

Thanks,

Shabz

[former_member200962 · Accepted solution]

After a select 'ALL', we are deleting all the records (that were selected)

May be you can associate some indicator (flag) with the records, and then do the SELECT/ DELETE.....delete all may wipe out even the new records

[Shabarish_Nair · Accepted solution]

thanks Abhishek.

I am trying to find out if there is any other way other than to use a FLAG option.

Want a confirmation if i dont use a where clause would it work

[Former Member · Accepted solution]

Would you consider calling a stored procedure instead of select statement? Within the sp you can control "atomocity" of select followed by delete/update.

[Shabarish_Nair · Accepted solution]

>

> Would you consider calling a stored procedure instead of select statement? Within the sp you can control "atomocity" of select followed by delete/update.

Sam,

I would love to. But our DB version (Oracle 8i) just wouldn't support a SP call (sender JDBC)

[Former Member · Accepted solution]

How about stored function then? Idea would be to get it to PL/SQL.. there you can ask the db guy to ensure atomocity in his code.

[Former Member · Accepted solution]

Oracle 8i does not support an SP call (sender JDBC) ?! Are you sure, did you try ? Sounds strange.

And one other thing, a polling interval of 1 second is not good. Can easily put too much load on the DB. Better use 5 or 10 seconds. If you have very high performance requirements (1 sec polling), then consider to let the other application send the data actively to XI when certain triggers are recognized.

As a general note, connecting systems directly by database access should be avoided because of the tight coupling. Too many things can go wrong, see your discussion about danger of deleting wrong records, or JDBC driver specifics, transaction problems, and more...

CSY

[Shabarish_Nair · Accepted solution]

>

> Oracle 8i does not support an SP call (sender JDBC) ?! Are you sure, did you try ? Sounds strange.

it doesnt support SP calls when using the sender JDBC adapter

@All,

so this is what is the end to the story.

We will need to use a flag to ensure transaction handling. So the design as to be based as that is documented in the SAP help doc or the SOAP FAQ note.

Along with the flag and using update statement enabling serializable option should solve the issue.

[former_member181985]

Hi Shab,

Assume that your requirement at transactional isolation level is not possible,

I am just thinking of the following solution:

Use an Indicator in the table.

In JDBC Sender

SELECT * FROM TABLE with where clause for indicator

UPDATE result set for the indicatior

Now the selected message will be in XI. Apart from your intended Receiver application as per your configuration, create one more receiver for the same DataBase issuing a DELETE statement using the standard document formats of JDBC receiver with where clause.

Regards,

Praveen Gujjeti.

Edited by: Praveen Gujjeti on Feb 23, 2010 12:15 AM

[former_member181985]

Shab,

Even we dont require a flag in the table.

In sender JDBC Channel Conf

SELECT Query with where CLAUSE......

<TEST> for UPDATE

Now the message will be in XI. Apart from the actual receiver, we can make delete statements with where clause using the selected fields from JDBC Sender ResultSet. Now pass this message to JDBC receiver.

This will ensure that only selected records will be deleted.

Regards,

Praveen Gujjeti.

[Shabarish_Nair]

I cant afford to pass the message to the receiver JDBC adapter since my polling interval at the sender side is 1 sec ( as per the requirement)

As an update,

we are trying the following now;

1. As per the SAP help - using a flag field and where statements after enabling Serializable mode

2. Trying to also test, a select followed by delete with the transaction handling enabled.

the legacy team is trying to help create a perfect test instance (with a 1 sec polling you know it can get a bit tricky)

will update this post soon

[former_member181985]

Eagerly waiting for your most efficient solution............

[HarshC]

Hi Shabz,

This would be taken care of by selecting the right level of transaction handling.

One thing that you need to check for is, whether the DBA guy has enabled transaction handling for remote or "service users".

At one of the client places, we faced this issue, because background users did not support transactions, therefore each step(select, update) was being treated as separate transactions by the DB. The only option left was to use a receiver channel to update the "selected" rows.

Hope this helps,

Harsh

[Shabarish_Nair]

>

> Hi Shabz,
> 
> This would be taken care of by selecting the right level of transaction handling.
> One thing that you need to check for is, whether the DBA guy has enabled transaction handling for remote or "service users".
> 
> At one of the client places, we faced this issue, because background users did not support transactions, therefore each step(select, update) was being treated as separate transactions by the DB. The only option left was to use a receiver channel to update the "selected" rows.
> 
> Hope this helps,
> Harsh

harsh,

I guess the transaction handling works on Select and Update. Will it work on Delete also?

(ideally it should isnt it?)

i.e

Assume transaction handling is set to serializable;

my statement are

select * from table;

delete from table;

[HarshC]

Hi Shabz,

It should work on delete as well.

Backup 1: Just to be doubly sure, can you add a dummy where clause that is same as selecting all rows.

Let's say you select all rows where date is in this century(assume same as select all).

Followed by delete with same where clause.

Even if 100 new rows were added between select & delete, delete should be treated as part of the select transaction, and skip the new 100 rows.

Backup 2:

1) add a flag column, use update instead of delete.

2) run a job to delete flagged rows.

Hope this helps,

Harsh