Solved

JDBC ODBC MSSQL Driver Question

Posted on 1997-12-12
4
812 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
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:

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now