Skip to Content
avatar image
Former Member

JDBC

Hello every one i have just got a requirement to insert data into multuiple tables using JDBC adapter, i am new to PI and this is my first interface i am doing on JDBC, before starting i have some doubts, can any one please cleare these.

How to insert data into multiple tables ?

Is it mandatory to give table field in case of JDBC receiver?

Is it possible to update all records in case of JDBC adapter without Key?

Thanks

Swetha

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Feb 10, 2012 at 06:41 AM
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 10, 2012 at 06:43 AM

    go for Stored procedures..let DB handle the insertion logic into multiple tables

    http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/1671

    http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9618

    chk the structure format:

    http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

    however in case u want to insert data into multiple tables chk this discussion (Praveen's reply):

    http://forums.sdn.sap.com/thread.jspa?threadID=1774710&tstart=30

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 10, 2012 at 06:55 AM

    How to insert data into multiple tables ?

    in your target Database type:

    for ex:

    <StatementName1>

    --<dbTableName action=u201DINSERTu201D>

    -<table> --


    assingn to table1

    ---<access>

    -


    <col1>val1</col1>

    -


    <col2>val2</col2>

    ---</access>

    </StatementName1>

    <StatementName2>

    --<dbTableName action=u201DINSERTu201D>

    -<table> --


    assign to table2

    ---<access>

    -


    <col1>val1</col1>

    -


    <col2>val2</col2>

    ---</access>

    </StatementName1>

    Is it mandatory to give table field in case of JDBC receiver?

    https://forums.sdn.sap.com/thread.jspa?threadID=1005995&tstart=0

    http://help.sap.com/saphelp_nw04/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm

    Edited by: bhavanisankar.solasu on Feb 10, 2012 12:27 PM

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 10, 2012 at 04:30 PM

    >How to insert data into multiple tables ?

    If you have more than five or six tables then I would recommend to go for StoredProcedure. Less than 5 tables, still you can handle using jdbc statement structure. Creating seperate statement for each table.

    Question: Are these tables belong to the same database or different? If it is different, go for stored procedure.

    >Is it mandatory to give table field in case of JDBC receiver?

    Not Necessary. It depends on the jdbc structure you go for. If you go for the below structure, you dont need to specify all the fields

    <StatementName>
    <anyName action=u201D SQL_DMLu201D>
    <access>SQL-String with optional placeholder(s)</access>
    <key>
      <placeholder1>value1</placeholder1>
      <placeholder2>value2<placeholder2>
    </key>
     </anyName >  
     </StatementName6>
    

    >Is it possible to update all records in case of JDBC adapter without Key?

    I would say it is purely design of the tables. The best practice is to have keys on the db tables. So you need keys to update the table. YOu can try using the above structure and passing no value for the key fields and see how that works.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 10, 2012 at 04:46 PM

    If you use multiple statement in Receiver data type you can insert data in to multiple tables but design point of this is not a right approach to handle high volume because every record you perform multiple insert operations so obliviously it takes more time.

    if load is not high then go with multiple statement objects in Data type otherwise use stored procedure.

    Regards,

    Raj

    Add comment
    10|10000 characters needed characters exceeded