« Back to CVP - All Versions

CVP -VXML Call studio Database Dip

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
CVP -VXML Call studio Database Dip
Answer
8/22/12 7:52 PM
Hi,
I am trying to write a vxml application to collect customer details from an Oracle database through Database dip based on ANI. Could you please tell me which is the base integration method for database dip. I saw documents based on JNDI and JAVA programming Please explaing me which is the best solution?
Flag

RE: New Message from Mark Applebee in Customer Voice Portal (CVP) - CVP - A
Answer
8/22/12 8:10 PM as a reply to Mark Applebee.
Depends on how you will be using databases I would say. JNDI is the one you will have to use if you want to use the built in db element in call studio. The problem with that element is if it fails to connect for any reason, it throws an error, which you then have to handle in the call studio script if you don’t want the script to just error out. If you have multiple DB calls and you want to know which one failed and why.
You could use java to do the lookup, but managing the connections in tomcat with a JNDI connection is much easier in my opinion.

What I have done for database lookups is used an extended version of the db element that Call studio provides. I got this element from Janine in her class, and modified it for my use, so that it returns the error message in addition to the success or failure status. That way I can include the error message in an email I send whenever the db element fails. In my opinion, this is the best solution.

I’ve already posted elements from Janine that I have modified, but I’m not sure how many of them she wants to share, since they are kind of a bonus from the class.
With her permission, I will post the custom element code here.
Flag

RE: New Message from Mark Applebee in Customer Voice Portal (CVP) - CVP - A
Answer
8/22/12 8:14 PM as a reply to Mark Applebee.
I have an application with over 100 Oracle SQL calls. What is your question?

Regards,
Geoff
Flag

RE: New Message from Mark Applebee in Customer Voice Portal (CVP) - CVP - A
Answer
8/22/12 8:22 PM as a reply to Asher Schweigart.
Thanks Asher..looks like my best option is to use JNDI then. I really appreciate if you could post those elements.
Flag

RE: New Message from Asher Schweigart in Customer Voice Portal (CVP) - CVP
Answer
8/22/12 8:41 PM as a reply to Asher Schweigart.
>>>You could use java to do the lookup, but managing the connections in tomcat with a JNDI connection is much easier in my opinion.

That’s not quite true. You can use the Connection Pooling features of the JNDI in Java.

I do that in Java and get the benefits.

Regards,
Geoff
Flag

RE: New Message from GEOFFREY THOMPSON in Customer Voice Portal (CVP) - CVP
Answer
8/22/12 8:51 PM as a reply to GEOFFREY THOMPSON.
I thought that might be possible, but I wasn’t sure. Can you reference the same JNDI that the included DB element uses, or is it a different JNDI configuration?
Any documentation you could point me to that describes how to use the call studio JNDI in custom java?
This would be handy for emails to; I could see creating an element that handles the DB lookup and the email on error all in one.
Flag

RE: New Message from Asher Schweigart in Customer Voice Portal (CVP) - CVP
Answer
8/22/12 9:01 PM as a reply to Asher Schweigart.
Code fragment (sorry, but email responses to the forum ruins all formatting)


Connection con = null;
Statement stmt = null;
ResultSet rs = null;

try
{
//
// Create the initial context. No JNDI properties
// are to be supplied here
//
Context initialContext = new InitialContext();

//
// Create a null datasource object
//
DataSource dataSource = null;

//
// Look up and set the datasource using the logical name
// specified in the ResourceLink global tag in Tomcat\conf\context.xml
// <ResourceLink global="jdbc/userDBConnection"
// name="jdbc/userDBConnection" type="javax.sql.DataSource"/>
//
// Note that we give the full context to lookup java:comp/env/
//
// You must always cast or narrow the object that JNDI
// returns to the DataSource, because the JNDI lookup()
// method returns a Java object.
//
dataSource = (DataSource) initialContext.lookup("java:comp/env/jdbc/userDBConnection");

//
// Establish the connection on the data source
//
con = dataSource.getConnection();

// do your SQL . . .

}
catch (SQLException ex)
{
}

Regards,
Geoff
Flag

RE: CVP -VXML Call studio Database Dip
Answer
3/14/13 1:27 PM as a reply to GEOFFREY THOMPSON.
Thanks Geoff. I got the code ready. I have some problem with Tomcat configuration I believe. My context.xml looks like this:

<Resource name="jdbc/cvp_db"
   auth="Container"
   type="javax.sql.DataSource"
   driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
   url="jdbc:sqlserver://10.10.66.10:1433;databaseName=cvp_db;integratedSecurity=true"
   />
 
and also I installed SQL JDBC driver on the vxml server and restarted the tomcat service.  Please let me know is anything else I have to do to make it work?
Flag