We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

JDBC ODBC MSSQL Driver Question

edc
edc asked
on
Medium Priority
857 Views
Last Modified: 2008-02-01
Hello All,
   I'm writing a bit of code to to connect a client machine to an MS-SQL server.  Right now I'm useing the JDBC-ODBC driver that is part of JDK 1.1.4.  Needless to say I'm not having any success with this.  When I run the application I get a 'Class not found' error.  Then an error from the application saying that it can't find a suitable driver.  I verified all the classpath statments.  I'm using the SQLStatement.java from the examples given with the JDBC download, (but the code is below anyway).  Does anyone see a problem with the code, or better yet, does anyone know of an MS-SQL driver that does not require ODBC?  Thanks for all the help.

import java.sql.*;
public class SQLStatement  {
public static void main(String args[]) {
String url = "jdbc:odbc:RemedySystemAR";
Connection con;
String query = "SELECT Ticket_Number, Assigned_to_Group, Short_Description " +
"FROM ARSystem.dbo.USFG_PM_ReportView " +               "where SUPPLIERS.SUP_NAME like 'Acme, Inc.' and " +         "WHERE CONVERT(varchar, Location_s__Affected) = 'Framingham' AND (Assigned_to_Group = 'OPSCMDCTR' OR Assigned_to_Group = 'NETWORKENG')";
Statement stmt;
try
{
Class.forName("JdbcOdbcDriver.class");
}
catch(java.lang.ClassNotFoundException e)
{
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
try
{
con = DriverManager.getConnection(url,"E3GU9", "9ug3e");      stmt = con.createStatement();                        ResultSet rs = stmt.executeQuery(query);      ResultSetMetaData rsmd = rs.getMetaData();            
int numberOfColumns = rsmd.getColumnCount();
int rowCount = 1;
while (rs.next())
{
System.out.println("Row " + rowCount + ":  ");
for (int i = 1; i <= numberOfColumns; i++)
{
System.out.print("  Column " + i + ":  ");            System.out.println(rs.getString(i));
}
System.out.println("");
rowCount++;
}
stmt.close();
con.close();
}
catch(SQLException ex)
{
System.err.print("SQLException: ");      System.err.println(ex.getMessage());
}
}
}
Comment
Watch Question

Up front I can see several problems with your code:



                  import java.sql.*;
                  public class SQLStatement {
Change this to :

public class SQLStatement trows SQLException {

to catch DB errors.


 
                  public static void main(String args[]) {

Why are you making main() a sub class of SQLStatement? main shoud be an independent class that instantiates a member of a class that includes SQLStatement or that call a function. Something like:

public static void main(String args[]) {
   SQLStatement();
   ...
}

public class SQLStatement {
...
}

                  String url = "jdbc:odbc:RemedySystemAR";
                  Connection con;
                  String query = "SELECT Ticket_Number, Assigned_to_Group,
                  Short_Description " +
                  "FROM ARSystem.dbo.USFG_PM_ReportView " + "where
                  SUPPLIERS.SUP_NAME like 'Acme, Inc.' and " + "WHERE
                  CONVERT(varchar, Location_s__Affected) = 'Framingham' AND
                  (Assigned_to_Group = 'OPSCMDCTR' OR Assigned_to_Group =
                  'NETWORKENG')";
                  Statement stmt;
                  try

Since you are (or should be) throwing SQLException back in the first line of the function, you might consider eliminating these intermediate try blocks in a simple program But its wise to keep them in ore complex code.


                  {
                  Class.forName("JdbcOdbcDriver.class");

This line should read:

Class.forName("JdbcOdbcDriver.JdbcOdbcDriver");


                  }
                  catch(java.lang.ClassNotFoundException e)
                  {
                  System.err.print("ClassNotFoundException: ");
                  System.err.println(e.getMessage());
                  try
                  {
                  con = DriverManager.getConnection(url,"E3GU9", "9ug3e"); stmt =
                  con.createStatement(); ResultSet rs = stmt.executeQuery(query);
                  ResultSetMetaData rsmd = rs.getMetaData();
                  int numberOfColumns = rsmd.getColumnCount();
                  int rowCount = 1;
                  while (rs.next())
                  {
                  System.out.println("Row " + rowCount + ": ");
                  for (int i = 1; i <= numberOfColumns; i++)

Since you are post incrementing, I suggest you change this to:

for (int i = 1; i <= numberOfColumns - 1; i++)
 
                  {
                  System.out.print(" Column " + i + ": ");
                  System.out.println(rs.getString(i));
                  }
                  System.out.println("");
                  rowCount++;
                  }
                  stmt.close();
                  con.close();
                  }
                  catch(SQLException ex)
                  {
                  System.err.print("SQLException: "); System.err.println(ex.getMessage());

                  }
                  }
                  }

These changes should get you a lot closer to what you are looking for.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Regarding part 2 of your question, there is a list of comertial JDBC drivers at:

http://www.javasoft.com/products/jdbc/jdbc.drivers.html

but changing from ODBC to straight JDBC is not going to solve the problems you are facing.

You might also want to look at http://www.mcp.com

They have a 30 day on-line trial of their book:

JDBC Guide, Getting Started

and several other books with good JDBC chapters that should get you up and running verry quickly.
edc

Author

Commented:
Thanks for your comments.  Monday will be the first opportunity that I have to start trying these changes.  I'll keep you posted.
edc

Author

Commented:
jpk,
  Couldn't find the book on mcp, but found other good stuff there.  Found a driver that skips ODBC all together, and goes strait to the SQL server.  Thanks for the help
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.