Overview

The Database object enables easy access to Relational Database Management Systems (RDBMS) to retrieve, store, and manipulate data. It can work with single values as well as entire tables, which are represented as Collection objects. The result mapping capability can be used to conveniently extract individual values from result sets.

The Database object also provides embedded connection pooling to optimize throughput and at the same time limit the number of simultaneous RDBMS connections.

Usage examples for the Database object are:

·          Retrieving dynamic content such as prompts or grammars

·          Retrieving customer profiles at the beginning of the call and writing back updates after the call has ended

·          Making a transaction on a back-end system, e.g. booking a flight

While the Database object specializes on access to RDBMS, the Connector object is available for general purpose backend connectivity. The advantage of the Database object is that only the SQL needs to be provided; when working with the Connector object some level of custom coding is required.

The Object Definition below covers the configuration of the Database object with VoiceObjects Desktop. For information on how to define this object type using VoiceObjectsXML refer to the VoiceObjectsXML Definition paragraph.

The Database object belongs to the object category Resources.

Dialog Flow Scenario

The following dialog flow shows a database transaction while the caller is logging into a brokerage service. The personal stock portfolio of the caller is retrieved and played back as a list.

Object – Caller		Dialog Flow
		Please wait while I check your data.
		A wait loop audio file is played until the Database object has finished processing.
		Connects to the database holding customer data.
		[ Earcon for positive confirmation ]
		Welcome to the brokerage service. Here’s a list of all the companies in your portfolio…

Object Definition

The Definition of the Database object provides the following sections:

·          Database Connection
To define the connection to the RDBMS.

·          Database
To define one or more Database items.

·          Process Notification
To optionally specify a notification to the caller that the database processing has started.

·          Wait Loop Audio
To optionally specify an audio file that is played back while the database process is running.

For further details regarding additional object configuration, refer to Pre-/Postprocessing, Event Handling, Tuning, or Properties in this Object Reference.

Database Connection

The Database Connection section defines the key properties required for connecting to the RDBMS.

 

The individual properties are as follows:

Property	Description
Database type	The type of RDBMS to connect to. Predefined types are DB2, Derby, H2, MaxDB, MySQL, Oracle (the default), PostgreSQL, and SQL Server. In addition it is also possible to provide the fully qualified name of the JDBC driver when using a RDBMS not contained in this list.
Database URL	The URL to connect to the RDBMS. This is a JDBC URL e.g. of the form jdbc:oracle:thin:@localhost:1521:OVAPDB (for Oracle) or jdbc:jtds:sqlserver://localhost:1433/TDR (for SQL Server).
User ID	The user ID with which to connect to the RDBMS.
Password	The password for the user specified by User ID. Note that User ID and Password can also be defined using e.g. Variable objects that are filled by an external configuration using Application Defaults. See the corresponding section in Chapter 4 – Service Deployment in the Deployment Guide for details.
Pool size	The size of the connection pool to be used. This limits the total number of simultaneous connections open at the same time for the same RDBMS connection. This connection is defined by Database type, Database URL and User ID. At the same time it optimizes throughput by eliminating the overhead of constantly having to re-establish the connection. If not specified, a default pool size of 10 is used.

 

Database

In the Database section, define at least one Database Item that will process a SQL statement.

 

Each Database Item contains the following set of fields:

Property	Description
Label	Optional parameter to identify the Database item in a list.
Layer	Optional parameter to indicate the layer that the Database item belongs to. For more information on layers, see Chapter 7 – How to Use Layers in the Design Guide or the Layer object in this Object Reference.
SQL Statement	The SQL Statement can be defined by entering plain text in the text area or by inserting new or existing Variable, Collection, Expression, Script, and Layer objects. For more information on how to do this, refer to Chapter 6 – Object Editors in the Desktop for Eclipse Guide and Desktop for Web Guide, respectively. To insert a new object, drag the appropriate object type from the Object Palette. In Desktop for Web this is done via the context menu. To simplify the mapping to the tables of the Infostore Repository you can use wildcards inside a SQL statement that represent key values from tables, like dialog ID, CRM ID, the ID of the active business task, etc. For a list of these wildcards, refer to Chapter 1 – Welcome to Infostore in the Infostore Guide. Stored procedures may be used if they can be accessed from within the SQL statement (this differs depending on the RDBMS that is used). For Oracle, the call statement must be used to invoke the stored procedure.
Return value	Allows to link a Variable or Collection object that will be set to the return value of the SQL statement. The structure of the return value depends on whether the SQL statement selects values from tables, or modifies tables (e.g. using UPDATE or INSERT). When values are selected, the structure of the return value is as follows: ·          If a single value is returned by the RDBMS and a Variable object is linked as return value, the variable receives the single value. ·          If a single value is returned by the RDBMS and a Collection object is linked as return value, the collection receives a structure of the form <root>   <row>     <column name=”Column name from database table”>Value</column>   </row> </root> ·          If a set of rows is returned by the RDBMS and a Variable object is linked as return value, the variable receives the comma-separated list of values, column-by-column and row-by-row. ·          If a set of rows is returned by the RDBMS and a Collection object is linked as return value, the collection receives a structure of the form <root>   <row>     <column name=”Column name from database table”>Value</column>   </row>   … </root> When tables are modified, the structure of the return value is as follows: ·          If a Variable object is linked as return value, the variable receives the number of rows that were modified. ·          If a Collection object is linked as return value, the collection receives a structure of the form <root>   <row>     <column name=”cnt”>Number of modified rows</column>   </row> </root>

 

Result Mapping

The Result Mapping section allows extracting specific values from the return value of the SQL statements by applying an XPath expression to the return value. Any number of result mappings can be defined. The expression specified in the XPath field will be applied to the return value of the SQL statement. The result of this operation will be assigned to the Variable, Collection or Layer object defined in the Variable field.

For more information on using Result Mapping, refer to the Connector object.

More information about XPath can be found at http://www.w3.org/TR/xpath.

Process Notification

In the Process Notification section, an embedded Output object can be specified that gives the caller a notification that the database process is starting. This parameter is optional.

 

For further details, refer to the Output object in this Object Reference.

Wait Loop Audio

In the Wait Loop Audio section, an audio file can be specified that is played back while the database process is running. This parameter is optional and only applies to the voice and video channel. It is typically used for time-consuming database operations, e.g. to play hold music.

 

For further details on the possible settings, refer to the Audio object in this Object Reference.

i8    Note: On some media platforms, the duration of the audio file must be at least as long as the processing time needed by the Database object. On other platforms, an audio file that is shorter than the processing time required by the database will be looped continuously as needed. For further information on the behavior of audio processing refer to the local documentation of the media platform vendor.

Processing the Database Object

This section contains some technical remarks on the treatment of Database objects in VoiceObjects Server.

For each Database object used in the voice or video channel, the server sends a VoiceXML document (a single one for all Database items) to the media platform, even though there is no interaction with the caller. The document consists of a <submit> element including the fetchaudio attribute. If a wait loop audio is specified for the object, the audio is mentioned in this attribute, so that the platform plays this file while the connector is processed. Any output defined before the Database object (either in the Process Notification section within the object, or in Output objects prior to the Database object in a sequence) will be "flushed" by the platform, i.e. played back before the wait loop audio is applied, which is a desirable effect. To achieve this effect even if no wait loop audio is defined, the server renders a reference to an empty audio file (taken from the system resource folder of the VoiceObjects installation) in the fetchaudio attribute. Without this "flush" effect, a media platform might withhold the output defined prior to the Database object until it has been processed, which would result in undesirable delays.

At the same time, a fetchtimeout attribute is rendered and set to the value of 10000s (ten thousand seconds). The effect of this is that the media platform is forced to wait until the Database object has been processed and does not try to request another VoiceXML document from the server before then. If it did, the server would reject the request.

In the text and Web channel, there will be no intermediate page rendered, unless there is a process notification defined which is rendered as a standalone output.

VoiceObjectsXML Definition

The Database object is represented by the VoiceObjectsXML element <database>. It has five attributes and eight groups of children.

In addition, the element has the standard attributes described in the XDK Guide.

The <database> element uses the embedded <DatabaseItemXDK> and <resultMapping> elements.

Database

Attributes

·          user
User ID used for database schema.

·          password
The password for the user ID provided in user.

·          dbURL
URL to connect to the RDBMS.

·          dbType
Type of RDBMS used. Either one of DB2, Derby, H2, MaxDB, MySQL, Oracle, PostgreSQL, SQLServer, or the fully qualified name of the JDBC driver for the RDBMS (e.g. com.mysql.jdbc.Driver).
Default value is Oracle.

·          poolsize
Size of the pool to be used for this database connection. Must be an Integer. If not defined, defaults to 10.

 

Children

·          <expression usage=”precondition”> or
<variable usage=”precondition”> or
<collection usage=”precondition”> or
<script usage=”precondition”>
Defines the precondition for the Database object.

·          <sequence usage=”preprocessing”>
Defines the preprocessing sequence for the Database object.

·          +<databaseItem>
Defines the list of Database items.

·          <output>
Defines the “process notification” output for the Database object.

·          <audio>
Defines the wait loop audio. Alternative text for this audio is ignored.

·          <eventHandling>
Defines the event handling for the Database object.

·          <tuning>
Defines the tuning settings for the Database object.

·          <sequence usage=”postprocessing”>
Defines the postprocessing sequence for the Database object.

 

Example

<database name=”Retrieve language preference” dbType=”SQLServer”

  dbURL=”prefdb1:1433/CALLERPREFS” user=”prefmanager” password=”c78gbk4”>

  <databaseItem returnValue=”#Language Preference”>

           <text>select LANG from CALLERPREFS where CALLERID=’</text>

    <variable link=”#Caller ID”/>

    <text>’;</text>

  </databaseItem>

</database>

DatabaseItem

Attributes

·          label
A text string providing a name for the Database item.

·          layer
Defines the layer for the Database item. Can either be a reference to a Collection, Expression, Script, or Variable object; or a layer state reference of the form “Layer=State” or “Layer!=State” where “State” is the label of a state for the layer “Layer”.

·          returnValue
The return value of the SQL statement executed on the RDBMS.
Must be a reference to a Variable or Collection object.

Children

·          +(<output>, <variable>, <expression>, <collection>, <script>, <text>, <layer>)
Defines the SQL statement, as a concatenation of various elements.

·          <resultMapping>
Defines the result mapping for the Database item.

 

Example

  <databaseItem returnValue=”#Language Preference”>

           <text>select LANG from CALLERPREFS where CALLERID=’</text>

    <variable link=”#Caller ID”/>

    <text>’;</text>

  </databaseItem>

ResultMapping

Children

·          +<resultMappingItem>
Defines the list of entries in the result mapping. Both the object and value attribute must be defined.

 

Example

<resultMapping>

  <resultMappingItem object="#Tradevalue" value="/quote/lastTradeValue"/>

  <resultMappingItem object="#PrevTradeValue" value="/quote/previousCloseValue"/>

</resultMapping>

ResultMappingItem

Attributes

·          object
Defines a reference to a Variable, Collection or Layer object.

·          value
Defines the XPath expression that will be applied to the return value. The result of this expression will be assigned to the object under object.

 

Example

  <resultMappingItem object="#Tradevalue" value="/quote/lastTradeValue"/>

  <resultMappingItem object="#PrevTradeValue" value="/quote/previousCloseValue"/>

Object Interoperability

The following table contains all object types that can refer to a Database object:

Icon	Object Name	Use Case Example
	Module	A Database object can be used within the embedded sequence of a Module object.
	Sequence	Most commonly, a Database object is used within a Sequence object in a dialog flow.
	Menu	A Database object can be used as the destination object within a Menu item in a Menu object.
	Confirmation	A Database object can be used as the destination object within a Correction item inside a Confirmation object. While technically possible, this is not typically useful.
	If	A Database object can be used within either the THEN or the ELSE item of an If object.
	Case	A Database object can be used in any WHEN item of a Case object.
	Loop	A Database object can be used within the embedded sequence of a Loop object.
	Goto	A Database object can be referenced via a Goto object.
	Hyperlink	A Database object can be the destination of a Hyperlink object.

Object Naming Conventions

In order to leverage the capabilities of the integrated documentation of VoiceObjects it is important to provide intuitive and self-explaining object names and descriptions.

The name of a Database object should indicate the task it performs. The short description should contain a brief explanation of the data exchanged, as well as assumptions and restrictions. The long description should give a full description of the parameters exchanged in the Database object, indicating whether they are read, write, or read/write. In case of finite value sets, these should also be listed (e.g. variable can have the values yes, no, or maybe). The table below lists three examples:

Name	Description
Retrieve caller profile	Retrieves caller profile from database based on ANI. The profile is provided in the Collection object Caller Profile.
Obtain flight options	Retrieves list of possible flight connections from back-end, based on date, time, origin, and destination. Note that the list may be empty.
Store caller preferences	Writes the modified caller preferences (persona, output style, barge-in setting) to the database.