Can we execute more than one SQL in Stored Procedure?

[Former Member]

Please help me with this.

I am connecting to SQL SERVER

Thanks

Venu

[Former Member]

>>2 Configure Receiver JDBC adapter(Synchronous)which executes a STORED PROCEDURE that has some processing logic containing more than one sql statment

This is no problem. You can have any number of select /update statements inside the stored procedure. But <b>your stored procedure will not return data to XI and that will not be sent as a response to your BPM</b>

Bhavesh,

Not to offend you, Are you 100% sure about the highlighted statment above.

Thanks

[Former Member]

Can anyone suggest the solution for my scenario:

1) I need to execute stored procedure to get data out from SQL server (Input parameters are in file)

e.g:

I need get data for a location and company, and these details vary and are stored in a text file

2) Pass the response to BPM

Thanks

Venu

[Former Member]

Bhavesh,

The "batch processing" information is in "Configuring JDBC receiver adapter configuration section"

Thanks

Venu

[Former Member]

Hi Bhavesh,

Thanks a lot for your input. I was under the impression that "SINGLE SQL RULE: is true in bothe sender & receiver.

Can I do following in my scenario:

1) Get the input parametrs for SP in from file

2) Configure Receiver JDBC adapter(Synchronous)which executes a STORED PROCEDURE that has some processing logic containing more than one sql statment

3) Send the response from SP to a BPM

And also can you shed some light on Batch processing restrictions mentioned in help.sap.com

http://help.sap.com/saphelp_nw04/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm

Thanks

Venu

[Former Member]

Hi Venu,

>>1) Get the input parametrs for SP in from file

You can pass parameters to the SP from the payload of the message. I am not sure what do you mean by "Getting the parameters for SP in form file"..

>>2 Configure Receiver JDBC adapter(Synchronous)which executes a STORED PROCEDURE that has some processing logic containing more than one sql statment

This is no problem. You can have any number of select /update statements inside the stored procedure. But your stored procedure will not return data to XI and that will not be sent as a response to your BPM

3) Send the response from SP to a BPM

yes.You can have another Stored procedure that have only one select statement to send response to BPM. You may have to define abstract interfaces .

hence for 2) and 3) the message payload would be something like this

-

---

<StatementName1>

<storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName1>

<StatementName2>

<anyName action=” SQL_QUERY”>

<access>SQL-String with optional placeholder(s)</access>

<key>

<placeholder1>value1</placeholder1>

<placeholder2>value2<placeholder2>

</key>

</anyName >

</StatementName2>

-

---

Regards

Arul

PS: Mark usefull answers and for more info about points - read the post "Rules of engagement" at the top of the XI forum

[bhavesh_kantilal]

Venu,

<i> was under the impression that "SINGLE SQL RULE: is true in bothe sender & receiver.</i>

You can do all the above steps using a Stored Procedure.

Just take a look at this blog and the document format,

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

/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

Regards,

Bhavesh

[bhavesh_kantilal]

<i>And also can you shed some light on Batch processing restrictions mentioned in help.sap.com</i>

Can you please tell me where you exactly read this?

Regards,

Bhavesh

[bhavesh_kantilal]

AM not sure I understand... You can do any DB operation you want in your STored Procedure if it is a Receiver JDBC adapter's Stroed Procedure.

In case of Sender, <i>Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.</i>

Regards,

Bhavesh