DB Data Common Block

The DB Data block is available for both routing and voice applications. Use for connecting to a database and retrieving/manipulating information from/in a database. This block uses a connection profile to read database access information. It accepts a SQL query or a Stored Procedure call, which can be defined using the Using the Query Builder or Stored Procedure Helper.  It can also use a SQL script file. Note: When using the DB Data block to connect to and query information from an Oracle database, some connections may remain in the TIME_WAIT state. If you encounter this situation, use connection pooling in order to avoid exhausting the number of allowed Oracle connections. This block acts as a data source for the DB Prompt and DB Input blocks (available only in callflows).  An Entry block user variable can also be used to access the results of a Stored Procedure call specified in a DB Data block for both voice and routing applications.

Important

The Looping block can work with the DB Data block. For example, you can use the Looping block to Iterate over a data set returned by the DB Data block to map values returned from a database query to application variables.

Manually Configuring Context Attributes for Memory Leaks

At times, possible memory leaks can occur in Tomcat when the DB Data block is used in a strategy. To avoid these possible leaks during runtime, set the following context attributes in the context.xml file (<tomcat-folder>/conf/context.xml) to true:

• clearReferencesStopThreads
• clearReferencesStopTimerThreads
• clearReferencesThreadLocals

Example:

<!-- The contents of this file will be loaded for each web application -->
<Context clearReferencesStopThreads="true" clearReferencesStopTimerThreads="true" clearReferencesThreadLocals="true">
<!-- Default set of monitored resources. If one of these changes, the -->
 <!-- web application will be reloaded. -->
 <WatchedResource>WEB-INF/web.xml</WatchedResource>
 <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
 <!--
 <Manager pathname="" />
 -->
</Context>

Also see: Working with Database Blocks. The DB Data block has the following properties:

Name Property

Find this property's details under Common Properties for Callflow Blocks or Common Properties for Workflow Blocks. Note: If you rename a DB Data block, its corresponding SQL statement file in the db folder will not be updated and will not be valid until you generate code again.

Block Notes Property

Find this property's details under Common Properties for Callflow Blocks or Common Properties for Workflow Blocks.

Connection Profile Property

The Connection Profile property allows you to select a previously-created database Database Connection Profiles that specifies database details for this DB Data block. If you have not created a connection profile, open the Connections Profile editor as follows:

1. Under Value, click the down arrow.
2. Select Create New Profile Using Editor...

Refer to the topic Database Connection Profiles for instructions. To select a connection profile for your database query:

1. Select the Connection Profile row in the block's property table.
2. Select the connection profile to use for this query.

Connection Properties Property

The Connection Properties property allows you to override the parameters in connection profile during runtime. The properties that can be overriden are Hostname, Password, Port, Database, Username and other Custom Parameters. Variable mapping can be configured in the dialog box provided for the property. To define the variable mapping for Connection Parameters:

1. Click the button to open the Connection Properties variable mapping dialog.
2. Dialog displays the parameter name and value in connection profile. Select the system variable in the drop down combo against each property.
3. Click OK

Connection String Property

The Connection String Property allows you to define the value of Connection String that need to be used at Runtime. If this property is specified, the parameters from Connection Profile is ignored. To define this property enter either literal value or select system variable from the combo provided for the property.

Timeout Property

The Timeout property defines the length of time in seconds that the voice application will wait for query execution to complete. To provide a timeout value:

1. Select the Timeout row in the block's property table.
2. In the Value field, type a timeout value, in seconds.

The default value (20 seconds) of this property is used if not specified explicitly.  Disable the timeout by setting to -1. If the query takes longer than this specified time to complete the error.com.genesyslab.composer.dbtimeout exception is thrown. In order to select a query type, the Connection Profile property must be set.

Query Type Property

To define a query type:

1. Select the Operation Type row in the block's property table.
2. Select one of the following:

• SQLQuery
• SQLScriptFile
• StoredProcedure

Based on the value selected for Operation Type, the specified value is used and some properties are not used.

Query Property

The Query property opens the Query Builder in which you can visually build the database query. Note: The Query property and Query File property are mutually exclusive.  If both are entered, then the Query File property takes precedence over the query defined in the Query property. To define a query:

1. Select the Query row in the block's property table.
2. Click the button to open the Query Builder.

Important

When specifying integers (or constants) in SQL queries, enclose them within single quotes (e.g. '0123') in the DB data block

Query File Property

The Query File property accepts a filename that points to a SQL file that the user has written. To provide a filename for a user-written SQL file:

1. Select the Query File row in the block's property table.
2. In the Value field, type the filename of the SQL file (the file is usually in the db folder of your project. If it is present in a different location, specify a relative path, such as ../myfolder/myquery.sql.

Stored Procedure Property

The Stored Procedure property opens the Stored Procedure Helper in which you can visually build the database query. To define a stored procedure call:

1. Select the Stored Procedure row in the block's property table.
2. Click the button to open the Stored Procedure Helper.

Column Names Variable Property

The Column Names Variable property maps the list of column names in the result to the specified variable. The default is Use system default, in which case the system uses an internal variable which is named in the format below. Genesys recommends that you define a user variable for this purpose in the Entry block and specify it in the DBData block. For Callflow diagrams: AppState.<blockname>DBResultColumnsNames For Workflow diagrams: App_<blockname>['DBResultColumnsNames'] To select a variable:

1. Select the Column Names Variable row in the block's property table.
2. In the Value field, select the variable from the dropdown list.

Records Variable Property

The Records Variable property maps the records (data) in the result set to the specified variable. The default value is Use system default, in which case the system creates an internal variable which is named in the format below. However, Genesys recommends that you specify a user variable in the Entry block. For Callflow diagrams: AppState.<blockname>DBResult For Workflow diagrams: App_<blockname>['DBResult'] To select a variable:

1. Select the Records Variable row in the block's property table.
2. In the Value field, select the variable from the dropdown list.

Note: The following applies to all methods of getting database results (query builder, stored procedure helper, custom queries): Results are stored in a variable as a two-dimensional JSON array. This data can then be accessed via a Looping block or via scripting in the Assign or ECMAScript block. For example, if the database result set looks like this in tabular form:

Vegetables	Animals
lettuce	chicken
broccoli	lion

 The JSON for the result will look like this: {"db_result":[["lettuce", "chicken"], ["broccoli", "lion"]],"db_result_columns":["vegetables", "animals"]}

Suppress Empty Result Set Exception Property

The Suppress Empty Result Set Exception property determines if the dbemptyresultset exception should be thrown if a query or a stored procedure execution results in an empty result set (number of records returned is zero). To provide a value:

1. Select the Suppress Empty Result Set Exception row in the block's property table.
2. Select true or false.

Exceptions Property

Find this property's details under Common Properties for voice blocks or Common Properties for Workflow Blocks. The Exceptions dialog box for the DB Data block has the following exception events:

• error.com.genesyslab.composer.dbconnectionerror
• error.com.genesyslab.composer.dberror (pre-selected in the Supported column)
• error.com.genesyslab.composer.dbemptyresultset (pre-selected in the Supported column)
• error.com.genesyslab.composer.dbtimeout

Condition Property

Find this property's details under Common Properties for Callflow Blocks or Common Properties for Workflow Blocks.

Logging Details Property

Find this property's details under Common Properties for Callflow Blocks or Common Properties for Workflow Blocks.

Log Level Property

Find this property's details under Common Properties for Callflow Blocks or Common Properties for Workflow Blocks.

Enable Status Property

Find this property's details under Common Properties for Callflow Blocks or Common Properties for Workflow Blocks.

ORS Extensions Property

Starting with 8.1.4, Composer blocks used to build routing applications (with the exception of the Disconnect and EndParallel blocks) add a new ORS Extensions property.