This discussion is archived
12 Replies Latest reply: Apr 6, 2012 2:16 AM by Arik RSS

how to insert data coming from 2 different file adapters in to one DB adapt

Gunuru Newbie
Currently Being Moderated
Hi

i want insert data in to database containing two diffferent tables, so i imported tables in to DB adapter by creating relation ships.But, data for two tables are in xml format & two are in different locations.So, i used 2 file adapters to get data from 2 different & i used BPEL(Define service later) Service. now in bpel i used receive activity to receive one file adapter data ( i checked create instance in receive) then used transform activiy for tranformation & finally invoke activity to invoke DB adapteer........similarly i repeated sequetially to 2 file adapter, by keeping 2nd receive(no need to check create instance) next to invoke.*Problem is after deployment finished only data coming from 1st receive is inserting to the table...& 2 nd receive not working it showing as Pending & showing as Asynchronus Call back inte console*

I configured all the adapters perfectly..........Can any one can help me how to Commit 2 nd receive to insert data in the 2nd table

Regards,
jay
  • 1. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Arik Expert
    Currently Being Moderated
    Hi Jay,

    What version you are working on?
    If you are working on 11g you can use a transformation activity and have multiple sources. Just click the + and add sources from files/DB's messages
    (inside the transformation you will see the 2 sources and you can map them).

    If you are using 10g(11g as well), you can do the follow:

    1. define 2 variables with the same type as your target DB table (created after you ran the DB wizard for insert)
    2. transform first source into first variable
    3. transform second source into scond variable
    4. use assign activity to assign the first variable (at record level) into source variable
    use assign activity (can be the same below) to assign the second variable (at record level) into source variable (only this time choose the append operation and no copy
    operation)

    Arik
  • 2. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    NeerajSehgal Journeyer
    Currently Being Moderated
    Hi Jay,

    You can't use mid-process Receive like this to read a file. If you want to read a file in the middle of the process, use Sync Read option on File Adapter. After you have the data from both the files in 2 separate variables, you can transform the data to the Target as suggested in the above thread.

    Regards,
    Neeraj Sehgal
  • 3. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Gunuru Newbie
    Currently Being Moderated
    Thank u both for ur replay.........


    I am doing this in 11g there is no problem regarding transform activity.
    My requirement is

    two different files from two different folders in a drive & we can't use one file adapter bcoz both have different columns(only few are common columns) so we use two different xsd's .So, i am using two file adapters to insert in database having two different tables with respect to two different files data coming from two file adapters. i am using one DB adapter to insert bcoz both tables are in same database with relationships & i used BPEL(define service later) .

    NOW PLEASE SUGGEST ME THE FLOW IN BPEL TO INSERT BOTH FILES IN THERE RESPECTIVE TABLES IN DATABASE.
    The flow i did 1st file adater--->receive--->transform---->invoke----->DB adapter.....Then again repeating this as keeping 2nd receive below 1st invoke
    2nd file adapter-------->2nd receive---->2nd invoke------>same DB adapter
    MY problem is only data coming from 1st process is inserting & 2nd one is not working as i discussed earlier........I USED READ FILE OPTION, UNCHECKED DELETE FILES OPTION & SET DIFFERENT POLLING FREQUENCY FOR BOTH FILE ADAPTERS.

    I tried to set correlation but it is not working & later tried i kept non-blocking invoke as TRUE in DB adapter also didn't work...........also i tried this transaction property in bpel component _<property name="bpel.config.transaction"_

    many="false" type="xs:string">required / requiresNew</property>...............BUT NO CHANGE...........

    Regards,
    jay

    Edited by: 910162 on Apr 5, 2012 12:38 AM
  • 4. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Arik Expert
    Currently Being Moderated
    Jay,

    Just to be sure - you have 2 files (different XSD) and 2 tables.
    You want data from both files to be transfered into both tables (existing joins between them?)? File 1 into table 1, file 2 into table 2? Or file 1+2 into table 1+2?
    Do you know from advanced the name of the files?

    Arik
  • 5. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Gunuru Newbie
    Currently Being Moderated
    Hi Arik,
    i have 2 files (different XSD) and 2 tables, i want data from both files to be transfered into both tables ( joins are existing between them).File 1 into table 1, file 2 into table 2.
    file 1 contains header.xml file to be inserted header table
    file 2 contains line.xml file to be inserted line table


    Regards,
    jay
  • 6. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Arik Expert
    Currently Being Moderated
    Let's say you are starting with reading file 1 and by knowing the file 2 name you can poll it during your process.
    So, if I understood your request, you can have 1 receive activity(read file 1), 1 invoke activity (synchronous read file 2), 1 invoke activity (use db adapter with select/insert options, check the Use Outer...check box).

    Put the transformation activity before the invoke(DB). Choose both receive and invoke variables(files activities) as a source. Choose invoke DB variable as target.
    Now, inside the transormation, you will have in the target, a Header level(with fields) + Line level under the header level.
    Put for each above the header level and drag the record of the file 1. Then, put for-each above line level and drag record of file 2.

    Arik
  • 7. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Gunuru Newbie
    Currently Being Moderated
    i have a doubt if we use sychronous read we can read only one particular file that we give file name._

    But, if we have 'N' no.of header.xml files reading from file1 having different data it will works.
    But, while coming to file2 snychronous read means one one line.xml file can be inserted........after deployment when ever i keep new files in the both locations given to file1 & file2 to read,
    all files must be inserted (i use polling frequency)

    jay
  • 8. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Arik Expert
    Currently Being Moderated
    You can have 2 bpel processes. Each process will poll a file and insert the data into the table (file 1 -> table 1, file 2->table 2).
    Does the data must be enter in the same process? If no, just use 2 processes. In that way, you can also mange more correctly both files/tables details.

    If it's not enough, you can have a third bpel process that will be triggered and just read both tables and transform it into your join tables.
  • 9. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Gunuru Newbie
    Currently Being Moderated
    what you told i did & i was successed only inserting one Line.xml data in line table bcoz it is synchronous read , but coming to header.xml i can insert 'N' no.of files in header table. So, tell me the solution to insert 'N' no.of Line.xml files in line table simultaneously when inserting 'N' no.of header.xml file in header table

    FINALLY TELL ME WEATHER WE CAN DO OR NOT USING ONE BPEL PROCESS

    Regards,
    jay

    Edited by: 910162 on Apr 5, 2012 6:54 AM
  • 10. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Arik Expert
    Currently Being Moderated
    Jay,

    Assuming you must use 1 bpel process, I have 2 ideas:
    1. Insert a while activity inside a scope. Run an endless loop. Each loop use the synchronous read file - in that way you can read all your files.
    After all files were read, you will get an exception. Catch it and finish the process.
    Ugly but possible.
    2. Use java code and run unix command to find the number of files & files names. Once you have it, you can have a loop and use the synchronous read file.

    I still think you should use more then 1 bpel process.

    Arik
  • 11. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Gunuru Newbie
    Currently Being Moderated
    Arik,

    can you tell me the flow of activities in bpel for your 1st idea

    Regards,
    jay

    Edited by: 910162 on Apr 5, 2012 11:30 PM
  • 12. Re: how to insert data coming from 2 different file adapters in to one DB adapt
    Arik Expert
    Currently Being Moderated
    Sure.

    Activities are:

    1. receive to poll first file
    2. transformation
    3. invoke to insert first table
    4. new scope. Inside it:
    4a.while activity. Inside it:
    4aa. invoke for synchronous read second file
    4ab. transformation
    4ac. invoke to insert second table
    5. Catch (for the scope). Inside:
    5a. put some activity, maybe empty activity.
    6. end

    According to your need you can add more scopes, fault handling, messaged etc...
    Don't forget - you might have an exception on 4aa because there is no file and it should be.....

    Arik

Legend

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