Solved: Custom java class with JDBC

Michael Aossey · ‎03-17-2009

I am attempting to use custom java app that queries a mssql server on another machine for data and returns it to CRS.

When I test the code from my dev machine or the command line on the crs server it returns data without issue but when I upload the jar file into CRS and attempt to call the same method in the script editor it fails and gives me the default prompt. I suspect that it is failing on the Class.getName.

I have tried to include the jar file with the jdbc driver in the jar file and reference it as a class path value in the manifest and I have also tried to upload it directly to CRS; neither of which worked.

I am really not sure how to troubleshoot this any further since I can not expose the exceptions when the code is called via the CRS script.

Is anyone out there using JDBC from a library that is uploaded into CRS?

Please help! Thank you in advance!

sferland · ‎03-18-2009

Two things to check:

1- You need to be sure that your custom class is compiled with the correct JRE version. Ex: for IPCC 4.0, you should use jre1.4.2_05.

2- Here is an example on how I'm using the JDBC driver:

...

Statement lStmt;

Connection lCon = null;

ResultSet lResultSet;

String lQuery = "SELECT [Key],[Value] FROM " + mTableName;

mURL = "jdbc:odbc:" + pDataSource + ";UID=" + pUsername + ";PWD=" + pPwd;

try {

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

lCon = DriverManager.getConnection(mURL);

lStmt = lCon.createStatement();

lResultSet = lStmt.executeQuery(lQuery);

...

I hope this help !

View solution in original post

sferland · ‎03-18-2009

Two things to check:

1- You need to be sure that your custom class is compiled with the correct JRE version. Ex: for IPCC 4.0, you should use jre1.4.2_05.

2- Here is an example on how I'm using the JDBC driver:

...

Statement lStmt;

Connection lCon = null;

ResultSet lResultSet;

String lQuery = "SELECT [Key],[Value] FROM " + mTableName;

mURL = "jdbc:odbc:" + pDataSource + ";UID=" + pUsername + ";PWD=" + pPwd;

try {

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

lCon = DriverManager.getConnection(mURL);

lStmt = lCon.createStatement();

lResultSet = lStmt.executeQuery(lQuery);

...

I hope this help !

Michael Aossey · ‎03-23-2009

Awesome. This was exactly what I needed. I guess I had never even considered ODBC since in general Sun steers you away from it at all costs. I would rather see this use a third party JDBC driver but now that I set up the DSN on my CRS server I see that this is most likely the method that Cisco is useing as well since there is a system DSN for every DB that CRS uses.

Thank you for the help!

geoff · ‎03-23-2009

Why not use the native SQL Server Java driver from Microsoft rather than going through the old ODBC/JDBC way?

That's an overhead you need to avoid.

Regards,

Geoff

Michael Aossey · ‎03-23-2009

Geoff,

There are 2 problems with the MSSQL JDBC driver from MSFT. First off it does not provide support for windows only authentication models like that used in CRS. Second is the fact that you have to have the MSFT (or anyother third party driver) jar files in your class path for the Class.ForName method to execute when called from within your CRS script. I was initially having trouble with the later of the 2 problems when I posted this. Using ODBC solved my issue becuase you do not need to package the thrid party driver in you archive that gets uploaded into CRS.

-Mike

geoff · ‎03-23-2009

Thank you Mike for your reply. I mainly work with CVP VXML and I talk to databases using the native Microsoft driver. After years of programming with the JDBC/ODBC method, I was happy to move on.

I was not aware of the CRS restrictions.

Regards,

Geoff

voiceops · ‎03-23-2009

What version of CRS are you running?