Skip to Content

There have been several questions on the XI forum asking “How to configure a JDBC Receiver Adapter in a Synchronous Fashion for a SELECT query”.

This weblog is an attempt to answer that question.

Scenario:

Let’s say, we have an Employee Table EMPTEST with EmpNo and Name. We use a File to pass the EmpNo and the Operator to the JDBC adapter. This will dynamically select the data that satisfies the query from the database and finally push this data back to a file.

Example:
Consider the Query, Select EMPNO, NAME from EMPTEST where EMPNO > 2
In this case, the input file will contain the value for EmpNo =2 and the value for the operator as =GT (greater than).

Steps


1.The sample Table EMPTEST with data populated

image

2.The Source File Data type

image

We will be passing the EmpNo and Operation from the Input File. Note that “Request” element has an occurrence 1…unbounded.  So, if needed, we can make multiple Select Calls using a Single JDBC adapter.

3.The Datatype for the JDBC request message & response message


image

image

4.The Datatype for the Target File

image

The above is the format of my File. For every SELECT message in the Source, a response tag is created.

5.Creating the Message Type for the Request and Response JDBC.

Message Type for JDBC request

image

Message Type for JDBC response

image

This is the trickiest part of the configuration and where most of us go wrong. The JDBC adapter when sending its response back will create a Message Type of the same name as the request message with a _response appended to it.

So, if your Request message type is going to have the name, MT_JDBC_SELECT_JDBC_REQUEST then, the message type for the response that is to be created will have to be MT_JDBC_SELECT_JDBC_REQUEST_response .

6.Creating the Mapping between the Source File and the JDBC Request Message

image

Map from the Source to the Target as shown above.

  • The fields EMPNO, NAME should be mapped to blank constants.
  • ACTION is mapped to constant SELECT and TABLE will contain the name of the table.

7.Create the mapping for the JDBC response to the File response message.

image

Further Reading : Document Format For Receiver JDBC Adapter

To report this post you need to login first.

14 Comments

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

  1. Bhavesh,

    This feature is in use since the early days of XI3.0. There are plenty of forum posts and Blogs addressing on how to do basic select. But there are still many unaddressed areas like how to select or push binary large objects. XI now allows
    cursors (only oracle).

    Keep up the good work and thanks for blogging.

    Cheers,
    Naveen

    (0) 
    1. Bhavesh Kantilal Post author
      Hi Naveen,
      Thanks for the input.
      XI allows Oracle Cursors? From which SP is this applicable from? If this is the case, then it implies, an Oracle Stored Procedure can now be used in the Sender JDBC adapter right?

      Regards,
      Bhavesh

      (0) 
      1. SHAIK AZMATHULLA

        Hi Bhavesh ,

        Great Blog ..

        I am facing some Issues in JDBC receiver side configuration and ESR part . and i am unable to find full blog document too .

        Could you please share the document for the following mail id Please .

        azmathip@gmail.com.

        Regards,

        Azmath.

         

        (0) 
  2. Aarthi R
    Hi Bhavesh,

    Your blog provided me excellent information in a step by step manner. I was successfull in using your blog for one of my requirements.Good job.

    Keep Bloging
    -Aarthi

    (0) 
  3. Ravi Siddamsettiwar
    Hi,

    It is a good blog, but it is incomplete, you should have atleast completed with IR part, the message interface is not included in your bolg. It would be gr8 if you can do it now.

    with regards,
    Ravi

    (0) 
  4. Ritesh patel
    It’s excellent explaination.
    I’ve similar scenario

    ” SOAP Request (UserId) comes to PI, PI calls the external Database by using JDBC and gets relevant data from Database based on UserId. PI maps these data (which comes from database) with BAPI and maps the response of BAPI with Webservice.

    Now I’ve questions:

    How can I map JDBC response with BAPI structure?

    what are the steps required in BPM to perform this scenario (I am new in BPM)

    Here there are 4-5 different SAP-HR systems, which can be determined based on the data (http link for perticular HR system) comes from external database. so How can i determine this SAP system? (each system use the same BAPI structure)

    also there is a value mapping when BAPI sends response back to SOAP sender. how can I perform that?

    If anyone has relevant documents for this scenario please provide me. It will be great help.”

    Regards
    Chintan Patel

    (0) 
  5. naresh j
    Very good explanation. It helped me a lot. But if possible can u please explain me how to deal with stored procedures instead tables. Any one can please help..?
    (0) 

Leave a Reply