cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (5)

Answers (5)

rajasekhar_reddy14
Active Contributor
0 Kudos

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

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>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.

Former Member
0 Kudos

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

Former Member
0 Kudos

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

praveenreddy_bk
Participant
0 Kudos