h1. *SYNCHRONOUS SOAP TO JDBC, USING STORED
PROCEDURES. *
END TO END
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.
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. (*
= “U”*)
If so, the interface have to
retrieve the updated record and update its status to Processed ( = “P”)
Assumptions, prerequisites and known issues:
convenience we will create our objects in the database (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.
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* *
Create a *[New
Product] *with the Following Details:
Vendor:
YABCvendor
Name:
YABC
Version:
1
Click on
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
2- Return to the SLD Home and go
to
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
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.
1. Import the SW Component:
First we will import the recently created
Software Component
Go
to in the Menu Bar
->
Transfer from System Landscape Directory ->
Select
->
->
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 , the response have to be named .
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
!https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig2.JPG|height=378|alt=Fig 2. SOAP Request : ySoapEmpReq_dt|width=694|src=https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig2.JPG!
Fig 3. SOAP Response: ySoapEmpRes_dt
!https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig3.JPG|height=309|alt=Fig 3. SOAP Response: ySoapEmpRes_dt|width=433|src=https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig3.JPG!
Fig 4. JDBC Request: yJDBCEmpReq_dt
!https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig4.JPG|height=309|alt=Fig 4. JDBC Request: yJDBCEmpReq_dt|width=437|src=https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig4.JPG!
Fig 5. JDBC Response: yJDBCEmpReq_dt_response
!https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig5.JPG|height=314|alt=Fig 5. JDBC Response: yJDBCEmpReq_dt_response|width=462|src=https://weblogs.sdn.sap.com/weblogs/images/251764026/Fig5.JPG!
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 ->
@action
Fig. 10b : Constant -> @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 | 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 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.
First of
all, in the menu bar, select Enviroment
-> Clear SLD Data Cache
This will
ensure that our Business System is available for selection.
1. Create a New Configuration Scenario
In the Menu
Bar Select Object -> New -> Configuration Scenario
Name it as
: YSOAP_TO_JDBC_SYNC
2. Assign the Bussines System
-
Open
the Recently Created Config. Scenario
-
Right
Click on Business System
-
Select
Assign Bussines System
-
-
-
Scroll
Down as needed and select the YBSABC
Business System.
-
Unselect the Create Communication Channels
Automatically box.
-
Click
on
Figure 13 :
Selecting Business System
Create the
Sender SOAP Comm. Channel as shown in the figure 14.
You only
need to be careful when typing the namespace and the Interface Name.
Fig 14:
YSOAP_SENDER
Now create
the Receiver JDBC Comm. Channel with the following attributes:
NAME:
YJDBC_RECVR
JDBC
Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver
Connection:
jdbc:microsoft:sqlserver://127.0.0.1:3333;DatabaseName=Northwind;
(The IP
127.0.0.1 is just a sample, replace it with the IP of your SQL Sever; Also
ensure that the port that you are using is correct)
User: I'm using the almighty sa user, you might need to use a
different user for your connection.
Password: The *correct
password* for the above user.
Check *
* *
Number of
Retries... : * 2*
Transaction
Isolation Level: serializable
Activate the checkbox *
Activate the checkbox
The Figure
15 is trimmed to show only the meaningful configuration.
Fig. 15:
YJDBC_RECVR
*4. Create the Receiver Agreement, Sender
Agreement, Interface Determination and Receiver Determination.*
The
following steps should be very straight forward.
Refer to
the figures 16, 17, 18 & 19 if you have problems setting up the ID
components.
Fig. 16:
Receiver Agreement
Fig. 17:
Sender Agreement
Fig. 18:
Interface Determination
Fig. 19 Receiver Determination
With this,
we have finished our Integration Directory Configuration.
UNIT TESTING
1. CREATE THE SQL SERVER OBJECTS
We need to
create the Table EMP_DETAILS and the
stored procedure getEmpDetails
To make
this simple, copy and paste the source code 1, avaialble at the end of this web log, and execute it to create
all the objects and populate the table in the database.
Follow the
procedure described below:
-
Open
the SQL Server Enterprise Manager
-
In
the menu bar select
-
Select
-
Copy
and paste the source code 1, available at the end of this web log.
-
Press
key or Click on the Run button.
Figure 20
shows the Table with the rows already inserted
Fig. 20:
EMP_DETAILS Table
2. CREATE THE SOAP CLIENTClick on (Send)
Fig. 21:
Soap Client
A pop-up
window will appear asking for your XI user and password.
Fill in,
and click
Fig 22.
Pop-up Window
The
Response message will appear in the Response box, scroll to the right and you
will be able to see the message body.
If any
error happens, it will be shown in the Response box aswell.
Fig. 23:
Response message
We have Successfully created all the XI Objects that are needed for the SOAP to JDBC Sync. Interface.
At the end of this weblog you will find the source code needed for the unit testings and a brief conectivity trobuleshoting guide.
| !http://lh3.ggpht.com/lemelgar/SCB67cAphDI/AAAAAAAAARU/7H-F0VUMj94/LuisMelgar.jpg|height=108|alt=|wid...! |
Luis Melgar is a System and Computers Engineer, Certified Oracle
Database Administrator and is currently working as an XI Consultant for
AES Corporation.
He have been working in different IT projects since 1995.
h2. *TROUBLESHOTING GUIDE FOR *CONNECTIVITY ISSUES TO SQL SERVER.
your SQL Server 2000 is installed on Windows XP with SP2, and you cant connect to the DB from XI, you might need to download and execute the
Critical Update file ;
you can download the file using the following link: http://www.microsoft.com/downloads/details.aspx?FamilyID=9552d43b-04eb-4af9-9e24-6cde4d933600&displa...
- The
Port used by SQL server in this Walkthrough is the 3333, you can change the
default port as follows:
Open
the Enterprise Manager.
Right
Click on the Desired SQL Server Instance: e.g. (local)
Then
select: Properties -> General Tab -> Network Configuration -> If
needed enable the TCP/Protocol -> Properties
Then
you can change the port number
Restart
the Database Instance to apply the changes.
To test the
connectivity to the SQL server, open an telnet session from the XI server as
follows:
telnet
e.g.
telnet 10.248.99.99 3333
Where
10.248.99.99 is the ip address of the SQL server, and 3333 is the port where
the DB Instance is listening.
The
expected result is a instant Blank Screen, nothing more, nothing less, as shown in the Figure 24... if any
error message appears after a few seconds (like in the Fig 25), that means that there is no network
connectivity in the given port ; Check the steps mentioned at the begginging of this guide or contact a network
administrator if possible.
Fig 24: Successful Telnet to 127.0.0.1 at port 3333
Fig 25: Failed telnet at the port 3355
h2. *SOURCE CODE FOR THE SQL OBJECTS AND THE HTML
BASED SOAP CLIENT*
Source Code 1: Table and Stored Procedure creation.
----
CREATE TABLE .[dbo].[EMP_DETAILS] (
(50) NULL ,
(50) NULL ,
(18, 0) NULL ,
(50) NULL
) ON
GO
insert into .[dbo].[EMP_DETAILS] VALUES
('1', 'Francisco','175000','U');
insert into .[dbo].[EMP_DETAILS] VALUES
('2', 'Sheik','180000','U');
insert into .[dbo].[EMP_DETAILS] VALUES
('3', 'Robert','174000','U');
insert into .[dbo].[EMP_DETAILS] VALUES
('4', 'Jane','175000','U');
GO
CREATE PROCEDURE .[getEmpDetails]
(
@EmpID (10)
)
AS
SELECT EmpID, EmpName, Salary
FROM EMP_DETAILS
where RecordStatus = 'U' and EmpID = @EmpID;
Update EMP_DETAILS
SET RecordStatus='P'
where EmpID = @EmpID;
GO
----
Source Code 2: HTML BASED SOAP CLIENT
----
function
getWebServiceResults() {
window.setTimeout(getWebServiceResultsAfterInit,
1);
}
var objXmlDoc;
var objHttp;
function
getWebServiceResultsAfterInit() {
// Must be using IE for this to work
if (window.ActiveXObject) {
// Create the XML HTTP object
var bHttp = false;
var aszHttpProgIDs = [
"MSXML2.XMLHTTP.4.0",
"MSXML2.XMLHTTP.3.0",
"MSXML2.XMLHTTP",
"Microsoft.XMLHTTP" ];
for (var i = 0; !bHttp && i <<br />aszHttpProgIDs.length; i++) {
try {
objHttp = new ActiveXObject(aszHttpProgIDs[i]);
bHttp = true;
} catch (objException) {
// error handling elided for clarity
}
}
// If we failed to create both objects,
then throw an exception and return
if (!bHttp) {
throw "MSXML not found on your
computer.";
return;
}
objHttp.onreadystatechange =
getWebServiceResultsAfterLoad
objHttp.open("post", URL.value);
objHttp.setRequestHeader("Content-Type", "text/xml;
charset=utf-8");
objHttp.setRequestHeader("SOAPAction",
SoapAction.value);
objHttp.send(soaprequest.value);
}
}
function
getWebServiceResultsAfterLoad() {
if (objHttp.readyState == 4) {
// Get the return envelope
soapresponse.value = objHttp.responseText;
}
}
URL:<INPUT
name="URL" style="WIDTH: 518px; HEIGHT: 22px" size=65
value="http://yourXIserverNameGoesHere:port/XISOAPAdapter/MessageServlet?channel=:YBSABC:YSOAP_SENDER"></ns0:ySoapEmpReq_mt>
id=button1 style="LEFT: 356px; TOP: 30px"
onclick=getWebServiceResults() type=button value=Enviar
name=button1>
Response:
id=soapresponse style="WIDTH: 568px; HEIGHT: 212px" rows=9 wrap=off
cols=67>
6 Comments
