JDBC ODBC MSSQL Driver Question

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());
}
}
}
LVL 1
edcAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jpk041897Connect With a Mentor Commented:
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.
0
 
jpk041897Commented:
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.
0
 
edcAuthor Commented:
Thanks for your comments.  Monday will be the first opportunity that I have to start trying these changes.  I'll keep you posted.
0
 
edcAuthor 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
0
All Courses

From novice to tech pro — start learning today.