JDBC adapter Call Stored Procedures

Scenario:

Http Client sends a Synchronous request to a DB. The stored Procedure is executed and the correspoing role of the user is sent back to the HTTP Client.



List Of steps:

1. Below is a sample DB Structure which is used in the scenario.
   
   
   
   
2. sp_GetRole is the name of the Stored Procedure used.
   
   Input parameters: Username, Password
   
   Output parameters: Role
   
   CREATE PROCEDURE [dbo].[sp_GetRole] @UserName char(10), @Password char(10) AS SELECT URole FROM LoginMaster WHERE UName = @UserName and UPassword = @Password GO
   
   
   
   
3. Create the required Data types, Message Types & message interfaces required for Request Mapping. U can find the Stored Procedure Message Type on the Left hand side of request mapping shown below.
   
   
   
   
4. Map the necessary fields.
   
   EXECUTE-->action
   
   true-->isInput
   
   CHAR-->type (CHAR is the Data type of UName & UPassword). Refer to the DB structure in Fig 1.
   
   
5. Create the required Data types, Message Types & message interfaces required for Response mapping. U can find the response of Stored Procedure Message Type on the Right hand side of response mapping shown below.
   
   
   
   
   
   
6. Create the required Interface mapping
   
   
   
   
   
   
7. Go to Configuration Time and create the necessary Configuration time Objects. Receiver Determination, Interface Determination & Mapping, Receiver Agreements.
   
   
8. In JDBC Receiver Channel, key in the following parameter in connection textbox. jdbc:microsoft:sqlserver://<hostname>:1433;DatabaseName=<dbname>;SelectMethod=cursor
   
   
   
   
9. Execute the Scenario by sending the request, and you will get the desired output.

https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/1671