scenario - soap to jdbc
Author: Uday Kumar S
SYNCHRONOUS SOAP TO JDBC,USING STORED PROCEDURES ENDTOEND WALKTHROUGH
This Web Log is aimed to Explain and provide a clear, step-by-step, end to end, walkthrough to implement a SOAP to JDBC synchronous scenario, executing a given stored procedure and processing the respective response.
The intended audience for this Web Log are the complete beginners, and people that needs a quick guide to implement the JDBC Receiver in their interfaces; With this in mind, I have made a effort to document all the necessary steps to successfully implement this interface.
BUSINESS SCENARIO
The company ABC needs an interface to synchronize their employee’s salary details.
They need to connect to an old SQL Server Database through a SOAP client, and check if the salary information of a given (or several) employee(s) has been updated. ( [RecordStatus] = “U”)
If so, the interface have to retrieve the updated record and update its status to Processed ([RecordStatus] = “P”)
Assumptions, prerequisites and known issues:
SQL Server 2000 is the DB Server used in this scenario.
For convenience we will create our objects in the database [Northwind] (you can create a brand new DB if you feel so).
If your SQL Server 200 is installed on a Windows using XP with SP2, I suggest to disable the Windows Firewall and, if needed, follow the Troubleshoting guide available at the end of this web log.
Naming conventions
To easily identify all the objects related to this interface, we will use a “y” or a capital “Y” at the beginning of all the object names.
SYSTEM LANDSCAPE DIRECTORY SETUP:
1- Go to [Software Catalog]
Create a [New Product] with the Following Details:
Vendor: YABCvendor
Name: YABC
Version: 1
Click on [Create]
Define a [New Software Component] with the Following Details:
Use the previously defined Product and Vendor
Name (upper case): YABC_SW_COMP
Version: 1
Click on [Create]
2- Return to the SLD Home and go to [Technical Landscape]
Create a [New Technical system] with the Following Details:
Technical System Type: Third Party
-> NEXT
System Name: YTSABC
Hostname: YABCSERVER
-> NEXT
Add the product: YABC, 1 of YABCVendor
-> FINISH
3- Return to the SLD Home and go to [Business Landscape]
Create a [New Business System]
Name: YBSABC
-> NEXT
Tech. Syst. Type: Third Party
-> NEXT
System: YTSABC on yabcserver
Logical System Name: YLSABC
-> NEXT
Select your preferred Integration Server
-> FINISH
We have finishing setting up the SLD for our SOAP to JDBC Synchronous Interface.
INTEGRATION REPOSITORY SETUP:
1. Import the SW Component:
First we will import the recently created Software Component
Go to [Tools] in the Menu Bar
-> Transfer from System Landscape Directory -> Import Software Component Versions …
Select [ YABC_SW_COMP, 1 of yabcvendor ]
-> [Import]
-> [Exit]
Fig. 1 Import SC Versions
The Software Component YABC_SW_COMP shall be available in the Objects Tab list.
2. Create The Namespace
Under SC YABC_SW_COMP create the following namespace: http://yabc.com/soap2jdbc/sync
3. Create The Data Types
We need to create four different Data Types for this interface as follows:
SOAP Request : ySoapEmpReq_dt
SOAP Response: ySoapEmpRes_dt
JDBC Request: yJDBCEmpReq_dt
JDBC Response: yJDBCEmpReq_dt_response
Please ensure that the name of the JDBC Response Data type and Message type have the string “_response” in the end, this is obligatory to properly handle the result of the stored procedure execution.
e.g. If your jdbc request message is named [xyz], the response have to be named [xyz_response].
Be very careful with all the names of the elements within the JDBC Request or Response Data Types, by example, the element getEmpDetails, needs to be the exact name of the stored procedure that we will execute in the Database, and the element EmpID is the exact input variable name that we will provide to the Stored Procedure.
Now you can proceed to create the Data Types as shown in the Figures 2, 3, 4 & 5.
Fig 2. SOAP Request : ySoapEmpReq_dt
Fig 3. SOAP Response: ySoapEmpRes_dt
Fig 4. JDBC Request: yJDBCEmpReq_dt
Fig 5. JDBC Response: yJDBCEmpReq_dt_response
4. Create The Message Types
To continue we need to create a Message Type for each Data Type, this step is really simple, because the relation between the DT’s and MT’s is one to one.
The Figure 6 will give a clear idea of the relationship between the DT and MT in this Scenario.
Fig. 6 Data types and Message Types
Tip: While Creating the Message Types, you can drag and drop the desired Data type and Namespace to the Respective Text Boxes; Start with the namespace, and finish with the Name of the Data type, then save the Message Type.
Fig 7. Drag and Drop the DT and the Namespace
5. Create The Message Interfaces
We will require Two Synchronous Message Interfaces, one Outbound Synchronous and one Inbound Synchronous
Configure each Message Interface as is shown in the fires 8 & 9.
Fig 8: Outbound Message Interface
Fig 9: Inbound Message Interface
6. Create The Message Mappings
We will require two message mappings, one for the Request and another for the Response. Figure 10, 10a, 10b and Figure 11 shows the mapping details.
Fig. 10 SOAP Request to JDBC Request
Fig. 10a : Constant [EXECUTE] -> @action
Fig. 10b : Constant [CHAR] -> @type
Click on the link below for more information on the document format for the Receiver JDBC Adapter
http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm
Fig. 11 JDBC Response to SOAP Response
7. Create The Interface Mapping
The last step that we will configure in the Integration Repository is the Interface Mapping.
We will only require one Interface Mapping.
Select the Source Interface and the Target Interface as shown in the Figure 12
Fig 12: Interface Mapping
Now Click on The button [Read Interfaces] and select the corresponding message mappings in the Request and Response Tab.
The figure 12a and 12b shows the message mappings already selected in each tab.
Fig. 12a.
Fig. 12b
We have finishing setting up the IR for our SOAP to JDBC Synchronous Interface.
_______________________________Next page>>
>>