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
2.The Source File Data type
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
4.The Datatype for the Target File
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
Message Type for JDBC response
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
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.
Further Reading : Document Format For Receiver JDBC Adapter
15 Comments
