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.

 

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.

 

 

 INTEGRATION DIRECTORY SETUP

 

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

-       [Continue]

-       [Continue]

-       Scroll Down as needed and select the YBSABC Business System.

-       Unselect the Create Communication Channels Automatically box.

-       Click on [Finish]

 

Figure 13 : Selecting Business System

 

3. Create the communication Channels

 

Create the Sender SOAP Comm. Channel as shown in the figure 14.

You only need to be careful when typing the namespace and the [Outbound] 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 [Advanced Mode]

 

Number of Retries... :                          2

Transaction Isolation Level:                 serializable

 

Activate the checkbox [Database Auto-Commit...]

Activate the checkbox [Disconnect from Database ...]

 

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 [Tools]

-       Select [Query Analyzer]

-       Copy and paste the source code 1, available at the end of this web log.

-       Press [F5] 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 CLIENT

The simplest way to test this interface is creating a client within an HTML file and execute it with the Internet Explorer.

 

You don't even need a html editor, just open the good old notepad and paste the code below, save the file and name it "SOAP_TO_JDBC.html"

Ensure that you didn't save a txt file, if so, just change the extension to html.

Open the File with Internet Explorer

Ensure that you Allow Blocked Content...

You can change the EmpID, or add several <requests> at the same time.

Click on [Enviar]    (Send)

 

Fig. 21: Soap Client

 

A pop-up window will appear asking for your XI user and password.

Fill in, and click [ok.]

 

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.

 

 

 

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.

 

 

 

 

 

 

TROUBLESHOTING GUIDE FOR CONNECTIVITY ISSUES TO SQL SERVER.

 

• If 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 [SQLCritUpdPkg_ENU.exe]; you can download the file using the following link: http://www.microsoft.com/downloads/details.aspx?FamilyID=9552d43b-04eb-4af9-9e24-6cde4d933600&displaylang=en

                       

• 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 [SQL-Server-IP-Address] [port#]

 

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

 

SOURCE CODE FOR THE SQL OBJECTS AND THE HTML BASED SOAP CLIENT

 

Source Code 1: Table and Stored Procedure creation.

------------------------------------------------------------

 

CREATE TABLE [Northwind].[dbo].[EMP_DETAILS] (

     [EmpID] [varchar] (50)  NULL ,

     [EmpName] [varchar] (50)  NULL ,

     [Salary] [numeric](18, 0) NULL ,

     [RecordStatus] [varchar] (50)  NULL

) ON [PRIMARY]

GO

 

insert into [Northwind].[dbo].[EMP_DETAILS] VALUES ('1', 'Francisco','175000','U');

insert into [Northwind].[dbo].[EMP_DETAILS] VALUES ('2', 'Sheik','180000','U');

insert into [Northwind].[dbo].[EMP_DETAILS] VALUES ('3', 'Robert','174000','U');

insert into [Northwind].[dbo].[EMP_DETAILS] VALUES ('4', 'Jane','175000','U');

GO

 

CREATE PROCEDURE [dbo].[getEmpDetails]

(

@EmpID [varchar](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

------------------------------------------------------------

<HTML>

<HEAD>

<TITLE>teste</TITLE>

 

<SCRIPT>

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 < 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;

  }

}

 

</SCRIPT>

</HEAD>

<BODY>

<P>URL:<INPUT name="URL" style="WIDTH: 518px; HEIGHT: 22px" size=65 value="http://yourXIserverNameGoesHere:port/XISOAPAdapter/MessageServlet?channel=:YBSABC:YSOAP_SENDER"><BR>

SoapAction:<INPUT name="SoapAction" style="WIDTH: 477px; HEIGHT: 22px" size=65 value=" "><BR>

Request:<BR><TEXTAREA id=soaprequest style="WIDTH: 563px; HEIGHT: 212px" rows=11 wrap=off cols=66>

<?xml version="1.0" encoding="UTF-8"?>

<SOAP:Envelope xmlns:SOAP='http://schemas.xmlsoap.org/soap/envelope/'>

<SOAP:Body>

<ns0:ySoapEmpReq_mt xmlns:ns0="http://yabc.com/soap2jdbc/sync" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">

   <Request>

      <EmpID>1</EmpID>

   </Request>

</ns0:ySoapEmpReq_mt>

</SOAP:Body>

</SOAP:Envelope>

</TEXTAREA><BR><INPUT id=button1 style="LEFT: 356px; TOP: 30px" onclick=getWebServiceResults() type=button value=Enviar name=button1></P>

<P>Response:<BR><TEXTAREA id=soapresponse style="WIDTH: 568px; HEIGHT: 212px" rows=9 wrap=off cols=67></TEXTAREA></P>

</BODY>

</HTML>