database element provides the ability to execute
an SQL command on external databases within a voice application call flow. The
element requires JNDI to be configured in the Java application server to handle
database connections. Only a single SQL statement can be executed per element.
There are four types of commands that can be made:
Single – This is used to run a SQL query that returns
only a single row. Element data will be created with the variable names being
the names of the columns returned and the value of that column as the element
data value (as a string). If no row is returned, no element data will be set.
Multiple – This is used to run a SQL query that returns
multiple rows. A Unified CVP-defined Java data structure, the Java class
ResultSetList, stores the full result and is
placed in session data. If no rows are returned, the
ResultSetList object in session data will be
empty. For detail about the ResultSetList data structure, refer to the javadocs
for this class.
Inserts – This is used to run a SQL INSERT command that
inserts information into the database.
Updates – This is used to run a SQL UPDATE command
that updates information in the database.
The developer can utilize substitution to create dynamic queries.
The Database element is ideal for performing simple queries and updates. It may
not be sufficient for performing complex database interactions such as multiple
dependent queries or stored procedure calls. One would use a custom
configurable or generic action element for these tasks. Also note that in order
to avoid performance issues creating database connections, implementing
database pooling on the application server is highly recommended. Refer to
additional information on configuring Tomcat JNDI resources.
The type of query: single,
multiple, insert or
JNDI name for the SQL datasource of the database.
(Session Data Key)
queries of type multiple, the name of the session variable which the results of
the query will be stored.
The SQL query to be
Element data is created
only when the type setting is set to
single. Element data given the names of the return columns
are created containing the respective return values. For example, if a query
returned the following information:
The following element data will be created: foo with
the value 123 and bar with the value
Session data is created
only when the type setting is
set to multiple. In all other cases, no session data is
[value of setting "key"]
The Java data
structure that stores the returned values from a multiple type query. The name
of the session data variable is specified by the developer in the
database query was successfully
Folder and Class Information
Element Folder Name
Exception event handler type can be selected.
Create JNDI Database Connection in Tomcat for Use in VXML Applications
explains how to create a new JNDI database connection in Tomcat. These
instructions are useful when you would like to use the built-in Studio Database
element, or create some custom code that accesses database functionality
In order enable database access on
your application server, a compatible JDBC driver must be installed. These
drivers, typically packaged as JAR files, should be placed in a directory
accessible to the application server classpath (on Tomcat, for example, place
database must exist for this connection to work. CVP VXML Server will not
create the database for you.
Builder for CVP Studio, edit the configuration of the Database element in
question. Enter the string you entered
in <LABEL_YOU_CHOOSE> from the Tomcat Context into the JNDI Name property
of the Settings tab of your Database element.
should not include the
jdbc/ portion here.
Here is an example that uses MySQL (edit
port number for MySQL is 3306. An example url for the above Context would be
<Resource> can be configured in the
server.xml file under
<GlobalNamingResources>, and a
<ResourceLink> created in
4. Under heavy
load conditions, enable Database Connection Pooling.
connection pool creates and manages a pool of connections to a database.
Recycling and reusing already existing connections to a database is more
efficient than opening a new connection. For further information on Tomcat