Status update back to database

[Former Member]

Hi,

We are working on XI3.0 SP9. I have been asked if it is possible for an interface to select all data associated with a single Transaction ID from the database and once the mapping is finished to update the status for that Transaction ID. So my Update statement has to be dynamic and only update for the specific Transaction ID.

Is this possible?

Thanks

[former_member187339]

Hi Gareth,

>> I have been asked if it is possible for an interface to select all data associated with a single Transaction ID from the database

Yes use the normail Sender JDBC and the select query

>>and once the mapping is finished to update the status for that Transaction ID.

The update statement in Sender jdbc cannot be used here.. you need to read records then do mapping and then update record based on some condition. So make it as jdbc to XI to jdbc (for the mapped record).

So my Update statement has to be dynamic and only update for the specific Transaction ID

>> But once you read the records it will be delivered to XI and so you canmake use of the update section of sender jdbc adapter. Only in cases where you need to some selctive update (say based on the outcome of mapping) then the scenario needs to be modified.

Regards

Suraj

[Former Member]

Hi,

"So make it as jdbc to XI to jdbc (for the mapped record)."

When you say this does this mean I should use BPM to make this almost a synchronous interface?

[former_member187339]

Hi

I am not sure who is the actual reciver in this case.

jdbc -> XI (then jdbc receiver)->? (end receiver)

IF you are planning to make interface like above , then Yes BPM is required.

Or else make if the receiver is also synchronous, then the entire scenario can be made synchronous (without BPM) using ResponseOneWay Bean.

Regards

Suraj

Edited by: S.R.Suraj on Aug 6, 2009 4:23 AM

[venkatanarayana_vagu]

Hello Gareth,

we did this with BPM as we added an extra field at source as XI FLAG and once the sender channel pick the data and it will update the field against to the row as some value. I we send this to BPM using insert count and update count from the receiver for each row and as sync receiver from receiver at BPM and next we are adding this data to the transformation step along with the key node and sending back to the source database with the status as if it is inserted then it will show the status as insert or update then update.

BPM:

receiver(ASYNC) -> Sender (SYNC) -> TRANSFORMATION (FLAG with KEY NODE) -> receiver (to Sender JDBC)

[Former Member]

By default you can issue a update statement to the database after fetching some records using select query and after that you will not have control over the transaction (containing your db data)

If you are talking about this transaction then a failure in this transaction can not be updated usingupdate statement

Rajesh

[Former Member]

I need to do something like this (pseudo code)

select * from TABLENAME where TransactionID = "lowest TransactionID in table"

this returns data for TransactionID "12"

I then have to do the following:

Update TABLENAME set Status "DONE" where Transaction ID = "12"

You can see I have to update a specific Transaction ID. I cant just assume the lowest will be OK for the update.

Thanks

Edited by: Gareth O´Brien on Aug 6, 2009 10:10 AM

[Former Member]

Hey ,

when you are able to get some transaction id using select query condition then the same can be used at update statement

i.e update tablename set status "done " where transactionID = "lowest transactionID in table"

But one thing to be observed here is that even though the data retrieved using select query cannot be guarnated to deliver to target system, update will be executed and need to make sure that the XI transaction will be commited ( i.e even if it fails manual intervention or automatic rsend has to be done for guaranted delivery)

Rajesh