Help me on CVP class to query a SQL database

Unanswered Question
Jun 15th, 2009

Dear friends,

I try to create a class extends to Action element. But when I do deploy, it return no result.

I want this class receive Calling Number and do the query to database (SQL as I test, later if it works I will do for another database). After query I will get Name, Address, Email of this Customer based on Calling Number (ANI).

Please help to take a look on my program and help me to fix it.

Thank you very much.

import com.audium.server.AudiumException;

import com.audium.server.voiceElement.ActionElementBase;

import com.audium.server.session.ActionElementData;

import java.sql.*;


/**

* This class is called when a standard action has been configured to use a

* Java class. Since this is a standard action element, it applies to a

* specific application and does not have a configuration. As a result, the

* only method needed in this class is the doAction method.

*/

public class DBLookupStandardAction extends ActionElementBase

{



class WhatIGot

{

String ANI = null;

String Name = null;

String Address = null;

String Email = null;

}


public void doAction(String name, ActionElementData data) throws AudiumException

{

Connection conn = null;

String Name = null;

String Address = null;

conn = connectToDatabase();

String ani = data.getAni();

WhatIGot whatigot = new WhatIGot();

whatigot = QueryDatabase(conn, ani);

Name = whatigot.Name;

Address = whatigot.Address;

data.setSessionData("Name",Name);

data.setSessionData("Address",Address);

// PUT YOUR CODE HERE.

}


private Connection connectToDatabase()

{

Connection conn = null;

try

{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

conn = DriverManager.getConnection("jdbc:sqlserver://172.16.3.5:1433;DatabaseName=hcmpt","sa","");

}

catch (ClassNotFoundException e)

{

e.printStackTrace();

System.exit(1);

}

catch (SQLException e)

{

e.printStackTrace();

System.exit(2);

}

return conn;

}



/*Query*/

private WhatIGot QueryDatabase(Connection conn, String ANI)

{

WhatIGot WhatIGot = new WhatIGot();

try

{

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery("SELECT prefixnos, Name, Address, Email FROM customer_info where prefixnos = ANI");


WhatIGot.ANI = rs.getString ("prefixnos");

WhatIGot.Name = rs.getString ("Name");

WhatIGot.Address = rs.getString ("Address");

WhatIGot.Email = rs.getString ("Email");


rs.close();

st.close();


}


catch (SQLException se) {

System.err.println("Threw a SQLException creating the list of blogs.");

System.err.println(se.getMessage());

}

return WhatIGot;

}


}

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
Doan Khanh Tan Thanh Wed, 06/17/2009 - 01:43

Thank you Geoff,


I have made some changes and now it's working fine with SQL database.


I go forward to Oracle database. But can you show me how can I copy a new library (ojdbc14.jar) to the CVP server:

- which folder?

- and how apply it for all other application can use the same lib here?


waiting for you answer...


Thank you,




This code I have tested, it can connect and query and I try to put to screen. It is working. But when I apply to CVP xml server, it not work. And the CVP VXML server got "stop" (maybe on the fail state)




package com.dts.db;


/**

*

* @author Doan Khanh Tan Thanh

*/

// These classes are used by standard action elements.

import com.audium.server.AudiumException;

import com.audium.server.voiceElement.ActionElementBase;

import com.audium.server.session.ActionElementData;

import java.sql.*;




/**

* This class is called when a standard action has been configured to use a

* Java class. Since this is a standard action element, it applies to a

* specific application and does not have a configuration. As a result, the

* only method needed in this class is the doAction method.

*/

public class OracleDBLookupAction extends ActionElementBase

{



class WhatIGot

{

String Name = null;

String Address = null;

String Email = null;

}


public void doAction(String name, ActionElementData data) throws AudiumException

{

String NameWhatIGot = null;

Connection conn = null;

String AddressWhatIGot = null;

//String EmailWhatIGot = null;

String ani = null;

conn = connectToDatabase();

ani = (String)data.getSessionData("ANI");

WhatIGot whatigot = new WhatIGot();

whatigot = QueryDatabase(conn, ani);


NameWhatIGot = whatigot.Name;

AddressWhatIGot = whatigot.Address;

//EmailWhatIGot = whatigot.Email;

data.setSessionData("Name",NameWhatIGot);

data.setSessionData("Address",AddressWhatIGot);

// PUT YOUR CODE HERE.

}


private Connection connectToDatabase()

{

Connection conn = null;

try

{

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection("jdbc:oracle:thin:@172.16.5.134:1521:dbkttt","CUOC1080","123456");

}

catch (ClassNotFoundException e)

{

e.printStackTrace();

System.exit(1);

}

catch (SQLException e)

{

e.printStackTrace();

System.exit(2);

}

return conn;

}



/*Query*/

private WhatIGot QueryDatabase(Connection conn, String ANI)

{

WhatIGot WhatIGot = new WhatIGot();


try

{

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery("SELECT * FROM CUSTOMER_INFO where ANI = '838232822'");

while ( rs.next() )

{

WhatIGot.Name = rs.getString ("NAME");

WhatIGot.Address = rs.getString ("ADDRESS");

WhatIGot.Email = rs.getString ("EMAIL");

}

rs.close();

st.close();


}


catch (SQLException se) {

System.err.println("Threw a SQLException creating the list of blogs.");

System.err.println(se.getMessage());

}

return WhatIGot;

}


}

Under almost all circumstances, custom classes go in Cisco\CVP\VXMLServer\common\classes and JARs go in Cisco\CVP\VXMLServer\common\lib.


There are very unusual conditions with the class loader where you may have to put jars in VXMLServer\Tomcat\common\lib\endorsed and classes in VXMLServer\Tomcat\common\classes but you need to know what you are doing in order to take this step.


Regards,

Geoff

Doan Khanh Tan Thanh Wed, 06/17/2009 - 04:24

I have do the same: i put the jar file to this folder Cisco\CVP\VXMLServer\common\lib


but i got an error when i do the update any application, i got the same error when i try to restart the vxml service or vxml server also.


06/17/2009 17:58:25, SERVER ERROR: There was a problem loading classes from CVP_HOME\VXMLServer/common. The error was: There was a problem loading classes from CVP_HOME\VXMLServer/common. The root cause was: java.lang.NoClassDefFoundError: javax/resource/spi/ConnectionManager



Please help me on this.


Thanh



Doan Khanh Tan Thanh Wed, 06/17/2009 - 17:22

Thank you Geoff,


I copy this jar from the Oracle database that I tried to connect to in the path

Oracle_home/jdbc/lib

there are 3 files: class12.jar, ojdbc14.jar, ojdbc14_g.jar


If you have a this library that working wiht CVP 4.1.1. Please share it to me.


Thanh you,


Thanh

Doan Khanh Tan Thanh Wed, 06/17/2009 - 20:51

hi geoff,


can you show me how can i go forward with this java loader error?


06/17/2009 17:58:25, SERVER ERROR: There was a problem loading classes from CVP_HOME\VXMLServer/common. The error was: There was a problem loading classes from CVP_HOME\VXMLServer/common. The root cause was: java.lang.NoClassDefFoundError: javax/resource/spi/ConnectionManager

Jawad Bokhari Fri, 07/30/2010 - 15:20

I am also facing exactly the same issue.

When I place the ojdbc14.jar in common/lib, VXML server doesn't work. The classloader gives the same exception:


07/31/2010 00:58:58, SERVER ERROR: There was a problem loading classes from CVP_HOME\VXMLServer/common. The error was: There was a problem loading classes from CVP_HOME\VXMLServer/common. The root cause was: java.lang.NoClassDefFoundError: javax/resource/spi/ConnectionManager


And when I try running the java program from command line from the same server, it works supurbly.


I am using CVP VXML 7.2.


Thanks for any hints/directions for this.


Jawad

MaxTikhov_2 Mon, 11/29/2010 - 08:48

Hi, Doan!


Looks like you are using some kind of template for custom java action element to query DB. Can you share it or send it to me or say where you've got it?

Actions

This Discussion