BPM Steps Asynchronous Flow

[Former Member]

Hi all,

I had a scenario where i had to pick a file and transfer it to Oracle database table.

installation of drivers ,connectivity test has been done...

but the database table has to populated every time fresh data., as per the requirment. now i had to pick a file ,than a particular table data( all rows, not table)

has to be deleted, and than the new file data has to be populated in the table

i had design 2 mappings :

1. source file data to target table datatype ( for for inserting the data ).

2. source file data to a data type( for delete operation).

in BPM i had to make these asynchrous steps in sequence

<b> > Pick file-> Delete all rows in table --> Insert in table.</b>

<u>can any help what all the Blocks in BPM i had to configure.</u>

till now i had done :

<b>Start > Receive Step> Transformation1 ->Transformation2> Stop</b>.

<i>Transformation1: Interface mapp for Deletion

Transformation2 : Interface mapp for Insert.</i>

is it correct or somewhere i had to change.

Regards

AP

[former_member192892 · Accepted solution]

Ajay,

You can have multiple SQL statements in JDBC receiver document format..

You don't even need BPM for this if you just want to run two queries in JDBC receiver

[Former Member · Accepted solution]

Thx Varun

But i had to execute these steps in <b>sequence</b> first delete all rows in a particular table than insert file data in that table.

will your solution work with single jdbc adapter.

thx

AP

[Former Member · Accepted solution]

Ajay, then change order of tags - first delete, than insert.

Peter

[former_member192892 · Accepted solution]

Ajay,

Peter's reply is perfect....

The first occuring node will b executed 1st

[Former Member · Accepted solution]

Thx Varun Mukund & Peter Jarunek

i tried u r option and it is working....

<b>But i had to use BPM , not because of two sequence mappings , but to configure

exception also...</b>

do u any idea of the following BPM steps....

when i am using check option in my BPM

it is showing some message as XX ( Out bound interface used in 1st maaping) Container variable not intialised

and IN and Out bound interfec ( Used in 2 nd mapp) intialised but not used.

[Former Member · Accepted solution]

Ajay, which exception do you want to handle? If delete fails you don't want to insert the records? And if not, what do you want to do with them in this case?

Peter

[Former Member · Accepted solution]

Thx Peter For the reply...

After deletion , when insertion will take place...i had to send a mail that how many lines have inserted , through mail adapter.

counting the rows will be done in mapping through UDF.

Regards,

AP

[former_member192892 · Accepted solution]

Hi Ajay,

Do this in your BPM,

1) Receive step for file input

2) Transformation to map to JDBC receiver format

3) Syncronous send to JDBC(I'm doing sync send because the count of the inserted rows can be received in the response)

4) Transformation to map to mail receiver format

5) Asyncronous send to mail adapter

[Former Member · Accepted solution]

Varun, this won't help Ajay, cos' you can't have items to be inserted in the response of the first JDBC call.

Well, Ajay, this is a bit difficult. I suppose, you have to send 2 messages into BPM. First deletes rows, 2nd will do the insert. BUT - there is a problem, that 2nd insert depends on whether delete is successful... ufff

You can probably try:

a, Receive step for deleting message (starts BPM)

b. transformation (if needed)

c, Block (in block sync JDBC call to delete - in exception branch exc catch)

d. if succesful - receive step for inserting message

e, transformation (if needed)

f, send to insert rows.. (if exception should be cought, again in the block)

This is just my idea, how it would be possible to think about it...

[Former Member · Accepted solution]

Hi Peter

You are correct , my IInd insertion depend upon the sucessfull execution of the Ist delete Steps...Only.. I am following the details provided above..

Thx

AP

[Former Member · Accepted solution]

Ajay, one more idea:

try to use correlation:

send insert data "start correlation", wait for response from delete, if seccuessful -> mapping and fulfilling the correlation condition, this can send the insert data further..

Peter

[Former Member · Accepted solution]

Thx Peter..

Correlation is grey area to me.... but i appreciate u r solution too..

I will Close this thread as soon i will make my BPM work..or i will come back with some doubt..

Regards

AP

null

[former_member192892]

Use

<root>

<StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

</StatementName2>

<StatementName3>

<dbTableName action=”DELETE”>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

</StatementName3>

</root>

as the JDBC receiver data type structure