cancel
Showing results for 
Search instead for 
Did you mean: 

Proxy to JDBC synchronous scenario

Former Member
0 Kudos

Hi All,

My scenario is Proxy to JDBC where a message is sent from SAP as proxy it will have employee payroll data with some multiple records.Requirement is to using JDBC receiver adapter I need to check whether a particular record already exists in DB & INSERT if record does not exist.If record already exists an error message need to be sent back to PI.

For this requirement I'm designing Proxy to JDBC synchronous scenario, in JDBC receiver formats we have UPDATE_INSERT which will check If a record exists, then it will overwrite else it will INSERT record in to table. My requirement is with JDBC receiver I need to check If a record exists then return ERROR else record needs to be inserted. Can anyone please provide inputs how to do design this interface.


Regards,

Vivek.

Accepted Solutions (1)

Accepted Solutions (1)

former_member190293
Active Contributor
0 Kudos

Hi Vivek!

In this case I would use stored procedure on receiver DB side and call it in JDBC receiver.

Regards, Evgeniy.

Former Member
0 Kudos

Hi Evgeniy,

Thanks for your reply, DB team is not supporting for us to go ahead with stored procedures.

Regards,

Vivek

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi All,

Thank you for all of your valuable inputs, I will check with the possibility of designing this interface with JDBC lookup & get to you in case If I need any help further.

Regards,

Vivek.

Former Member
0 Kudos

Though having a stored procedure would be very ideal in this situation.

One scenario (which I have never tried) is to design a lookup (JDBC) scenario for all primary keys and on the basis of returned value for individual records you create one more level of mapping to pass on non-existing record further to database for insertion and which are already there you can create another branched scenario which will notify of its existence.

iaki_vila
Active Contributor
0 Kudos

Hi Vivek,

If you do the INSERT and the registers exists the JDBC receiver will return 0 registers inserted, you can take this answer like an error in your mapping, although this depends about the table and its primary key. Another way would be to do two scenarios, one with SELECT and the next one with the INSERT.

Regards.

Former Member
0 Kudos

Dear Experts,

Kindly let me know If we have any suggestions further for this requirement, do we have any other alternate designs options.

Regards,

Vivek

former_member190293
Active Contributor
0 Kudos

Hi Vivek!

Another option is to use ccBPM (if you use dual stack PI system).

Also you may consider JDBC-lookup in your request mapping to get records that already exist in your DB table and remove it from your payload. But in this case, I guess, you should use some kind of "tricky" way to store your cancelled records info in request mapping and restore it in response mapping in order to create correct error report in response structure.

Regards, Evgeniy.