Howto call Database Stored Procedures from VoiceObjects

Overview

Known limitations of the VoiceObjects Database Object

As of VO 9.1R1, the Database object does not support calling DB stored procedures. It does support direct execution of SELECT, INSERT and UPDATE statements, though. When calling a SELECT statement, the result set is returned as a Collection to the Result value in the Database object.

Generic Connector for DB stored procedures

In this article, we present a generic VoiceObjects connector that calls database stored procedures. It can be used without modifications for most database servers and most stored procedures; and in cases where certain functionality is missing, it should be easy to add any required functionality by modifying the Java source code that is provided with this article.

Since stored procedures can be implemented in a variety of ways, we need to make a few assumptions.

• The stored procedure will take only parameters of simple types (such as VARCHAR, NUMERIC, DATETIME etc), with any combination of IN and OUT parameters

Here's an example for such a connector:

CREATE OR REPLACE procedure demo_proc (currFrom IN VARCHAR2, currTo IN VARCHAR2, amount IN NUMBER, lastUpdate OUT DATE) ...

• The stored procedure may return a result set
• The database connection pool should be configured as a resource on the web application server

Design considerations

The connector is implemented as a CGI connector - that is, VoiceObjects communicates with the connector code by exchanging XML request and response data via http. The implementation builds on the framework presented in this VoiceObjects Blog post.

These are the advantages of this approach - as compared to using a Java connector:

• Problems within the connector code cannot directly affect operations and performance of VoiceObjects Server because the code is executed in a different JVM.
• The database connection pool can be managed independently of the VoiceObjects cluster. With a Java connector, a separate database connection pool would be required on each individual VoiceObjects Server instance; with the CGI connector, this can be managed centrally and hence more efficiently.

Download the package

Required: The connector package VOConnector.zip.

Optional: The Java sources. Note that if you want to recompile the Java sources, you need to

• add ConnectorUtil.jar, jstl.jar and standard.jar (they are part of the VOConnector.zip archive) and javax.servlet.jar (this can be found in the /lib folder of your web application server) to your classpath, and
• compile into the jar file storedproc.jar, replacing the archive with the same name in VOConnector.zip.

Deploying the Connector

Let's start with deploying the connector. We will explain how to deploy it to a Tomcat 6.0 web application server, using the DBCP library implementation that comes pre-installed with Tomcat 6.0. Internally, this library uses the Commons Database Connection Pool.

Note that other web application servers will work just fine; the configuration details might differ slightly. Just make sure that you have configured the web application server to use JDK/JRE 1.6 or above.

Download and install Tomcat 6.0

We will install the connector on a separate web application server (as opposed to the web application server that hosts VoiceObjects Server). Download Tomcat 6.0 and install it on your machine. When configuring that Tomcat server, make sure to avoid port conflicts with your existing VoiceObjects installation.

Deploy the JDBC driver

Depending on which database server you use, copy the proper JDBC driver's *.jar file from your VoiceObjects /lib folder to the /lib folder of your Tomcat installation. For Oracle, this is ojdbc14.jar; for MS SQL Server, jtds.jar; for PostGreSQL, postgres.jar; for mySQL, mysql.jar.

Deploy the VOConnector web application

Unzip VOConnector.zip to the /webapps folder of your Tomcat installation. Having done that, just check that the file Tomcat 6.0/webapps/VOConnector/WEB-INF/lib/ConnectorUtil.jar exists in your installation.

The next two steps are explained in more detail in the Tomcat 6 JDNI Datasource Howto.

Configure the JNDI datasource

In the /conf/context.xml file of your Tomcat installation, add a declaration for your database connection pool resource to your context. Here's an example for a connection pool configuration for MS SQL Server; note that the <Resource> element needs to be a child of the <Context> element.

<Resource name="jdbc/MyDB"

auth="Container"

type="javax.sql.DataSource"

username="vouser" password="mypassword"

driverClassName="net.sourceforge.jtds.jdbc.Driver"

url="jdbc:jtds:sqlserver://localhost:1433/DemoData"

maxActive="8" maxIdle="4"/>

For Oracle, you'd set driverClassName="oracle.jdbc.driver.OracleDriver" and url="jdbc:oracle:thin:@{host}:{port}:{dbName}" instead.

For an overview of the supported attributes in the Resource element, click here.

Add a resource reference to web.xml

The deployment descriptor /webapps/VOConnector/WEB-INF/web.xml of the new web application must contain a reference to the resource that we just configured.

<web-app ...>

...

<resource-ref>

<description>DB Connection</description>

<res-ref-name>jdbc/MyDB</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>

</web-app>

Note how the content <res-ref-name> element - jdbc/MyDB - refers to the name attribute of the <Resource> element in context.xml.

Configure the VoiceObjects Connector object

In your VoiceObjects project, create a new Connector object. Here's how to configure the parameters:

URL = http://{yourserver}:{yourport}/VOConnector/

All other fields stay empty or keep their default values.

Now, for the Parameter Set:

The format of the (optional) resultSet collection is straightforward: One row for each row from the result set, one column for each column from the result set. And the column names are simply the column names from the result set.

<root>

<row>

<col name="colname1">value 1,1</col>

<col name="colname2">value 1,2</col>

...

</row>

<row>

...

</row>

</root>

Testing the connector

Before calling the connector from a VoiceObjects application, we can test it directly with the test.html page that was provided with the connector code. Simply call

http://{yourserver}:{yourport}/VOConnector/test.html

in your favourite web browser, and configure the web form with parameters as described in the section above.

Here's an example:

Now, click the Submit button. If everything is configured properly, the browser now presents an XML document with the response XML code that was created by the connector, such as:

Error codes

There are a few error scenarios that may happen. These are the error codes that the connector may respond with: