The DB function enables you to get data from the database based on a condition. It comprises of the following sub-functions.
Mapping Function | Sub-Functions | Description | Example |
|---|---|---|---|
| DB | DBQuery | Returns data from the database, based on a query. | DBQuery( select EmpID from EMP where EMPID=1035, $var1, 'true') returns all the records from the EMP table, where EMPID is 1035. Here, $var1 is the Connection Info variable. If you change the last boolean argument from true to false, only the first record is returned. |
| PreparedQuery | Returns data from the database, based on a query. | PreparedQuery (SELECT EMPID FROM EMP WHERE EMPNAME=? AND EMPCITY=? AND EMPISMALE=?, $oracle_databaseserver, true ,('Smith','Chicago','true')). Here, '=?' is the variable name that you pass in the braces. The '$oracle_databaseserver' is the name of the Connection Info variable. If you change the last boolean argument from true to false, only the first record is returned. |
You can use this sub-function of DB mapping function to get data from the database. The process of using this sub-function is outlined below.
Steps to get data from the database using the 'DB Query' DB Mapping Function
Ensure that the source and target layouts are loaded and all their elements are listed under their respective nodes.
Ensure that a Connection Variable is created for the DB Query and is listed in the Parameters panel.
For information on how to create a Connection Variable, refer to Declaring Connection Info Variable.- Click the target element that you want to map with the DB Query.
The target element gets displayed in the Mapping Expression Area. Click the DB function and select the DB Query sub-function.
Add a constant node with the query for searching data from the database based on the specified condition (For example, select Name from EMP where designation= 'Manager').
Double-click the constant node to add a query.
Use alias name in the query while using SQL function in DB query. For example,
SELECT sql_function AS alias_name
FROM table_name;- Create a link from the output of the constant node to the first input of the DB Query node.
Double-click the required Connection variable under Connection in the Parameters panel.
- Create a link from the output of the Connection Info variable to the second input of the DB Query node.
- Add another constant node for the third argument of the DB Query function.
This constant node can have a Boolean value, true or false. - Create a link from the output of this constant node to the third input of the DB Query node.
- Create a link from the output of the DB Query node to the input of the target element.
This maps the DB Query function to the target element. The defined query is passed to the database where a connection is created using the Connection Info variable. If the Boolean value is set as 'True' then the database fetches all the values that match the query. If the value is 'False', then the database fetches only the first matching value.
Steps to get data from the database using the 'Prepared Query' DB Mapping function
Ensure that the source and target layouts are loaded and all their elements are listed under their respective nodes.
Ensure that a Connection Variable is created for the Prepared Query and is listed in the Parameters panel.
For information on how to create a Connection Variable, refer to Declaring Connection Variable.
- Click the target element that you want to map with the Prepared Query.
The target element gets displayed in the Mapping Expression Area. Click the DB function and select the Prepared Query sub-function.
- Add a constant node with the query for searching data from the database based on the specified condition (For example, Select EMPID from EMP where EMPNAME=? AND EMPCITY=? AND EMPISMALE=?, $oracle_databaseserver, true,('Smith','Chicago','true')).
- Create a link from the output of the constant node to the first input of the Prepared Query node.
Double-click the required Connection variable under Connection in the Parameters panel.
- Create a link from the output of the Connection Variable to the second input of the Prepared Query node.
- Add another constant node for the third argument of the Prepared Query function.
This constant node can have a Boolean value, true or false. - In the fourth argument of the Prepared Query function you can pass values through either as a variable, or as a constant, or as a xpath.
- Create a link from the output of this constant node to the fourth input of the Prepared Query node.
Create a link from the output of the Prepared Query node to the input of the target element.
This maps the Prepared Query function to the target element. The defined query is passed to the database where a connection is created using the Connection Variable. If the Boolean value is set as 'True' then the database fetches all the values that match the query. If the value is 'False', then the database fetches only the first matching value.
Steps to enable DB Query Caching
To enable DB Query Caching in the Data Mapper, go to Open->Preferences->Miscellaneous. A check mark corresponding to the Enable DB Query option indicates caching is enabled successfully.