Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

Help me on CVP class to query a SQL database

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;

}

}

13 REPLIES
Green

Re: Help me on CVP class to query a SQL database

I've done this many times - SELECT, UPDATE, INSERT and stored procedures. You are certainly on the right track.

Use the ActionData - addToLog() method to write out some tracing info. You'll find the bug.

Regards,

Geoff

Re: Help me on CVP class to query a SQL database

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;

}

}

Green

Re: Help me on CVP class to query a SQL database

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

Re: Help me on CVP class to query a SQL database

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

Green

Re: Help me on CVP class to query a SQL database

Is ojdbc14.jar self-consistent? Does it contain references to javax/resource/spi/ConnectionManager? Do you have the jar that resolves that reference?

Class loading issues can be tricky, but can normally be resolved.

Regards,

Geoff

Re: Help me on CVP class to query a SQL database

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

Green

Re: Help me on CVP class to query a SQL database

Sorry, I've only done MySQL and Microsoft SQL Server.

Regards,

Geoff

Re: Help me on CVP class to query a SQL database

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

New Member

Re: Help me on CVP class to query a SQL database

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

Green

Re: Help me on CVP class to query a SQL database

Try it in Cisco\CVP\VXMLServer\Tomcat\common\lib

If you want to use the JNDI connection pooling in Tomcat, you need it there.

Regards,

Geoff

Re: Help me on CVP class to query a SQL database

Yes, I put it here C:\Cisco\CVP\VXMLServer\Tomcat\common\lib and restart the VMXL service. It works.

New Member

Re: Help me on CVP class to query a SQL database

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?

Green

Re: Help me on CVP class to query a SQL database

we all got it from the old Audium forum.

Regards,

Geoff

1765
Views
0
Helpful
13
Replies
CreatePlease login to create content