The ultimate site for SAP XI certification and tutorial resources. Submit tutorials/scenarios and get assured gifts!!! For more info click here...

gift

scenario - soap to jdbc

Author: Uday Kumar S

 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

1

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

2

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

3

 4. Create the Receiver Agreement, Sender Agreement, Interface Determination and Receiver Determination.

 The following steps should be very straightforward.

Refer to the figures 16, 17, 18 & 19 if you have problems setting up the ID components.

 Fig. 16: Receiver Agreement

4

Fig. 17: Sender Agreement

  5

Fig. 18: Interface Determination

6

Fig. 19 Receiver Determination

  7

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

  8

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

  9

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

Fill in, and click [ok.]

Fig 22. Pop-up Window

10

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

  11

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.

 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

12

Fig 25: Failed telnet at the port 3355

13

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=:kone_bussys: sender_soap_cc"><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://SOAP2JBBC_UK " 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> ___________

<<Prev page

Cool !!!View other tutorials!________________________Submit a tutorial