Reg:UDF and Stored Procedure

[Former Member]

Hi All,

My actual requirement is, A project ID is being triggered to PI which will pull the data from the database(SQL Server 2000) ,from different tables and it has to post to SAP-R3 system through a RFC call.

For the above scenario I have used BPM ie SOAP-JDBC-RFC and have written a stored procedure for that as I need to pull data for a NxN structure.

Initially, I tried through JDBC lookup and but I couldnu2019t get the data for NxM structure.

Can anyone explain me clearly why its not possible through a UDF to achieve the above scenario but only possible through a stored procedure for a NxM structure.

Thanks in Advance,

Lavanya.B

Edited by: Lavanya Balanandham on Jan 5, 2009 6:57 AM

[Former Member · Accepted solution]

Hi,

Plese see the blog by Michal Krawczyk

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/a03e7b02-eea4-2910-089f-8214c6d1...

Even though its on RFC lookup, I think JDBC lookup also acts in the same way.

The lookup gets us back the response as a Stream of Data in a single line ( Even though the return object is 2 dimensional)

It all depends on the way we parse it and send back to the Receiving Structure.

But Stored Procedure is definitely an easier option.

Regards,

Subhendu

[prateek]

The output of lookup could be assigned to only one target field, otherwise you will have to execute the lookup again and again which is not recommended. BPM approach is most efficient in your case. The strored procedure can be executed using lookup however this doesn't seem to be a good design.

Regards,

Prateek

[Former Member]

Hi Lavanya,

I am not sure but I think the result set of a User Defined Function can only handle one dimensional array but not Multi dimensional Structures.

In your case as u said that u have to pull data from different tables in DB , I guess it would involve more than one single query and the result u need is a set of Records..

So as it is a Stored procedure would be relatively easy to achive this rather than a Lookup and Java code..

May be some one can suggest a better solution for this.. Lets see wat experts have to say.

Gud Luck,

Cheers,

Mohan.