Proxy to JDBC

[silentbull]

Hello All

I have a scenario where I need to insert multiple records in two tables using JDBC.

I have a header table which will hold one record and detail table which is unbounded.

These are my clarifications with regards to JDBC Receiver

1. Can PI insert multiple records into multiple tables?

2. Do we require just one communciation channel or two?

3. If for some reason the header record has worked but failed in detail, will the entire set be rollbacked or only header will be showing with records. What settings should I make for this?

4. Many suggested alternative would be stored procedures, what benefits do i have in choosing stored procedures for this particular scenario and what steps need to do from my side on stored procedures.

Regards

Sam

[Former Member · Accepted solution]

Hello,

Check this thread it will answer most of ur queries

http://scn.sap.com/thread/1774710

>>If for some reason the header record has worked but failed in detail, will the entire set be rollbacked or only header will be showing with records. What settings should I make for this?

I think yes, if any of the statements inside a single transaction gets failed then the entire query will be rolled back.

But certainly this behavior can be change (check my reply below)

>>Many suggested alternative would be stored procedures, what benefits do i have in choosing stored procedures for this particular scenario

IMO, using SP is always a better option becoz it provides lot of flexibility + better in performance.U can just send data to SP and let SP handle the task of performing DML operations on multiple tables.

One other such instance where SP could be handy, suppose by any chance if any of the insert within a transaction gets failed then ur SP won't rollback the entire transaction (u have to check database-auto commit handling in jdbc receiver) and then obviously you could catch that error, save that erroneous record in some DB table and trigger mails (all these stuffs have to be done in DB).

>>what steps need to do from my side on stored procedures.

Create structure in below format and map the same with ur source message and u r done

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

Thanks

Amit Srivastava

[Former Member]

Structure for stored procedure

http://help.sap.com/saphelp_nw73/helpdata/en/44/7b72b2fde93673e10000000a114a6b/content.htm

Regards,

Pranav

[former_member184681]

Hi Sam,

Answering your questions:

1. Yes, as long as they are in the same database.

2. One, as long as both tables are in the same database.

3. It depends on your Transaction Isolation Level (read more here), but yes, it is possible to apply the transactional processing, as long as the database engine supports it.

4. Benefit for you is mainly that you move the responsibility for data integrity to a 3rd party (responsible for DB development) and allows some additional logic on DB side (not just plain INSERT). Using stored procedure call instead of INSERTs requires using a different message structure. Apart from that, both approaches seem similar.

Regards,

Greg

[Former Member]

Hi Sam,

I would create jdbc recordset for two tables in the same data type structures, which would ensure all the queries are executed successfully, in case of exception, message will be rolled back and in error status.

1. Can PI insert multiple records into multiple tables?

>>> yes

2. Do we require just one communication channel or two?

>>>> We require one channel as we shall provide database name in the channel, table names shall be mentioned in the mapping (or structure).

3. If for some reason the header record has worked but failed in detail, will the entire set be rollbacked or only header will be showing with records. What settings should I make for this?

>>> Entire message is in error, no inserts shall happen to the table.

4. Many suggested alternative would be stored procedures, what benefits do i have in choosing stored procedures for this particular scenario and what steps need to do from my side on stored procedures.

>>> Stored procedure is a better option, Legacy team should create stored procedure at there end and handle exceptions. we need to have Stored procedure record set format and map all fields as required.

Regards,

Pranav