Page Comparison

The DB function enables you to get data from the database based on a condition. It comprises of the following sub-functions. 

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

1. Ensure that the source and target layouts are loaded and all their elements are listed under their respective nodes.

2. Ensure that a Connection Variable is created for the DB Query and is listed in the Parameters panel.

   Info
   For information on how to create a Connection Variable, refer to Declaring Connection Info Variable.

   
   

3. Click the target element that you want to map with the DB Query. 
   The target element gets displayed in the Mapping Expression Area.

4. Click the DB function and select the DB Query sub-function. 

   

5. 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').

6. Double-click the constant node to add a query.

   Info
   Use alias name in the query while using SQL function in DB query. For example,  SELECT sql_function AS alias_name FROM table_name;

   
   

7. Create a link from the output of the constant node to the first input of the DB Query node.

8. Double-click the required Connection variable under Connection in the Parameters panel.  

9. Create a link from the output of the Connection Info variable to the second input of the DB Query node.
10. Add another constant node for the third argument of the DB Query function. 
    This constant node can have a Boolean value, true or false.
11. Create a link from the output of this constant node to the third input of the DB Query node.
12. 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

1. Ensure that the source and target layouts are loaded and all their elements are listed under their respective nodes.

2. 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.

3. Click the target element that you want to map with the Prepared Query. 
   The target element gets displayed in the Mapping Expression Area.

4. Click the DB function and select the Prepared Query sub-function.  
   

5. 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')).
6. Create a link from the output of the constant node to the first input of the Prepared Query node.

7. Double-click the required Connection variable under Connection in the Parameters panel.  

8. Create a link from the output of the Connection Variable to the second input of the Prepared Query node.
9. Add another constant node for the third argument of the Prepared Query function. 
   This constant node can have a Boolean value, true or false.
10. 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.
11. Create a link from the output of this constant node to the fourth input of the Prepared Query node.

12. 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.