Skip to Content

             I often see threads in XI Forum, developers troubling with JDBC Document formats usage. Though there were already a couple of good blogs in SDN for this topic, but each blog explains a specific document format. This blog deals with all JDBC document formats usage in a simple and constructive way by reducing the design time efforts and errors in Integration Repository. Hence blogging my ideas and experiences.               

This blog is targeted for both beginners and Intermediates. I presume that all the necessary JDBC drivers have been deployed in J2EE engine in hand. If not, ask your BASIS person to deploy the same using this article ref: {code:html}How To Install and Configure External Drivers for JDBC & JMS Adapters{code}                 

The main intention of this blog is to quick check the document formats usage for JDBC Adapter in Receiver mode (depending upon your project requirement) before we can actually design the XML structures (Data Types, Message Types for JDBC Request and Response) in Integration Repository (IR), since design part in IR requires more efforts and time. So it’s always a good practice if we test the document formats before we can actually design the JDBC request, response Data Types and Message Types in Integration Repository. This procedure also eliminates maximum possible errors, such as Data Type and Message Type structural changes due to mapping errors and JDBC Adapter format conversion errors.   

Hence, I will be using the following blog concept for testing all document formats.   How to send any data (even binary) through XI, without using the Integration Repository by William Li: How to send any data (even binary) through XI, without using the Integration Repository 

Check the Document Formats for the Receiver JDBC Adapter from SAP:

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Scenario:   I will consider a simple scenario HTTP to JDBC (Synchronous fashion), as it will take less time to configure and test (No Sender Agreement).  

  I am using Microsoft SQL server 2000 for this scenario. You can use any other database according to your requirement as JDBC document formats are XML neutral i.e., independent of Data Base.  Execute the following SQL Scripts in the Microsoft SQL server 2000 database. 

create table OPERATIONS (EMPNAME varchar(40), EMPNO varchar(4));

insert into OPERATIONS values (‘SDN1′,’1001’);

insert into OPERATIONS values (‘SDN2′,’1002’);

insert into OPERATIONS values (‘SDN3′,’1003’); 

CREATE PROCEDURE  (dbo).[sp_Operations] AS

SELECT * FROM OPERATIONS

GO       

Ok then, let’s directly start with the Integration Directory. Create a new configuration scenario say CS_HTTPToJDBC. Then create a common business service for both HTTP sender and JDBC receiver say BS_JDBC. Now create a communication channel for JDBC connectivity say CC_JDBCReceiver and provide the following driver information.        

JDBC Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver     

Connection: jdbc:microsoft:sqlserver://<HOSTNAME>:1433;DatabaseName=<xxxx>;SelectMethod=cursor

                                   JDBC Receiver Channel

image     

Let’s create a new receiver determination with the following information for HTTP Sender,                                       

Service: BS_JDBC                              

Interface: MI_HTTP                                         

Namespace: http://jdbc.com   

Remember we can give any names for Message Interface and Namespace. 

Click on Create push button. In the next screen type BS_JDBC in Service Text Box under Configured Receivers then click Save. 

Now create interface determination and receiver agreement. In the interface determination let’s use the same names for Inbound Message Interface and namespace which we have used for HTTP Sender. Of course, we can give any other names.    

Interface Determination

 

Receiver Agreement

Finally when we open up the receiver determination editor, it should look as followsReceiver Determination.jpg

And finally the Selected Scenario should look like ………

ID Configuration.jpg

Let’s now start with SELECT document format i.e., <StatementName4> as per the standard receiver JDBC document formats.

SELECT PAYLOAD

<MT>
<SELECT_STATEMENT>
<dbTableName action="SELECT">
<table>OPERATIONS</table>
<access>
<EMPNAME/>
<EMPNO/>
</access>
<key>
<EMPNAME compareOperation="NEQ">*</EMPNAME>
</key>
</dbTableName> 
</SELECT_STATEMENT>
</MT>

To test this payload, I will be using HTTP Client provided by SAP:(https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/66dadc6e-0a01-0010-9ea9-bb6d8ca48cc8), since it provides us the most flexibility in testing all JDBC document formats by just changing the Request payload. Please read the above link to use the java script based HTTP client. Provide the following information in the HTTP client which is opened in browser. Click on Send push button.

HTTPClient.jpg

Now if everything goes fine, we should get the following payload as SELECT response.

Result:  

<?xml version="1.0" encoding="utf-8"?>
<MT_response >
<SELECT_STATEMENT_response>
            <row>
                        <EMPNAME>SDN1</EMPNAME>
                        <EMPNO>1001</EMPNO>
            </row>
            <row>
                        <EMPNAME>SDN2</EMPNAME>
                        <EMPNO>1002</EMPNO>
            </row>
            <row>
                        <EMPNAME>SDN3</EMPNAME>
                        <EMPNO>1003</EMPNO>
            </row>
</SELECT_STATEMENT_response>
</MT_response>

If any errors, correct the request payload and send again until the desired output comes up.

So once we can confirm that JDBC request and response payloads are valid, we can go ahead with repository part by creating necessary structures for JDBC request and response (and any other structures pertaining to other adapters such as FILE, JMS etc. as per the given requirement).

            Let’s consider the above JDBC select request payload again. Instead of <MT> & </MT> tags we can have any other meaningful tags according to the requirement. Say for e.g., <SELECT> & </SELECT>. Of course, when we use <SELECT> & </SELECT> tags for the request payload we have to make sure that <SELECT_response> & </SELECT_response> tags are created for the JDBC response Message Structure; otherwise we get a mapping error if we have done any mappings in between JDBC response and some other structure expected by the third party (say FILE, JMS systems etc).

For further help in creating structures read the following blog by Bhavesh Kantilal:

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

Now you can go ahead testing other document formats as well. I will leave this testing up to the reader. Simply Change the payload in HTTP Client and test.

INSERT PAYLOAD

<MT>
<INSERT_STATEMENT>
<dbTableName action="INSERT">
<table>OPERATIONS</table>
<access>
<EMPNAME>SDN4</EMPNAME>
<EMPNO>1004</EMPNO>
</access>
<access>
<EMPNAME>NONEMP</EMPNAME>
</access>
</dbTableName>
</INSERT_STATEMENT>
</MT>


UPDATE (or) UPDATE_INSERT PAYLOAD  

<MT>
<UPDATE_INSERT_STATEMENT>
<dbTableName action="UPDATE_INSERT">
<table>OPERATIONS</table>
<access>
<EMPNAME>SDN4_UPDATE</EMPNAME>
<EMPNO>1004</EMPNO>
</access>
<key1>
<EMPNAME>SDN4</EMPNAME>
<EMPNO>1004</EMPNO>
</key1>
<key2>
<EMPNO>1004</EMPNO>
</key2>
</dbTableName>
</UPDATE_INSERT_STATEMENT>
</MT>

DELETE PAYLOAD

<MT>
<DELETE_STATEMENT>
<dbTableName action="DELETE">
<table>OPERATIONS</table>
<key1>
<EMPNAME compareOperation="EQ">NONEMP</EMPNAME>
</key1>
</dbTableName>
</DELETE_STATEMENT>
</MT>

STOREDPROCEDURE PAYLOAD

<MT>
<STOREDPROCEDURE_STATEMENT>
<storedProcedureName action="EXECUTE">
<table>dbo.sp_Operations</table>

</storedProcedureName >
</STOREDPROCEDURE_STATEMENT>
</MT>

SQL_QUERY (or) SQL_DML

<MT>
<SQL_QUERY_OR_DML>
<anyName action="SQL_DML">
<access>UPDATE OPERATIONS SET EMPNAME='$ENAME$', EMPNO='$ENO$' WHERE EMPNAME='$TEST$'</access>
<key>
<ENAME>SDN1_DML</ENAME>
<ENO>1001</ENO>
<TEST>SDN1</TEST>
</key>
</anyName>
</SQL_QUERY_OR_DML>
</MT> 


Once you finish up reading & implementing this blog, you will realize that this concept will be useful for most of the other scenarios (apart from JDBC) for a quick R & D.



To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

    1. Farooq Farooqui
      It will be more efficient if you add PI 7.1 section also. What you mentioned it applicable upto 7.0

      You can add and extra section and can pass necessary link of Blog, Wiki and Aticle related to JDBC of PI 7.1

      Thanks
      Farooq

      (0) 
  1. Prateek Raj Srivastava
    As per my understanding, this would help in understanding the response structures only?
    You have already created the request structures for most of the scenarios.

    In my opinion this won’t be the best practice to follow in all the cases. There are certain cases in huge projects, wherein IR and ID are handled by different teams (may be with different timelines). In that case, ID team communicating the actual datatype structure to IR team would be a bit unmanageable.
    But this is just my opinion 🙂

    Best Regards,
    Prateek

    (0) 

Leave a Reply