How to update the field status in RUN time

[Former Member]

Hi,

In my JDBC to file scenario when ever the JDBC channel picking the data of TXN_D field I want to update the STATUS field to 'Y' in oracle DB.

EX: I have 1 table XXX

      FIELDS:  TXN_ID         ,STATUS
          Data       10                       N

                         20                      N

                         30                      N

                         40                      N

When ever my JDBC sender channel picking the data of TXN_ID field then STATUS filed data had to be update to Y. Plese help me on this.

Regards,

Anji Reddy

[iaki_vila]

Hi Any,

You can try with:

SELECT TXN_ID  FROM table WHERE STATUS = N;

UPDATE table SET STATUS = 'Y' WHERE  STATUS = N;

You have to take into account:

1. Your DB team has to tell you the connection

2. Your DB team has to tell you if you have to set a schema before the table name.

3. You basis team has to install the jdbc drivers.

Regards.

[Former Member]

Hi,

Thanks for your replies. But I want single row updation based on TXN_ID. what ever the above solutions are updating multiple rows.

Regards,

Anji Reddy

[pvishnuvardan_reddy]

Hi Anji,

In the update query which Inaki said, just add the TXN_ID also, which could set that TXN_ID status accordingly.

i.e. UPDATE table SET STATUS = 'Y' WHERE  STATUS = N AND TXN_ID = XXX

Try this out and let me know the outcome.

Regards

Vishnu

[Former Member]

Hi Vishnu,

Actually if I mention the particular transaction id, it will update the single transaction only.

I have mentioned the  Select query in JDBC SENDER channel is like

select transaction_id  from XXX where status='N',

after picking the transactions what ever the transactions picked I have to update the status fileld from 'N' to 'Y'depending on transactionid .

If I use your update query I can update only one particular transaction. But my requirement is different.

Thanks

Anji Reddy

[pvishnuvardan_reddy]

Hi Anji,

When you mean: after picking the transactions what ever the transactions picked I have to update the status fileld from 'N' to 'Y'depending on transactionid .

Can you please elaborate with an example.

As Inaki said, UPDATE table SET STATUS = 'Y' WHERE  STATUS = N; this update query statement, will mark TXN_ID status to Y which ever got picked up by the adapter at a polling instance.

Regards

Vishnu

[pvishnuvardan_reddy]

Hi Anji,

In Sender JDBC adapter configuration in PI, you have option to update sql statement, where you write the condition such that the picked records could not be picked up the channel in the next run.

This would solve your requirement.

Try this and let us know the outcome.

Regards

Vishnu

[sendhil_kumar]

Hi Anji,

The update query in the sender channel is where you can write a query to update status.

Go through the link.

Configuring the Sender JDBC Adapter - Advanced Adapter Engine - SAP Library

--

Sendhil