Insertion in Sequence using JDBC Receiver adapter

[Former Member]

Hi,

How can we insert data in multiple tables without using BPM in certain predefined sequence.

My requirement is:

I want to insert in table A, then B, then C. Without using BPM

I want to avoid any stored procedure for this activity

Kulwinder

[Former Member · Accepted solution]

Hi Kulwinder,

just create 3 statements in order a,b,c in your datatype for jdbc receiver strucutre, jdbc adapter will execute it in sequence.

Thanks,

Tuhin

[prateek]

In your case, as you have multiple tables and data for all the tables comes in a single message from source, then you can't directly make sure the order of insertion. This is because the transaction handling capabilities at each table are independent of each other.

I disagree to what others have suggested till now.

Regards,

Prateek

[Former Member]

Hi,

thanks prateek.

So we have following options to achieve this ;

1) put all data as single statement and insert it in staging table. Stored procedure should pick it up from there.

or

2) call stored procedure itself directly from XI

Actually we want to avoid SP, because we have alsmot 20-30 interfaces like this and its lot of work to write SP for each.

Any other suggestion.

Kulwinder

[prateek]

In both the cases, you have mentioned the use of stored procedures. So if the receiver allows direct access of database, then 2nd approach would be better.

i couldn't think of any option other than BPM.

Regards,

Prateek

[Former Member]

Hi,

take your source msg.......create one target data type have JDBC strructer for table A, then below it table B and then table C...create one msg type and one msg interface for it............map it and execute your scenario........then JDBC recever comm channel will insert the records sequentially............

Regards,

Rajeev Gupta

[Former Member]

Hi Rajiv/Tuhin,

Are you sure about this approach ? Have you achieved it earlier ?

I do not think statements are executed in sequence. JDBC adapter sends all statements in one short and they hit database at sametime.

After that insertion time depends upon the size of data.

Kulwinder