Multiple stored procedures using JDBC Adopter, Synchronous call

[Former Member]

Hello Guys,

This is kind of different scenario,

Here is the scenario (SQL Server -> XI -> Legacy)

1.I want to trigger stored procedure (from XI to)

2.Inside the stored procedure we are going to have multiple stored procedures.

-Inside stored procedures

SP1 -> It supposed to select the data from interface control table, if that is success

SP2 -> Call some other stored procedures which can be select/insert/delete from different tables most of the cases it would be select, if that is success

It should give results back (Response) to XI and with some output parameters like (status message, etc…)

Next step I want send those results to target system which is legacy. If that is success

Next step I should update entry in the Interface control table(which is in SQL Server) with success entry like (success message, current date, current time, interface name, username.)

If it fails any stage like while selecting data from interface control table which is SP1/ SP2 It should give error message back to XI(output parameters). XI is going to send Alerts/Mails to respective interface owners.

This is my scenario can anybody help me on this what needs to be done to implement this scenario.

[moorthy]

Hi Murali,

Anyway , altimately you are going to call one stored procedure and going to get one output. So it will not be a problem I think.

Anyway it is required to introduce BPM here.

<i>Next step I want send those results to target system which is legacy. If that is success</i>

>>>You can make sure that message is recieved by legacy system. Because legacy systems like File systems will not support Application Acknowledgement. So here you can go with Transport Acknowledgement.

From where will get Success messages like (current date etc) are you planning to take XI system date.. If so you can get this. And you can send the message to SQL.

<i>If it fails any stage like while selecting data from interface control table which is SP1/ SP2 It should give error message back to XI(output parameters). XI is going to send Alerts/Mails to respective interface owners.</i>

>>>For application errors like SP2 error etc, you need to get the Error Response from stored procedure itsel.

Second option is you can go with Adapter Alerting, whenever any error in the Sender JDBC adapter.

Anyway design/requirement should be perfect, before start the process here...

Regards,

Moorthy

[Former Member]

Moorthy,

Thanks for your quick response.

we have to use BPM that is fine.

<i>Here is my main concern.</i>

after trigger the stored procedure from XI, after every thing is success like (inside stored procedures executed successfully)while getting the response from SQL server how is our target structure,

ex: Becuse if results is having 100 records + some other output parameters like (message text message status).

our response should kind of table parameter (0: outbound, which can store multiple records) and other output parameters(status, message text i:e which stores single record (stored procedures should pass the constant message based on the condition)). is that possible.

Moorthy, can you please give your inputs on this..

Thanks

M

Message was edited by: Murali Babu Pallabothula

[Former Member]

Moorthy,

Can you please give me bit detailed on this issue, Thanks

M

Message was edited by: Murali Babu Pallabothula