Solved

JDBC ODBC MSSQL Driver Question

Posted on 1997-12-12
4
822 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());
}
}
}
0
Comment
Question by:edc
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
jpk041897 earned 150 total points
ID: 1231611
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
 
LVL 6

Expert Comment

by:jpk041897
ID: 1231612
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
 
LVL 1

Author Comment

by:edc
ID: 1231613
Thanks for your comments.  Monday will be the first opportunity that I have to start trying these changes.  I'll keep you posted.
0
 
LVL 1

Author Comment

by:edc
ID: 1231614
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
type mismatch (Object[] to double[] 4 41
Java basic valueOf question 1 32
hashmap order 17 40
jsp CRUD operations with and without prepared statement also hibernatge 1 27
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question