JDBC Sender Update on primary key

[ChRa]

Hi,

i have a JDBC Sender to XI-Message scenario.

By default JDBC Sender uses a Query Statement and an Update Statement to process data.

see the SAP example:

SELECT * FROM table WHERE processed = 0;

UPDATE table SET processed = 1 WHERE processed = 0;

In my Scenario I want to update each dataset by it's own primary key.

this means

SELECT * FROM table WHERE processed = 0;

the result is a dataset of 5 rows.

now i want to call 5 update commands.

UPDATE table SET processed = 1 WHERE primarykey = row[1].primarykey;

UPDATE table SET processed = 1 WHERE primarykey = row[2].primarykey;

UPDATE table SET processed = 1 WHERE primarykey = row[3].primarykey;

UPDATE table SET processed = 1 WHERE primarykey = row[4].primarykey;

UPDATE table SET processed = 1 WHERE primarykey = row[5].primarykey;

is there any possibility to do that in die JDBC-Sender adapter?

if the possibility is a module, how can i access the JDBC-Sender channel to get a jdbc connection?

thanks a lot.

[turmoll · Accepted solution]

Have you considered to write stored procedure? Not sure which DB you use since there are some constraints. Look at:

Regards,

Jakub

[ChRa]

thanks for your replies.

we agreed on using a stored procedure.

Thanks

[iaki_vila]

Hi all,

If you take in the database the fields that determine the primary key, and following Jakub idea about to work at mapping level, this blog gives you an idea how to work with a DB:

Regards.

[turmoll]

and it should not be difficult, simply you need to select primary key fields in your select statement.

[ChRa]

Hi,

the solution from Jakub seems to be a gooe one.

In my special case i have no access to the database to create a stored procedure.

The poll interval is 10 seconds.

I think i need a PO only solution to solve the problem.

are there any other suggestions?

Regards

[turmoll]

Can you pass/will you know the "primary key" at mapping level?

If so can you try this:

Implementing Lookups Using DataBaseAccessor - Managing Services in the Enterprise Services Repositor...

I'm not sure but you might give a try with aforementioned approach in custom adapter module.

/Jakub

[iaki_vila]

Hi Christian,

I think Jakub suggestion is the best in these cases. Another alternative, if you have in your scenario a SAP ABAP system involved is to do the control from a job with an ABAP report, doing two PI

scenarios one to do the SELECT and the second to do the UPDATE.

Regards.