Using DB Function

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 'DBQuery' DB Mapping Function

Ensure that the source and target schemas are loaded and all their elements are listed under their respective nodes.
Ensure that a Connection Info variable is created for the DBQuery and is listed in the Parameters Panel.

For information on how to create a Connection Info variable, refer to Declaring Connection Info Variable.
Double-click the target element that you want to map with the DBQuery. It is displayed in the Mapping Graph Area.
Click the DB function and select the DBQuery sub-function. A DBQuery node is displayed in the Mapping Graph Area.
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').
Use alias name in the queries 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 DBQuery node.
Double-click the required Connection Info variable under Connection Info in the Parameters Panel. The selected Connection Info variable node is displayed in the Mapping Graph Area.
Create a link from the output of the Connection Info variable to the second input of the DBQuery node.
Add another constant node for the third argument of the DBQuery function. This is a Boolean value as true or false.
Create a link from the output of this constant node to the third input of the DBQuery node.
Create a link from the output of the DBQuery node to the input of the target element.
Click the Apply Mapping () button. This maps the DBQuery 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. If you shift focus to another node or click any of the Mapping Rules, Textual Rules, Node, Properties, XSL or Debugger tabs, without applying the mapping, an alert message is displayed.
Click No to apply the mapping and shift focus to the other object. If you click Yes, then the mapping activity is cleared and is replaced by the previous mapping in the Mapping Graph Area and the focus is shifted to the other object.

At times, the mapping is too large and the applet memory may exceed 256M. In such cases, the mapping will fail and return an error of insufficient memory. You can overcome this situation by increasing the applet memory. For more information, refer to Appendix A.

Steps to get data from the database using the 'PreparedQuery' DB Mapping Function

Ensure that the source and target schemas are loaded and all their elements are listed under their respective nodes.
Ensure that a Connection Info variable is created for the PreparedQuery and is listed in the Parameters Panel.

For information on how to create a Connection Info variable, refer to Declaring Connection Info Variable.
Double-click the target element that you want to map with the PreparedQuery. It is displayed in the Mapping Graph Area.
Click the DB function and select the PreparedQuery sub-function. A PreparedQuery node is displayed in the Mapping Graph Area.
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 PreparedQuery node.
Double-click the required Connection Info variable under Connection Info in the Parameters Panel. The selected Connection Info variable node is displayed in the Mapping Graph Area.
Create a link from the output of the Connection Info variable to the second input of the PreparedQuery node.
Add another constant node for the third argument of the PreparedQuery function. This is a Boolean value as true or false.
In the fourth argument of the PreparedQueryfunction 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 third input of the PreparedQuery node.
Create a link from the output of the PreparedQuery node to the input of the target element.
Click . This maps the PreparedQuery 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. If you shift focus to another node or click any of the Mapping Rules, Textual Rules, Node, Properties, XSL or Debugger tabs, without applying the mapping, an alert message is displayed.
Click No to apply the mapping and shift focus to the other object. If you click Yes, then the mapping activity is cleared and is replaced by the previous mapping in the Mapping Graph Area and the focus is shifted to the other object.

At times, the mapping is too large and the applet memory may exceed 256M. In such cases, the mapping will fail and return an error of insufficient memory. You can overcome this situation by increasing the applet memory. For more information, refer to Appendix A.

Steps to enable DB Query Caching

To enable DB Query Caching in the Data Mapper, go to Actions > Enable DB Query Caching. A check mark corresponding to the Enable DB Query option indicates caching is enabled successfully.