Design help

[Former Member]

Hello guys,

I have a requirement where i need to implement the below

1. Call Stored proceudre and get ID name

2. if above sp returns error as output ,then call another sp and pass id name and status as error

3. if no error then execute some mapping and store the data into file

4. upon success of step 3 call sp with some other status

5.. similarly i have two execute two more sps after the above steps

I think it is possible only with BPM..

what i am looking for is

1. Any other option available other than BPM

2.If only BPM do i need to follow the below steps?

receive step

send step (for invoking First SP)

transfomration

send Step for File

send step for SP .

Thks in Advance

Rajesh

[Former Member · Accepted solution]

HI Rajesh,

I think you could also use a UDF with a mapping lookup. But I think your requirement is very complex so I would to recommend you to use BPM because it's better for message tracking.

what you need is:

sender agreement.

Receiver Agreement (maybe more than one)

receiver det (for each SP call, for the file and for BP)

interface det (as many as at receiver det)

and finally the transformation.

Regards,

Björn

[Former Member · Accepted solution]

Thanks for response

But to my knowledge no idea about the job of store procudure means any database operations of insert ,updation,,

are doing by Stored procudure

I read that lookup of db should be used only for select operations ,but still can i use udf for Stored procedure ?

Thanks

Rajesh

[Former Member · Accepted solution]

Hi,

From UDF you can certainly call stored procedure (use jdbc library) and then do whatever you want.

Steps:

-> Use JDBC drive to connect database.

-> Call Stored procedure

-> evaluate response

-> end of the udf

Another good way to do it external Java code, design external Java program to do DB I/O, try to make it generic so that it can be reusable and call this program from your mapping udf.

Regards,

Gourav

[prateek · Accepted solution]

When you talk about overall design, BPM would be the best design in terms of error handling. Use Branch step to handle the exception raised by one of the step and it would be very clean process.

but still can i use udf for Stored procedure

Yes, you can. But this would be considered as a poor design. There are various flaws in calling SP from UDF.

Another thing could be (but a difficult one), you merge the functionalities of various SP and call only one or two SP from PI. This would involve more modifications at the communicating system side.

Regards,

Prateek

[iprieto]

Hi Rajesh,

You could use Java Mappings for do the process as second option.

As you know, you can use Mapping Lookups for connect via JDBC to store procedure or SQL statement. This method have more development effort but the performance will be much better.

The idea will be insert in Java Mapping the connections sentences to DataBase using mapping lookup. It's very important implement a good try and catch mechanism for error control .

If you use BPM the sequence that you show in this post looks correct.

Best Regards

Ivá