Tue. Jun 2nd, 2020

Defining JDBC Lookups Graphically in SAP PI

4 min read

The data-flow editor in the mapping editor has the standard function JDBC lookup with which you can define a mapping-lookup using the JDBC adapter graphically. You can formulate simple SELECT statements using the editor for this function.

Prerequisites
To be able to graphically model the lookup, you must know the table structure of the table that is to be accessed. To use the table structure in the mapping editor, you must import it to the Enterprise Services Repository as an external definition. To do this, the following conditions must be met:

  • The table must be defined in the database.
  • To access the table of the database, a JDBC adapter instance and the relevant database must be running.

Special Prerequisites for Databases
The mapping editor generates Java program code from the graphical definition of the mapping lookup. The SELECT statement that is generated contains the fields you access using the default function in the graphical JDBC lookup. The fields are set in double quotation marks to avoid conflicts with SQL keywords.

Example

In the mapping lookup, access an ORDER field that contains a request number. The SQL SELECT statement however contains an ORDER keyword to set the order. To identify the field in the SELECT statement as identifier for a field it must be set in doubled quotation marks ( “ORDER” ). Otherwise the statement syntax is incorrect.

The database that you want to access using the JDBC lookup must work with double quotation marks for accessing fields in the SQL syntax. This can be configured for the database in some cases: for example, if a JDBC lookup, which is described by the graphical standard function, only functions with a MySQL database when the SQL mode ANSI or ANSI_QUOTES is set.

Procedure

1. Enable Access to the Database Table

– In the Integration Directory, create the JDBC receiver channel for the call to the application system.

Note –  This receiver channel is initially only required for reading the table structure. The developer or consultant can create a different receiver channel later to access the same table in a different system.

– Create an external definition in the Enterprise Repository and import the table structure for the lookup.

2. Define a Parameterized Message Mapping Program

  • In the Enterprise Services Repository, create a message mapping with a source and target structure, or open an existing message mapping for editing.
  • In the mapping editor, switch to the Signature tab page. Create an import message mapping parameter of category Adapter (for example, MMP_JDBC ) and assign it the adapter metadata of the JDBC adapter. The adapter metadata of the JDBC adapter is shipped by using software component SAP BASIS.
  • In the mapping editor, navigate to the Definition tab page and then to the target-field mapping for which you want to define the JDBC lookup.
  • Drag the standard JDBC lookup function to the data-flow editor and define the SELECT statement in the function properties graphically:
  1. Select the import message-mapping parameter for the JDBC adapter from the dropdown list box ( MMP_JDBC from step 4). The message mapping uses this parameter later to transfer the ID of the receiver channel to the function which is to be used for the lookup (see steps 7 and 9).
  2. Call input help and select the external definition from step 2.
  3. Define the SELECT statement.
  • All available fields are displayed in the middle of the standard-function editor. The mapping editor creates an inbound or outbound parameter for the function for each field that you add to the right or left column.
  • Add all fields that you want to use to read a row in the database table to the column on the left. You must assign values to these fields later. To do so, in the data-flow editor, assign them the source fields or result values of other functions. If you do not specify a unique key when you select the fields, multiple rows are read.
  • Add all the fields that you want to apply from the result of the SELECT statement and want to process further to the column on the right. If a SELECT statement selects multiple rows, the function returns a result queue for each result parameter.

4.  If you have selected the relevant checkbox in the function properties of the standard function, the mapping runtime inserts a context change after each value in the                   result queue.

5.  If, later, you want to assign a receiver channel to the message mapping parameter that you assigned the import function parameter to in step 1 (MMP_JDBC), you                         must assign this import parameter to an operation mapping parameter by using a binding for example OM_JDBC.

To execute or test the mapping program and the mapping lookup, perform the following steps in the Integration Directory:

3. Configure a Receiver Channel for Mapping Lookups

  • If you want to use a different JDBC receiver channel to the one in step 1, in the Integration Directory, create a new receiver channel for calling the application system.
  • To transfer the ID of the receiver channel to your message mapping program at runtime, create an interface determination and assign it the operation mapping from step 7. Then, in the interface determination, you can assign the receiver channel to the operation-mapping parameter (in the example OM_JDBC).

Result
You have defined a lookup in your message mapping by using the standard JDBC Lookup function, and have configured it in the Integration Directory. You can now test the message mapping program in the operation mapping.

Leave a Reply

Your email address will not be published. Required fields are marked *