IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Building mediation flows > Mediation primitives and nodes
Example: Database Lookup mediation primitive
The Database Lookup primitive searches values from a database and stores them in the message. This example shows you how to use the Database Lookup primitive in the Mediation Flow editor.
In the example, we want to retrieve an employee's salary from the employee database. Let's assume the following:
- The client application passes in the employee's serial number (049728) into serialNumber field of the message body.
- There is an employee record in the EmployeeDatabase with serialNumber = '049728' and salary = '$50,000'.
- We want to retrieve the employee's salary from the database, using the serialNumber field of the message to locate the record in the database.
- We then want to retrieve the value of the SALARY column for the employee's record from the database and put it in the message body.
This is the corresponding SQL statement:
select SALARY from EMPLOYEE_TABLE where SERIAL_NUMBER = '049728'
The result, '50000' of type long will be stored in the salary field in the message body.
Specify these properties for the primitive in the Details page of the Properties view.
In the Data source field, enter the JNDI name of the data source. In the example, jdbc/sample/EmployeeDatabase
In the Table field, enter the name of the database table, including the schema name. In the example, myschema.EMPLOYEE_TABLE
In the Search column field, enter the name of the database column against which the search string is matched. In the example, this column is SERIAL_NUMBER
In the Search location field, enter the XPath location of a field in the message body. The value of this field is the search string. Click Edit to launch the XPath Expression Builder to build the required XPath expression. In this case that is the value of the serialNumber (049728) from the message body.
In the table, click Add to define the database column from which the value will be retrieved, the type of information it is, and the place in the message where the retrieved value is to be stored. Each data element has three properties:
- Column specifies the name of the database column from which to obtain the element value. In the example, this is SALARY.
- Type specifies the type of the element value. In the example, the type is int.
- Target location specifies an XPath 1.0 expression describing the path location of the message element where the database value is stored. The XPath expression must evaluate to a single element in the message. In the example, the salary value retrieved will be stored in the message body, in the salary field.
See Database Lookup primitive reference.