cancel
Showing results for 
Search instead for 
Did you mean: 

Update in JDBC-Adapter (Send)

frank_schmitt3
Participant
0 Kudos

Hallo,

I´m trying to execute a Update in the JDBC-Adapter (Sender). When a Select-Statement results in exactly one entry back from the data-base I wont to Update this entry.

How refrencing to the enty (for example by an primary key)? I found only documentation for Receive-JDBC-Adapter...

Thanks in advance,

Frank

Accepted Solutions (0)

Answers (5)

Answers (5)

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Frank,

At last I get your scenario..

One solution to your problem can be the following though i am not sure if this is a perfect soultion or a viable solution but, it is a workaround solution which you can surely try.

1. To update the status of your database on the basis on the events that have taken place, you can configure a JDBC Adapter as a receiver.This JDBC adapter will contain the event and the flag value as well as Update condition.

Just check this blog and SAP help to understand how JDBC receiver adapter works,

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

2. In your JDBC sender adapter that is polling over your DB table, you can give the value for UPDATE as <test>. This means that you are not changing anything even after the adapter has picked up intfo from the table. Ofcourse, you would have to increase the polling interval time as the JDBC receiver has to update the DB.

I know this solution is a long shot and an added headache but if nothing works out, you can always do it this way.

Hope this helps,

Regards,

Bhavesh.

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Frank,

Another way to do this will be,

1. Select * from table where <condition> and flag='x'

2. Update table set flag='y' where <condition> and flag='x'.

Logically, select your rows from the table on the basis on some condition and only those that have flag set to 'x' and after you have selected the rows, the rows that have been selected are updated so that there flag becomes 'y'.

You can change your scenario with the select and update queries however you want.

Hope this helps,

Regards,

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

Hi frank,

I am not sure if the syntax of my SQL statement (Update statement ) is correct, do verify on that.

Regards,

bhavesh

Former Member
0 Kudos

Hi Frank,

That was a good reply from Bhavesh. Using the configuration mentioned by him you can restrict the JDBC Sender Adapter to pick the same data again.

The Update query looks like

<b>Update [TableName] set flag = 'y' where flag = 'x'</b>

regards,

Raju

Former Member
0 Kudos

Hi,

your update statement in JDBC sender will be executed as soon as ur select query is executed successfully no matter whether one record or more than one record is got as a result of select query. You have to incorporate the logic in the update statement eg would be like having a flag that would indicate row selected... something like that.so u have to conditionally update.

Regards,

Sudharshan

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Frank,

One solution to your problem can be done using CONDITIONAL RECEIVER DETRMINATION.

In your Receiver Determination step, you have the condition editior that allows you to specify the condition on which the receiver should be selected. Now, in your case, you can make a count of the number of records returned by your select statement and you check the condition in your receiver detemination.

Hope this helps. In case you need any clarification with this line of approach, do let me know.

Regards,

Bhavesh

frank_schmitt3
Participant
0 Kudos

Hi Bhavesh,

we are using the table for polling. This means that for example all 5 sec. the adapter show for new events. There is a field for a status, which is in the where clause of the sql-statement. When taking one entry this status must be set new, so on the next polling this entry can not be find. Because of this I wont this do in the jdbc-Adapter (send) by the update statement. This will be done on the java-stack. But then the generated message will be transfer on the abap-stack on the integration engine. But meanwhile the polling goes on. Therefore the setting of the status must be done in the jdbc-adapter.

I don´t understand your solution. When and how do you make the update, so the special entry can not be use for next polling (transaction block?).

Thanks in advance,

Frank

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Frank,

This is what I have understood from your clarification,

You want to configure a JDBC adapter as a sender, so that it will pick up some information from your table every polling interval. But, you dont want to pick up the same information repeatedly and so you are using a flag in your DataBase which has to be updated every time you pick up some statement.

In this case, while configuring your JDBC adapter as a sender, you will have two important fields to be considered.

1. Query SQL Statement

2. Update SQL Statement.

1.Your query sql statement will contain your SELECT SQL query like,

select * from tablename where flag='x'

2.YourUpdate SQL statement will contain your Update statement which will be like,

update table tablename set flag='y' where flag='x'

What this is logically is that first your SELECT statement is executed and your record is picked from DB abd then your UPDATE statement is executed so that the picked record need not be picked again in the next polling time.

For further clarification, check this link on SAP Help

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

Do let me know if this solves your problem and if i have understood your problem well.

Regards,

Bhavesh

frank_schmitt3
Participant
0 Kudos

Hallo,

i think this will not solve my problem, because then all entries in the table will set and not only the one i took from the data-Base.

Regards,

Frank

Former Member
0 Kudos

Hi

By specifying select statement i.e.,

Select * from TableName where flag = 'x'

you are selecting the entries which has flag set to 'x'.

and using the Update query i.e.,

Update TableName set flag = 'y' where flag = 'x'

you are making the flag to 'y' for only those records that are previuosly retrieved. So only those records will be set not all the records of the table.

If you want to select and update only single record then you can specify the where conditions according to your requirement.

But if your requirement is different from this, let us have a clear idea about that.

regards,

Raju.

frank_schmitt3
Participant
0 Kudos

Hi Raju,

Sorry, but i think that is not right for my szenario

In the event table there are much entries with that flag (it is an integer which can several status; initial=0; take this and send to the integration process=3 etc.).

When i execute the selcet statement becoming one entry. This have an individuel event_id. With this event_id i make the update. But how i can deliver this event_id to the update-Statement because this is dynamic and must be changing all the time. There are two fields on the front-end (configuration of communication-channel in Konfiguration of the Integration Builder):

SQL-Anweisung für Query: One result --> primary_key = 4711

SQL-Anweisung für Update: need the event_id (dynamic) like %primary_key%

Possible statement:

update table set event_status = 3 where event_id =4711

--> the problem is to make the reference of the event_id from the result of the select statement to the update (this must be dynamic)

Thanks in advance,

Frank