ClassNotFoundException at JDBC connection
Hi
1)I have classpath:
%CATALINA_HOME%\common\lib\servlet.jar;c:\jdk1.4.0_02\lib\tools.jar;c:\java;c:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar
2) i have 3 java file on my c:\java2 folder.
3) I test the following code, I got error at:
ClassNotFoundException: com.microsoft.jdbc.sqlserver....
Error Trace in getConnection() : com.microsoft.jdbc.sqlserver.SQLServ
erDriver
Error: No active Connection
4) can I compile this code with regular way like
java2>javac Connect.java
java2>java Connect
to compile and run the code?
Thank you !
/////////////////////////////
import java.sql.*;
public class Connect{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "pubs";
private final String userName = "sa";
private final String password = "ProjectA11";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";
// Constructor
public Connect(){}
private String getConnectionUrl(){
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}
private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
/*
Display the driver properties, database details
*/
public void displayDbProperties(){
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try{
con= this.getConnection();
if(con!=null){
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next()){
System.out.println("\tcatalog: "+ rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}catch(Exception e){
e.printStackTrace();
}
dm=null;
}
private void closeConnection(){
try{
if(con!=null)
con.close();
con=null;
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}
[3491 byte] By [
AAmya] at [2007-11-23]
You should have a class file called SQLServerDriver.class may be located in a .jar file.
If this is a jar file try putting it in c:\j2sdk1.4.0_02\jre\classes directory.
If you don't have the classes directory create it.
If that dosen't work, unzip the .jar file using winzip or something. This will give you a directory structure like
com\microsoft\jdbc\sqlserver\
the sqlserver directory must have the SQLSerevrDriver.class file. Also the com directory must be in classes directory.
Also make sure that the classes directroy is in your classpath
> Hi
> 1)I have classpath:
> %CATALINA_HOME%\common\lib\servlet.jar;c:\jdk1.4.0_02\l
> b\tools.jar;c:\java;c:\Program Files\Microsoft SQL
> Server 2000 Driver for
> JDBC\lib\msbase.jar;c:\Microsoft SQL Server 2000
> Driver for JDBC\lib\msutil.jar;c:\Microsoft SQL Server
> 2000 Driver for JDBC\lib\mssqlserver.jar
No you don't have that class path.
Why am I so sure? Because you have an unexpanded env variable in there.
So I am guessing that that is what you think the class path is.
When verifying the class path always explicitly check in from the environment from which you run. This means, for example, opening a console window and typing 'set' (or 'env') and looking at what the value is.
Once you know what your class path is then you can verify ANY class by typing the following.
java AnyClass
For your specific example that would look like this
java com.microsoft.jdbc.sqlserver....
If it says "class not found" then the class is not in your class path. If it say "main not found" then the class is in your class path.
If it is in your class path and you still get the error it means you are not checking the correct environment. The environment of a web server is not the same as the environment for a user. You must check the environment that you are running in.
Hi
Thank you all the reply! you are right, first I set my claspath like I post before, then I change them from c:\program files to c:\, but I forget to move my Jdbc drive from c;\program files direct to c:\.
sorry for the silly mistake!
but, now I get the new eror:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
LServer]Login failed for user 'sa'.
Error Trace in getConnection() : [Microsoft][SQLServer 2000 Driver fo
r JDBC][SQLServer]Login failed for user 'sa'.
Error: No active Connection
I checked my MS sql 2000 server sa is the deful username, I just wonder, do I need a special way to install sql 2000 server?
since, I test regular select, store proceudre code, it work on Dtabse environment, why connect is fail
hope get help !
Thank you
See if this works:
private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl() + ";user=" +userName + ";password=" +password);
if(con!=null) System.out.println("Connection Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
I changed
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
to
con = java.sql.DriverManager.getConnection(getConnectionUrl() + ";user=" +userName + ";password=" +password);
Hi Chirag11:
Thank you! I tried your suggestion, But still get error:
Error Trace in getConnection() : [Microsoft][SQLServer 2000 Driver fo
r JDBC]Error establishing socket.
Error: No active Connection....
Error Trace in getConnection() : [Microsoft][SQLServer 2000 Driver fo
r JDBC]Error establishing socket.
Error: No active Connection
now my new questions
1)What is the correct usernmae and password?
2)where to find the SQL username and password?
Thank you
Hi Chirag11:
Thank you! I tried your suggestion, But still get error:
Error Trace in getConnection() : [Microsoft][SQLServer 2000 Driver fo
r JDBC]Error establishing socket.
Error: No active Connection....
Error Trace in getConnection() : [Microsoft][SQLServer 2000 Driver fo
r JDBC]Error establishing socket.
Error: No active Connection
now my new questions
1)What is the correct usernmae and password?
2)where to find the SQL username and password?
Thank you
This works on my computer.
class dbcheck{
public static void main(String args[]){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://192.1.1.1:1433","sa","");
System.out.println("success");
}catch(Exception e){
System.out.print("Error");
e.printStackTrace();
}
}
}
replace the 192.1.1.1 with your IP address instead of localhost. If its on your own computer put 127.0.0.1
Instead of using sa create a new user and put the password instead of blank.
-Chirag
Hi chirag11:
the code still not work on my computer, I still got Errorjava.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDB
C]Error establishing socket.also, I test othe code on tomcat got same error.
may I know
1) what software are On your computer in order let the code work, I have jdk1.4.02; sql server2000 eval and driver on c driver ?
2) do you have special care when you install sql server?
//////////////////////
import java.sql.*;
class dbcheck{
public static void main(String args[]){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection connection = DriverManager.getConnection("jdbc:microsoft:sqlserver://172.0.0.1","sa","");
System.out.println("success");
}catch(Exception e){
System.out.print("Error");
e.printStackTrace();
}
}
}
ThanK you
Hi,
I will give u some hints . follow the steps
Common mistakes while coding a jdbc aware program
1. ClassNotfound Exception.: this occurs when the required driver clas is not found in your class path (the jar path is not set in the CLASSPATH variable). simple solution is copy all the driver related jar files to the JDK_HOME\jre\lib\ext folder.and forget hectic CLASSPATH !.but make sure that u are using this same jdk to run ur program.If the error exists then ur app is using someother default jdk!
2.No Suitable driver error: this occurs if the driver is not able to understand the supplied url. ie the url format is wrong.look at the driver documentation and use the correct URL syntax.
3.Error establishing socket,connection refused error etc.: here the driver tried to establish a connection to the host specified in the URL.but it failed.the reason :wrong host name.wrong port number etc.check the supplied host name. use ping/telnet tool to see whether the specified host is reachable.dont forget to supply port number in the driver URL.last not the least make sure that u have not done any change in the underlying jdk policy file(the default will work fine).
4.Login failed for user xx. this shows that the driver is able establish a connection with the database server.but the authentication failed.the reason is
1. the supplied user name/pwd is wrong.try to connect to SQL server using client utility with the proposed user/pwd
2.make sure that mixed mode authentication is set in the sql server.
in SQL server , the authentican mode can be set either
1. windows authentican only
2. mixed mode (window and sql server authentication)
u can change the auth mode using enterprise manager. right click on the server name (see the left side tree). from the popup click on properties.from the properties tab, select security.click the radio button - mixed mode authentication ie windows and sql server authentication.apply and restart sql server. done !
Now u can connect to the server as u expected.
HTH
- asharafkk
Hi asharafkk:
Thank you for you reply! I alway struggle at Error establishing socket
and Login failed for user xx. problems(som times gots first error, some times got second error) over one week, I guess that I still need to ask some detail question.
>3.Error establishing socket,connection refused error etc.: ...
>wrong host name.wrong port number etc.
1)What is correct host name, I try localhost, used horst nmae, Ip address find By("ipconfig/all") at DOs , but not of them is work.
2)on my windows ipconfiguration
a) I have WINS Proxy Enabled. . . . . . . . : No
b) NetBIOS over Tcpip. . . . . . . . : Disabled
is that is the problem, if yes, how to deal with.
>make sure that u have not done any change in the underlying jdk policy file(the default will work fine).
I have jdk 1.4.0_02 on my c driver, I don't thing that I made any change on here. But in order to make sure, I would like to ask which fold is jdk policy file.
>4.Login failed for user xx. this shows that the driver is able establish a connection with the database server.
I went to my sql log, I got message
SQL Server is ready for client connections
SQL Server terminating because of system shutdown.
2003-01-16 23:08:10.68 spid3SQL Server is terminating due to 'stop' request from Service Control Manager.
2003-01-16 23:08:10.71 spid3LogEvent: Failed to report the current event. Operating system error = 1717(The interface is unknown.).
1. the supplied user name/pwd is wrong.try to connect to SQL server using client utility with the proposed user/pwd
I don't know how to find a correct user name and password.
>2. mixed mode (window and sql server authentication)
I did set mixed mode, but the only server I have is local
and on SQL server properties(configure )-local, I chose Audit level =success, but I failed to chose This account to start SQL because the system keep tell me then the xxxxx is not a valid window NT name
Thank you
I use the driver available at:
http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/779/msdncompositedoc.xml
no spacesin the above link. Once installed, read the following:
If you still get the same error, this could be some security issue.
No I dont need any special setup for sql server. Check to make sure your server is running. Also create a new user with password and give it all previleges. Use that user to login and make sure it works then read the below.
Connecting Through the JDBC Driver Manager
--
One way of connecting to a database is through the JDBC driver manager using the method DriverManager.getConnection. This method uses a string containing a URL. The following is an example of using the JDBC driver manager to connect to Microsoft SQL Server 2000 while passing the user name and password:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://server1:1433;User=test;Password=secret");
URL Examples
The complete connection URL format used with the driver manager is:
jdbc:microsoft:sqlserver://hostname:port[;property=value...]
where:
hostname is the TCP/IP address or TCP/IP host name of the server to which you are connecting.
NOTE: Untrusted applets cannot open a socket to a machine other than the originating host.
port is the number of the TCP/IP port.
property=value specifies connection properties. See "Connection String Properties" for a list of connection properties and their values.
The following example shows a typical connection URL:
jdbc:microsoft:sqlserver://server1:1433;user=test;password=secret
IF THE ABOVE STILL DOSENT WORK TRY CONNECTING THRU DATASOURCE.
Connecting Through Data Sources
--
A SQL Server 2000 Driver for JDBC data source is a DataSource object that provides the connection information needed to connect to an underlying database. The main advantage of using a data source is that it works with the Java Naming Directory Interface (JNDI) naming service, and it is created and managed outside of the applications that use it. Because the connection information is outside of applications, the time it takes to reconfigure your infrastructure when a change is made is minimal. For example, if the underlying database is moved to another server and uses another port number, the administrator must change only the relevant properties of the SQL Server 2000 Driver for JDBC data source (a DataSource object). The applications using the underlying database do not need to change because they only refer to the logical name of the SQL Server 2000 Driver for JDBC data source.
How SQL Server 2000 Driver for JDBC Data Sources Are Implemented
Microsoft ships a data source class for the SQL Server 2000 Driver for JDBC. See "SQL Server 2000 Driver for JDBC" for the name of the class.
The SQL Server 2000 Driver for JDBC data source class provided implements the following interfaces defined in the JDBC 2.0 Optional Package:
javax.sql.DataSource
javax.sql.ConnectionPoolDataSource, which enables you to implement connection pooling
NOTE: You must include the javax.sql.* and javax.naming.* classes to create and use SQL Server 2000 Driver for JDBC data sources. The SQL Server 2000 Driver for JDBC provides all the necessary JAR files that contain the required classes and interfaces.
Calling a Data Source in an Application
Applications can call a SQL Server 2000 Driver for JDBC data source using a logical name to retrieve the javax.sql.DataSource object. This object loads the SQL Server 2000 Driver for JDBC and can be used to establish a connection to the underlying database.
Once a SQL Server 2000 Driver for JDBC data source has been registered with JNDI, it can be used by your JDBC application as shown in the following example:
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/EmployeeDB");
Connection con = ds.getConnection("matt", "wwf");
In this example, the JNDI environment is first initialized. Next, the initial naming context is used to find the logical name of the SQL Server 2000 Driver for JDBC data source (EmployeeDB). The Context.lookup() method returns a reference to a Java object, which is narrowed to a javax.sql.DataSource object. Finally, the DataSource.getConnection() method is called to establish a connection with the underlying database.
Using Connection Pooling
Connection pooling allows you to reuse connections rather than create a new one every time the SQL Server 2000 Driver for JDBC needs to establish a connection to the underlying database. Connection pooling manages connection sharing across different user requests to maintain performance and reduce the number of new connections that must be created. For example, compare the following transaction sequences.
Example A: Without Connection Pooling
The client application creates a connection.
The client application sends a data access query.
The client application obtains the result set of the query.
The client application displays the result set to the end user.
The client application ends the connection.
Example B: With Connection Pooling
The client checks the connection pool for an unused connection.
If an unused connection exists, it is returned by the pool implementation; otherwise, it creates a new connection.
The client application sends a data access query.
The client application obtains the result set of the query.
The client application displays the result set to the end user.
The client application returns the connection to the pool.
NOTE: The client application still calls "close()", but the connection remains open and the pool is notified of the close request.
The pool implementation creates "real" database connections using the getPooledConnection() method of ConnectionPoolDataSource. Then, the pool implementation registers itself as a listener to the PooledConnection. When a client application requests a connection, the pool implementation (Pool Manager) assigns one of its available connections. If there is no connection available, the Pool Manager establishes a new connection and assigns it to that application. When the client application closes the connection, the Pool Manager is notified by the driver through the ConnectionEventListener interface that the connection is free and available for reuse. The pool implementation is also notified by the ConnectionEventListener interface when the client somehow corrupts the database connection, so that the pool implementation can remove that connection from the pool.
Once a SQL Server 2000 Driver for JDBC data source has been registered with JNDI, it can be used by your JDBC application as shown in the following example, typically through a third-party connection pool tool:
Context ctx = new InitialContext();
ConnectionPoolDataSource ds =
(ConnectionPoolDataSource)ctx.lookup("jdbc/EmployeeDB");
pooledConnection pcon = ds.getPooledConnection("matt", "wwf");
In this example, the JNDI environment is first initialized. Next, the initial naming context is used to find the logical name of the JDBC data source (EmployeeDB). The Context.lookup() method returns a reference to a Java object, which is narrowed to a javax.sql.ConnectionPoolDataSource object. Finally, the ConnectionPoolDataSource.getPooledConnection() method is called to establish a connection with the underlying database.
NOTE: JDBC drivers do not manage connection pooling. You must use an external connection pool manager.
> Thank you for you reply! I alway struggle at Error
> establishing socket
>
Using enterprise manager [on the client machine that you are running java on, can you connect to the database. (Do not test on another machine. Nor logged in as other user - such as a web server runs as.)
Hi all: everything on same local computer. I redownload the jdbc driver, still same mistake!!!!thank you
Do a few things:
1)
Check what port is the database running on. Is it 1433 or something else?
If it is 1433 proceed to 2.
2)
Change the authentication type for sql server from windows authentication to SQL Server authentication.
3) Create a new user (besides sa) and give a password for it. (You will not be able to give password if you use windows authentication and we want to give password and authenticate thru sql server not windows)
You can create a new user by opening Enterprise Manager -> click on database name-> right click -> select new -> new user
Then give user name. In permissions, check everything that you want access to. If it dosent give password field, just save like this.
Now go to security (below the database list on left side) and click on LOGINS. This will show users in right side. Right click the new user that you created and give it a password.
Close everything.
Now modify the java code to reflect teh new username and password. I am very sure this should work.
Hi, AAmy,
1. you can take a look at the topic "JDBC Connection between Unix and SQL Server 2000" dated Jan 16. You may try that code to test the connection.
2. for classpath, you may need add "" to your setting, such as,
C:\"Program Files"\"Microsoft SQL Server"\....
(if the error is "Error establishing socket", that meams the driver loading is fine.)
3. you may need go to "Contro panel" to restart the MSSQLServer service.
Then run the test code.
Hi All: Thank you all your replies and nice suggestions. After I use Chairagll's suggestion, creating a new user and new password then I can connect my JDBC driver with my databse.Thank you every much!!!!
