proxy to jdbc insert

[former_member474221]

Hi

I have a proxy to JDBC insert scenario...I have the following questions

1. ECC is going to send multiple customers in one call.

    Should i repeat the "statement " node so that there will be multiple independent INSERT queries to the database

    or should i repeat the "ACCESS" node so that there is one single INSERT.

    In case i go ahead with repeating the "ACCESS" node ...even if one record ( customer ) has some data related issues the entire INSERT will be

    rejected right?

2. Should I make this synchronous ?

  I understand that JDBC will send the INSERT_COUNT back to PI ....should I send it back to ECC and make this synchronous.

  If I create multiple INSERTS inside the mapping....will the response have SUCCESS/FAILURE of each INSERT....??

  Will this response be helpful to ECC to understand which customer was inserted and which one failed??

[Shabarish_Nair · Accepted solution]

Yes you are right about the failure. If it is a single insert statement, then a roll back will happen in case of error.

If you want to make sure that one data issue doesnt affect other inserts, you could do a multimapping

Also regarding to make it sync or not depends if there is a business need for any updates back to SAP. If SAP doesnt really care, then why make it sync.

[former_member474221 · Accepted solution]

Do i really need a multimapping??

just by changing the "STATEMENT" node to multiple occurence will create multiple inserts right

also if i do not make it sync how will we understand if one of the inserts fails

Will i need to check the whole response xml?

[Former Member · Accepted solution]

Hi,

As suggested above, u can create a statement node with occ 0..unbounded and map the source records but if i am not wrong if any of the insert statement fails in that particular transaction it will rollback all the previous inserts (correct me if i am wrong).

But i would suggest that instead of using table using SP. Let me tell u the reason for the same, suppose if any of the insert fails ur SP wont rollback the transaction (u have to check database-auto commit handling in jdbc receiver) obviously you have to catch that error, save that errorneous record in some DB table and depending upon ur req u also can trigger a mail.

Thanks

Amit Srivastava

[Shabarish_Nair · Accepted solution]

If it is in the insert statement with multiple access, then in case of an error the entire transaction gets rolled back. If that is what you want, avoid multi mapping.

[Former Member · Accepted solution]

Hi Shabarish,

What if there are multiple statements in the single transaction then also i guess all the previous statements (within the transac.)  will get rolled back?? 

What u have to say on this.

Thanks

Amit Srivastava

[Shabarish_Nair · Accepted solution]

yes (

Note 823809 - XI 3.0 JDBC Adapter: Rollback not performed on DB error

).

hence if you dont want a roll-back in case one insert fails, you may use multi mapping with each insert as a separate call.

[rajasekhar_reddy14]

1)Mapping to Statment or access node completely depend on your requirement, some case one record it self can have multiple records this case we map to ACCESS,but in your case one record = one insert means then map to access.

There is no difference between mapping statement and access it consider as a multiple Insert statements only. performance als same.

2)If you requirement is sync then you can achieve it ecause Proxy and JDBC supports Sync communication but conside design change like moving to stored procedure right idea. always.

Regards,

Raj

[former_member474221]

ok i now understand that muultiple INSERT and multiple ACCESS have the same effect

But if I have multiple INSERT won't it create multiple response nodes in the response xml

if say 5 of those have INSERT_COUNT as 1 and rest have INSERT_COUNT as 0 how will the sender know....

is this situation possible or there will never be a case where INSERT_COUNT is 0 instead rec channel will directly get an error ??

I am trying to figure out whether async interface is ok in my case

[baskar_gopalakrishnan2]

The insert success only provide insert_count response. If there is error,you get an error. So you dont get insert_count 0 response. Shabarish already explained above regarding when to use multimapping.

[rajasekhar_reddy14]

If you really wnat to update count details to Traget system then go with sync other wise not required go with Async scenario.

Completely depend on your bussiness requirement.

If you really want Sync then try with Stored procedure approach at JDBC side.

[Former Member]

Hi,

Any specific reasons for not using SP??

Personally i would have tweaked this design and used SP. The SP will have a higher control over the processing of records and as already stated u can track those failed records and do whatever u want (mailing, sending it to sender application etc).

Thanks

Amit Srivastava