This document was generated from CDN thread
Created by: Gerard O'Rourke on 25-04-2012 08:20:48 AM
Hi,
I have used the Cisco Database Action Element and is works fine.
I have added an entry to Cisco docwiki on how to set this up to work with an SQL 2005 server.
http://docwiki.cisco.com/wiki/Unified_CVP_VXML_Server_JNDI_Resources
However if there is a failure to the DB the CVP application will exit with a failure.
This could happen if their database connection has an issue (network, DB server issue etc.) or if there is any authentication issues etc.
I do not know why this element was not designed as a Decision Element so you could
have a sucess or fail exit states, you could then decide how to handle the error better.
Question:
Am I missing anything here in trying to handle an error such as the ones outlined above?
Does anyone have a better database integration element that the default out of the box one that they would like to share?
Gerard.
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 05-05-2012 01:55:04 AM
Trying again....
Anyone have a Database decision Element they want to 'share' that handles DB failures, to use instead of the 'out of the box' Database Action Element which does not.handle DB failures?
Gerard.
Subject: RE: New Message from Gerard O'Rourke in Customer Voice Portal (CVP) - Gener
Replied by: Hemal Mehta on 06-05-2012 09:49:34 AM
Just code it as an action element and call a backend java class which calls your database and catches related exceptions. You could also do it using hibernate etc. Here is some code for a very simple class.....
Hemal
String conURL = "........";
// connection string based on your db, ip, username and password
try {
// Load the Driver class.
Class.forName("....");
// If you are using any other database then load the right driver here.
//Create the connection using the static getConnection method
Connection con = DriverManager.getConnection (conURL);
//Create a Statement class to execute the SQL statement
Statement stmt = con.createStatement();
//Execute the SQL statement and get the results in a Resultset
ResultSet rs = stmd.executeQuery("select..........");
// Iterate through the ResultSet
while (rs.next()){
rs.getString("var1");
............
}
}
//Add all the exceptions here.....
catch (SQLException e) {
e.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
// Close the connection
con.close();
}
}
}
________________________________
From: Cisco Developer Community Forums [[email protected]]
Sent: Saturday, May 05, 2012 12:55 AM
To: [email protected]
Subject: New Message from Gerard O'Rourke in Customer Voice Portal (CVP) - General Discussion - All Versions: RE: CVP Database Action Element
Gerard O'Rourke has created a new message in the forum "General Discussion - All Versions":
--------------------------------------------------------------
Trying again....
Anyone have a Database decision Element they want to 'share' that handles DB failures, to use instead of the 'out of the box' Database Action Element which does not.handle DB failures?
Gerard.
--
To respond to this post, please click the following link:
<http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/5563266>
or simply reply to this email.
Subject: RE: New Message from Hemal Mehta in Customer Voice Portal (CVP) - General D
Replied by: GEOFFREY THOMPSON on 06-05-2012 07:09:34 PM
Instead of creating a connection with a URL based on DB, host, port, user, password and loading the driver class and building a connection from the DriverManager, like Hemal mentioned in his post
String conURL = "........";
// connection string based on your db, ip, username and password
// Load the Driver class.
Class.forName("....");
// If you are using any other database then load the right driver here.
//Create the connection using the static getConnection method
Connection con = DriverManager.getConnection (conURL);
define all the things needed through the normal JNDI configuration, just as you do to use the Cisco VXML Database Element.
This will allow you to use connection pooling and all the other good things that come from the JNDI libraries.
Now in your code:
//
// 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
//
Connection = dataSource.getConnection();
Regards,
Geoff
Subject: RE: CVP Database Action Element
Replied by: Janine Graves on 09-05-2012 12:33:18 PM
If you want something very simple, you could compile this - it's just the Studio Database element with a catch handler to catch all exceptions.
It creates element data named 'status' with the value 'success' or 'failure'
//These classes are used by custom configurable elements.
import com.audium.server.AudiumException;
import com.audium.server.voiceElement.ElementInterface;
import com.audium.server.voiceElement.Setting;
import com.audium.server.voiceElement.ElementData;
import com.audium.server.voiceElement.ElementException;
import com.audium.server.xml.ActionElementConfig;
// This class is used by action elements.
import com.audium.server.action.database.DatabaseAction;
import com.audium.server.session.ActionElementData;
public class JaninesDatabaseElement extends DatabaseAction implements ElementInterface
{
public String getElementName()
{
return "JaninesDatabase";
}
public String getDisplayFolderName()
{
return "Janines Elements";
}
public String getDescription()
{
return "This element executes the Studio Database Element but catches Java Exceptions.\n" +
"It creates Element data named 'status' with value 'success' or 'failure' ";
}
public void doAction(String name, ActionElementData actionData) throws AudiumException
{
String status = "success";
try {
super.doAction(name,actionData);
} catch (Exception e) {
status = "failure";
}
actionData.setElementData("status",status);
}
}
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 10-05-2012 03:23:18 AM
Thanks for all the reponses.
Very helpful.
What I actually did was configure an "On Error" CVP Subdialog Return which meant I could catch the error gracefully.
I found this info on another decussion, I believe from yourself Jaine!
I did not know about the "On Error" config you could do on this element before. Very useful....
Thanks,
Gerry
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 18-06-2013 08:56:03 AM
Janine,
Using your code above, I get a error on "import com.audium.server.action.database.DatabaseAction;"
See attached.
Any ideas why? The framework.jar is included in my java build path.
CVP 9.0.1 is the CVP Studio version.
Any help much appreciated.
Gerry
Gerry
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 18-06-2013 09:51:13 AM
Thanks Janine,
I knew it was something simple!
Thanks very much, I and I am sure others very much appreciate all the effort you put into into helping us out in this forum.
Gerry
Subject: RE: CVP Database Action Element
Replied by: Janine Graves on 18-06-2013 09:12:00 AM
Hi Gerry,
My guess is that you don't have elements.jar in your classpath. It's only needed for a few things (like extending the Studio Database element).
You'll add it to the Java Project under Project/Properties/Java Build Path / Libraries / Add External Jar -
elements.jar is located in the Studio Debugger directory C:\Cisco\CallStudio\eclipse\plugins\com.audiumcorp.studio.debug.runtime_9.0.1-SNAPSHOT\AUDIUM_HOME\common\lib\elements.jar
![]()
![]()
Created by: Gerard O'Rourke on 25-04-2012 08:20:48 AM
Hi,
I have used the Cisco Database Action Element and is works fine.
I have added an entry to Cisco docwiki on how to set this up to work with an SQL 2005 server.
http://docwiki.cisco.com/wiki/Unified_CVP_VXML_Server_JNDI_Resources
However if there is a failure to the DB the CVP application will exit with a failure.
This could happen if their database connection has an issue (network, DB server issue etc.) or if there is any authentication issues etc.
I do not know why this element was not designed as a Decision Element so you could
have a sucess or fail exit states, you could then decide how to handle the error better.
Question:
Am I missing anything here in trying to handle an error such as the ones outlined above?
Does anyone have a better database integration element that the default out of the box one that they would like to share?
Gerard.
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 05-05-2012 01:55:04 AM
Trying again....
Anyone have a Database decision Element they want to 'share' that handles DB failures, to use instead of the 'out of the box' Database Action Element which does not.handle DB failures?
Gerard.
Subject: RE: New Message from Gerard O'Rourke in Customer Voice Portal (CVP) - Gener
Replied by: Hemal Mehta on 06-05-2012 09:49:34 AM
Just code it as an action element and call a backend java class which calls your database and catches related exceptions. You could also do it using hibernate etc. Here is some code for a very simple class.....
Hemal
String conURL = "........";
// connection string based on your db, ip, username and password
try {
// Load the Driver class.
Class.forName("....");
// If you are using any other database then load the right driver here.
//Create the connection using the static getConnection method
Connection con = DriverManager.getConnection (conURL);
//Create a Statement class to execute the SQL statement
Statement stmt = con.createStatement();
//Execute the SQL statement and get the results in a Resultset
ResultSet rs = stmd.executeQuery("select..........");
// Iterate through the ResultSet
while (rs.next()){
rs.getString("var1");
............
}
}
//Add all the exceptions here.....
catch (SQLException e) {
e.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
// Close the connection
con.close();
}
}
}
________________________________
From: Cisco Developer Community Forums [[email protected]]
Sent: Saturday, May 05, 2012 12:55 AM
To: [email protected]
Subject: New Message from Gerard O'Rourke in Customer Voice Portal (CVP) - General Discussion - All Versions: RE: CVP Database Action Element
Gerard O'Rourke has created a new message in the forum "General Discussion - All Versions":
--------------------------------------------------------------
Trying again....
Anyone have a Database decision Element they want to 'share' that handles DB failures, to use instead of the 'out of the box' Database Action Element which does not.handle DB failures?
Gerard.
--
To respond to this post, please click the following link:
<http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/5563266>
or simply reply to this email.
Subject: RE: New Message from Hemal Mehta in Customer Voice Portal (CVP) - General D
Replied by: GEOFFREY THOMPSON on 06-05-2012 07:09:34 PM
Instead of creating a connection with a URL based on DB, host, port, user, password and loading the driver class and building a connection from the DriverManager, like Hemal mentioned in his post
String conURL = "........";
// connection string based on your db, ip, username and password
// Load the Driver class.
Class.forName("....");
// If you are using any other database then load the right driver here.
//Create the connection using the static getConnection method
Connection con = DriverManager.getConnection (conURL);
define all the things needed through the normal JNDI configuration, just as you do to use the Cisco VXML Database Element.
This will allow you to use connection pooling and all the other good things that come from the JNDI libraries.
Now in your code:
//
// 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
//
Connection = dataSource.getConnection();
Regards,
Geoff
Subject: RE: CVP Database Action Element
Replied by: Janine Graves on 09-05-2012 12:33:18 PM
If you want something very simple, you could compile this - it's just the Studio Database element with a catch handler to catch all exceptions.
It creates element data named 'status' with the value 'success' or 'failure'
//These classes are used by custom configurable elements.
import com.audium.server.AudiumException;
import com.audium.server.voiceElement.ElementInterface;
import com.audium.server.voiceElement.Setting;
import com.audium.server.voiceElement.ElementData;
import com.audium.server.voiceElement.ElementException;
import com.audium.server.xml.ActionElementConfig;
// This class is used by action elements.
import com.audium.server.action.database.DatabaseAction;
import com.audium.server.session.ActionElementData;
public class JaninesDatabaseElement extends DatabaseAction implements ElementInterface
{
public String getElementName()
{
return "JaninesDatabase";
}
public String getDisplayFolderName()
{
return "Janines Elements";
}
public String getDescription()
{
return "This element executes the Studio Database Element but catches Java Exceptions.\n" +
"It creates Element data named 'status' with value 'success' or 'failure' ";
}
public void doAction(String name, ActionElementData actionData) throws AudiumException
{
String status = "success";
try {
super.doAction(name,actionData);
} catch (Exception e) {
status = "failure";
}
actionData.setElementData("status",status);
}
}
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 10-05-2012 03:23:18 AM
Thanks for all the reponses.
Very helpful.
What I actually did was configure an "On Error" CVP Subdialog Return which meant I could catch the error gracefully.
I found this info on another decussion, I believe from yourself Jaine!
I did not know about the "On Error" config you could do on this element before. Very useful....
Thanks,
Gerry
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 18-06-2013 08:56:03 AM
Janine,
Using your code above, I get a error on "import com.audium.server.action.database.DatabaseAction;"
See attached.
Any ideas why? The framework.jar is included in my java build path.
CVP 9.0.1 is the CVP Studio version.
Any help much appreciated.
Gerry
Gerry
Subject: RE: CVP Database Action Element
Replied by: Gerard O'Rourke on 18-06-2013 09:51:13 AM
Thanks Janine,
I knew it was something simple!
Thanks very much, I and I am sure others very much appreciate all the effort you put into into helping us out in this forum.
Gerry
Subject: RE: CVP Database Action Element
Replied by: Janine Graves on 18-06-2013 09:12:00 AM
Gerard O'Rourke:Janine,
Using your code above, I get a error on "import com.audium.server.action.database.DatabaseAction;"
See attached.
Any ideas why? The framework.jar is included in my java build path.
CVP 9.0.1 is the CVP Studio version.
Any help much appreciated.
Gerry
Gerry
Hi Gerry,
My guess is that you don't have elements.jar in your classpath. It's only needed for a few things (like extending the Studio Database element).
You'll add it to the Java Project under Project/Properties/Java Build Path / Libraries / Add External Jar -
elements.jar is located in the Studio Debugger directory C:\Cisco\CallStudio\eclipse\plugins\com.audiumcorp.studio.debug.runtime_9.0.1-SNAPSHOT\AUDIUM_HOME\common\lib\elements.jar