Custom java class with JDBC

Answered Question
Mar 17th, 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!

I have this problem too.
0 votes
Correct Answer by sferland about 7 years 8 months ago

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 !

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
Loading.
Correct Answer
sferland Wed, 03/18/2009 - 06:20

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 Mon, 03/23/2009 - 08:42

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!

Michael Aossey Mon, 03/23/2009 - 10:20

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

Actions

This Discussion