I have a requirement where I need to update a status field and last activity time field ( time of instance completed) in a database table. I used Polling option in database adapter, but it is updating the status field immediately once it is read. I don't want like that, I want to update the status field after completion of instance. Also, how to update the last activity time field ( i mean the field has to update at what time the instance got completed successfully). Actually, i am using
1. A database adapter for polling followed by receive activity, assign activity, invoke activity and this invoke activity invokes one more database adapter ( the adapter selects a table row with the ID given by database adapter 1).
2. Followed by transform activity, and again a invoke activity which invokes a webservice ( I am passing output values of 2nd database adapter as a input to webservice).
Let me explain clearly, I have a base table and duplicate table. When ever values gets inserted or updated in base table it needs to be reflected in duplicate table ( wrote a trigger for this and it is working fine). Apart from this I added two more fields in duplicate table ( with column names status, and instance completion time). I am using two database adapters one is for polling from duplicate table and other one for selection of values from base table based on ID provided by duplicate table. Further, the invoke activity receives the output from database adapter and again i am passing the output values as a input to external webservice. My question is how can I update the status field and instance completion time in duplicate table. Someone please help me.
If I understand your requirement, you have few options:
1. Build your process in a way that it will act as a synch process. That way you will have the option to rollback the transactions
and allow to poll the table again.
(see explanation: Re: Reading 5K records from DB
2. You can go with your solution. Create a table that acts only as a trigger table.
If something happens in you process and you want to poll from it again, just insert new record in it, in the end of the process.
Thanks for your reply. I have one more question, If any error raises in between the process, how can i save the error status into db adapter field. For example, I will receive a input from db adapter and I will send this input to a webservice which invokes further activities , my question is if any issue comes in between the process, i want to store that error message in db adapter field ( for example error field) can you throw some light, I mean step by step process.
You can just use a fault handling to monitor any system or business fault.
So, for example - put the invoke that calls the WS inside a scope, and add a catch activity. Within this scope, add another invoke that update or insert data to a DB table.
You can do the same for each part of the flow that you want to monitor.
More detailed explanation for fault handling:
But when i am updating or inserting into the table, the table is polling again. Actually, i thought of writing some condition during update operation of database adapter ( like update only if id=#sid and status ='Y'), but i don't see any option for update in database adapter. How can I alter the update query during update operation of database adapter.
I have one more question with respect to date conversion Actually the response date i am getting is YYYY-MM-DDTHH:MM:SS, but db adapter is not accepting this kind of format, actually i tried to convert date format using format-dateTime. but still it's not working. I am getting an empty response. The parameters that i am passing are format-dateTime( response as input, [D01][MN,*-3][Y001])