This discussion is archived
8 Replies Latest reply: Dec 12, 2012 3:18 AM by NeerajSehgal RSS

How to send response back to db adapter

BK574 Newbie
Currently Being Moderated
Hi,

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.

Thanks,
  • 1. Re: How to send response back to db adapter
    Arik Expert
    Currently Being Moderated
    Hi,

    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.

    Arik
  • 2. Re: How to send response back to db adapter
    BK574 Newbie
    Currently Being Moderated
    Hi Arik,

    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.

    Thanks,

    Edited by: 834995 on Dec 4, 2012 1:48 PM
  • 3. Re: How to send response back to db adapter
    Arik Expert
    Currently Being Moderated
    Hi,

    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:
    http://docs.oracle.com/cd/E21764_01/integration.1111/e10224/bp_faults.htm#BABIGGIB


    Arik
  • 4. Re: How to send response back to db adapter
    BK574 Newbie
    Currently Being Moderated
    Thanks for your reply,

    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.

    Thanks,
  • 5. Re: How to send response back to db adapter
    Arik Expert
    Currently Being Moderated
    You can just use the Pure SQL option to put your update query.
    Use # for parameter
    f.e update table1 set column1='abc' where column2=#param1 and column3=#param2

    Arik
  • 6. Re: How to send response back to db adapter
    BK574 Newbie
    Currently Being Moderated
    Thank you very much. I will try it once
  • 7. Re: How to send response back to db adapter
    BK574 Newbie
    Currently Being Moderated
    Hi Arik,

    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])


    Thanks,
  • 8. Re: How to send response back to db adapter
    NeerajSehgal Journeyer
    Currently Being Moderated
    Hi,

    Use your function like this.
    <xsl:value-of select="format-dateTime($input, '[D01] [MN,*-3] [Y0001]')"/>
    Also ensure that you have the value in the $input variable.

    Regards,
    Neeraj Sehgal

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points