How to pass parameters to Stored Procedure

[Former Member]

Could someone please guide me in following scenario.

1) I need to execute a stored procedure/sql statment using JDBC sender adapter for SQL SERVER

2) The input parameters are dynamic and are stored in file.

Thanks

Venu

[Former Member]

Hi Venu,

I can understand that input parameters could be dynamic. is that your design to store the dynamic parameters in a file?. Can you change your design so that you store the parameters in a database table. Then you may use value lookups to get the parameters from that table.

Arul

[Former Member]

Krishna & Bhavesh,

My end-2-end scenarion:

1) SQL server to File(to 2 FTP servers)

I am planning to do following way, will that work?

1) Define a outbound file adapter and receive the parameters

2) Define & configure a JDBC receiver adapter

3) Direct JDBC output to a BPM and do the validation and alerts

4) Send BPM output to FTP server

Please provide your valuable comments on this scenario.

Thanks

Venu

[moorthy]

HI Venu,

<i>1) SQL server to File(to 2 FTP servers)</i>

>>> For this BPM is not required. However, your requirement is to pass the dynamic values to stored proc. and get the data from the SQL server. If so

Let me detail you,

1) First get the File data into BPM ( thru Sender File Adapter and the BPM is the Receiver)

2) then send the this file data into SQL server thru JDBC Reciever Adapter. Make it synchronus call. Once you get the response from the SQL server, you can route this into 2 ftp servers.

Your BPM steps will be

1) Recieve the file message

2) Send the file message into the JDBC system - Sync Send step

3) Get the response back and send this response into 2 ftp severs with 2 send steps.

Assuming Mapping is done outside the BPM.

Hope this helps

Regards,

Moorthy

[moorthy]

Hi,

What is your end to end scenario ?

If you want to execute your stored procedures, you can even do it in the receiver jdbc adapter.

Message flow in XI starts with Sender agreement. ie, if you do the Sender JDBC adapter, you are indirectly giving sender message interface ( Outbound Message interface). so for this how can you pass the value ?

So I don;t think so it is feasible as per your requirement. other option is you can go with BPM etc.

Regards,

Moorthy

[bhavesh_kantilal]

Hi,

Take a look at my blog which is for SELECT in a receiver JDBC adapter .

Just make the changes to the Datatypes in accordance with this link and follow steps mentioned by moorthy,

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

REgards,

Bhavesh

[bhavesh_kantilal]

Hi,

<i>The input parameters are dynamic and are stored in file.</i>

Don't think this is possible. What you are looking at is to create both the KEY and VALUE dynamically for the Stored Procedure. But, the Datatype of your Stoered Procedure cannot be defined dynamically. You need to have fixed input and output parameters.

One option you can look at is creating multiple datatypes for each of the possible input / output parameters combination and then determine using contional receiever / interface determaintion or a BPM as to which Message is to be sent out.

Regards,

Bhavesh