*
The moose likes JDBC and the fly likes SQL Exception No suitable driver Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Exception No suitable driver" Watch "SQL Exception No suitable driver" New topic
Author

SQL Exception No suitable driver

Mandar Velankar
Ranch Hand

Joined: Jul 31, 2006
Posts: 32
Hi,
can anybody help me for problem i am tackling,

I am using Tomcat 5.0.30 , oracle9i Release 9.0.1.1.1 and j2sdk 1.4.2_04 for

my work.When I am trying to login to my system ,I got following exception,

Caught SQLException No suitable driver

I tried using ojdbc14.jar by adding to WEB-INF/lib folder, even classes12.zip

in WEB-INF/lib folder, but it cann't work. I am using oracle thin driver

'oracle.jdbc.driver.OracleDriver'

What can I do?please help me and thanks a lot.

waiting for yor reply.

Regards

Mandar


Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

UNless you are using an old version of the JDK you need the ojdbc14.jar in your classpath, not classes12.zip.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Mandar Velankar
Ranch Hand

Joined: Jul 31, 2006
Posts: 32
Hi Paul ,
Thanks. I tried that one, I am using Windows Server 2003, i set classpath by adding environment varible 'classpath' using control panel - system properties.but it doesn't work.can we required to check for dll file for such driver. I doesn't have any idea but heard somewhere. can anybody help?
Thanks a lot.
Regards
Mandar Velankar
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I tried that one, I am using Windows Server 2003, i set classpath by adding environment varible 'classpath' using control panel - system properties.but it doesn't work

No need to change the system classpath. Just put it in Tomcat's classpath (see the Tomcat documentation).


can we required to check for dll file for such driver

Not sure what you mean here, but if you are using the thin driver you don't need anything else installed on your machine. So no dll to look for.

Can you show us your server.xml?
Mandar Velankar
Ranch Hand

Joined: Jul 31, 2006
Posts: 32
Hi Paul,
Thanks a lot for your explnation.I removed classpath varible set earlier.I kept ojdbc14.jar in WEB-INF/lib of my web application.But still it is not working.I will list my login process page and server.xml page.

server.xml

<!-- Example Server Configuration File -->
<!-- Note that component elements are nested corresponding to their
parent-child relationships with each other -->

<!-- A "Server" is a singleton element that represents the entire JVM,
which may contain one or more "Service" instances. The Server
listens for a shutdown command on the indicated port.

Note: A "Server" is not itself a "Container", so you may not
define subcomponents such as "Valves" or "Loggers" at this level.
-->

<Server port="8005" shutdown="SHUTDOWN" debug="0">


<!-- Comment these entries out to disable JMX MBeans support -->
<!-- You may also configure custom components (e.g. Valves/Realms) by
including your own mbean-descriptor file(s), and setting the
"descriptors" attribute to point to a ';' seperated list of paths
(in the ClassLoader sense) of files to add to the default list.
e.g. descriptors="/com/myfirm/mypackage/mbean-descriptor.xml"
-->
<Listener className="org.apache.catalina.mbeans.ServerLifecycleListener"
debug="0"/>
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"
debug="0"/>

<!-- Global JNDI resources -->
<GlobalNamingResources>

<!-- Test entry for demonstration purposes -->
<Environment name="simpleValue" type="java.lang.Integer" value="30"/>

<!-- Editable user database that can also be used by
UserDatabaseRealm to authenticate users -->
<Resource name="UserDatabase" auth="Container"
type="org.apache.catalina.UserDatabase"
description="User database that can be updated and saved">
</Resource>
<ResourceParams name="UserDatabase">
<parameter>
<name>factory</name>
<value>org.apache.catalina.users.MemoryUserDatabaseFactory</value>
</parameter>
<parameter>
<name>pathname</name>
<value>conf/tomcat-users.xml</value>
</parameter>
</ResourceParams>

</GlobalNamingResources>

<!-- A "Service" is a collection of one or more "Connectors" that share
a single "Container" (and therefore the web applications visible
within that Container). Normally, that Container is an "Engine",
but this is not required.

Note: A "Service" is not itself a "Container", so you may not
define subcomponents such as "Valves" or "Loggers" at this level.
-->

<!-- Define the Tomcat Stand-Alone Service -->
<Service name="Catalina">

<!-- A "Connector" represents an endpoint by which requests are received
and responses are returned. Each Connector passes requests on to the
associated "Container" (normally an Engine) for processing.

By default, a non-SSL HTTP/1.1 Connector is established on port 8080.
You can also enable an SSL HTTP/1.1 Connector on port 8443 by
following the instructions below and uncommenting the second Connector
entry. SSL support requires the following steps (see the SSL Config
HOWTO in the Tomcat 5 documentation bundle for more detailed
instructions):
* If your JDK version 1.3 or prior, download and install JSSE 1.0.2 or
later, and put the JAR files into "$JAVA_HOME/jre/lib/ext".
* Execute:
%JAVA_HOME%\bin\keytool -genkey -alias tomcat -keyalg RSA (Windows)
$JAVA_HOME/bin/keytool -genkey -alias tomcat -keyalg RSA (Unix)
with a password value of "changeit" for both the certificate and
the keystore itself.

By default, DNS lookups are enabled when a web application calls
request.getRemoteHost(). This can have an adverse impact on
performance, so you can disable it by setting the
"enableLookups" attribute to "false". When DNS lookups are disabled,
request.getRemoteHost() will return the String version of the
IP address of the remote client.
-->

<!-- Define a non-SSL Coyote HTTP/1.1 Connector on port specified
during installation -->
<Connector port="2020"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="100"
debug="0" connectionTimeout="20000"
disableUploadTimeout="true" />
<!-- Note : To disable connection timeouts, set connectionTimeout value
to 0 -->

<!-- Note : To use gzip compression you could set the following properties :

compression="on"
compressionMinSize="2048"
noCompressionUserAgents="gozilla, traviata"
compressableMimeType="text/html,text/xml"
-->

<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<!--
<Connector port="8443"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true"
acceptCount="100" debug="0" scheme="https" secure="true"
clientAuth="false" sslProtocol="TLS" />
-->

<!-- Define a Coyote/JK2 AJP 1.3 Connector on port 8009 -->
<Connector port="8009"
enableLookups="false" redirectPort="8443" debug="0"
protocol="AJP/1.3" />

<!-- Define a Proxied HTTP/1.1 Connector on port 8082 -->
<!-- See proxy documentation for more information about using this. -->
<!--
<Connector port="8082"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false"
acceptCount="100" debug="0" connectionTimeout="20000"
proxyPort="80" disableUploadTimeout="true" />
-->

<!-- An Engine represents the entry point (within Catalina) that processes
every request. The Engine implementation for Tomcat stand alone
analyzes the HTTP headers included with the request, and passes them
on to the appropriate Host (virtual host). -->

<!-- You should set jvmRoute to support load-balancing via JK/JK2 ie :
<Engine name="Standalone" defaultHost="localhost" debug="0" jvmRoute="jvm1">
-->

<!-- Define the top level container in our container hierarchy -->
<Engine name="Catalina" defaultHost="localhost" debug="0">

<!-- The request dumper valve dumps useful debugging information about
the request headers and cookies that were received, and the response
headers and cookies that were sent, for all requests received by
this instance of Tomcat. If you care only about requests to a
particular virtual host, or a particular application, nest this
element inside the corresponding <Host> or <Context> entry instead.

For a similar mechanism that is portable to all Servlet 2.4
containers, check out the "RequestDumperFilter" Filter in the
example application (the source for this filter may be found in
"$CATALINA_HOME/webapps/examples/WEB-INF/classes/filters").

Request dumping is disabled by default. Uncomment the following
element to enable it. -->
<!--
<Valve className="org.apache.catalina.valves.RequestDumperValve"/>
-->

<!-- Global logger unless overridden at lower levels -->
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="catalina_log." suffix=".txt"
timestamp="true"/>

<!-- Because this Realm is here, an instance will be shared globally -->

<!-- This Realm uses the UserDatabase configured in the global JNDI
resources under the key "UserDatabase". Any edits
that are performed against this UserDatabase are immediately
available for use by the Realm. -->
<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
debug="0" resourceName="UserDatabase"/>

<!-- Comment out the old realm but leave here for now in case we
need to go back quickly -->
<!--
<Realm className="org.apache.catalina.realm.MemoryRealm" />
-->

<!-- Replace the above Realm with one of the following to get a Realm
stored in a database and accessed via JDBC -->

<!--
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
driverName="org.gjt.mm.mysql.Driver"
connectionURL="jdbc:mysql://localhost/authority"
connectionName="test" connectionPassword="test"
userTable="users" userNameCol="user_name" userCredCol="user_pass"
userRoleTable="user_roles" roleNameCol="role_name" />
-->

<!--
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
driverName="oracle.jdbc.driver.OracleDriver"
connectionURL="jdbcracle:thin:@ntserver:1521:ORCL"
connectionName="scott" connectionPassword="tiger"
userTable="users" userNameCol="user_name" userCredCol="user_pass"
userRoleTable="user_roles" roleNameCol="role_name" />
-->

<!--
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
driverName="sun.jdbc.odbc.JdbcOdbcDriver"
connectionURL="jdbcdbc:CATALINA"
userTable="users" userNameCol="user_name" userCredCol="user_pass"
userRoleTable="user_roles" roleNameCol="role_name" />
-->

<!-- Define the default virtual host
Note: XML Schema validation will not work with Xerces 2.2.
-->

<Host name="localhost" debug="0" appBase="webapps"
unpackWARs="true" autoDeploy="true"
xmlValidation="false" xmlNamespaceAware="false">

<!-- Defines a cluster for this node,
By defining this element, means that every manager will be changed.
So when running a cluster, only make sure that you have webapps in there
that need to be clustered and remove the other ones.
A cluster has the following parameters:

className = the fully qualified name of the cluster class

name = a descriptive name for your cluster, can be anything

debug = the debug level, higher means more output

mcastAddr = the multicast address, has to be the same for all the nodes

mcastPort = the multicast port, has to be the same for all the nodes

mcastBindAddr = bind the multicast socket to a specific address

mcastTTL = the multicast TTL if you want to limit your broadcast

mcastSoTimeout = the multicast readtimeout

mcastFrequency = the number of milliseconds in between sending a "I'm alive" heartbeat

mcastDropTime = the number a milliseconds before a node is considered "dead" if no heartbeat is received

tcpThreadCount = the number of threads to handle incoming replication requests, optimal would be the same amount of threads as nodes

tcpListenAddress = the listen address (bind address) for TCP cluster request on this host,
in case of multiple ethernet cards.
auto means that address becomes
InetAddress.getLocalHost().getHostAddress()

tcpListenPort = the tcp listen port

tcpSelectorTimeout = the timeout (ms) for the Selector.select() method in case the OS
has a wakup bug in java.nio. Set to 0 for no timeout

printToScreen = true means that managers will also print to std.out

expireSessionsOnShutdown = true means that

useDirtyFlag = true means that we only replicate a session after setAttribute,removeAttribute has been called.
false means to replicate the session after each request.
false means that replication would work for the following piece of code:
<%
HashMap map = (HashMap)session.getAttribute("map");
map.put("key","value");
%>
replicationMode = can be either 'pooled', 'synchronous' or 'asynchronous'.
* Pooled means that the replication happens using several sockets in a synchronous way. Ie, the data gets replicated, then the request return. This is the same as the 'synchronous' setting except it uses a pool of sockets, hence it is multithreaded. This is the fastest and safest configuration. To use this, also increase the nr of tcp threads that you have dealing with replication.
* Synchronous means that the thread that executes the request, is also the
thread the replicates the data to the other nodes, and will not return until all
nodes have received the information.
* Asynchronous means that there is a specific 'sender' thread for each cluster node,
so the request thread will queue the replication request into a "smart" queue,
and then return to the client.
The "smart" queue is a queue where when a session is added to the queue, and the same session
already exists in the queue from a previous request, that session will be replaced
in the queue instead of replicating two requests. This almost never happens, unless there is a
large network delay.
-->
<!--
When configuring for clustering, you also add in a valve to catch all the requests
coming in, at the end of the request, the session may or may not be replicated.
A session is replicated if and only if all the conditions are met:
1. useDirtyFlag is true or setAttribute or removeAttribute has been called AND
2. a session exists (has been created)
3. the request is not trapped by the "filter" attribute

The filter attribute is to filter out requests that could not modify the session,
hence we don't replicate the session after the end of this request.
The filter is negative, ie, anything you put in the filter, you mean to filter out,
ie, no replication will be done on requests that match one of the filters.
The filter attribute is delimited by ;, so you can't escape out ; even if you wanted to.

filter=".*\.gif;.*\.js;" means that we will not replicate the session after requests with the URI
ending with .gif and .js are intercepted.

The deployer element can be used to deploy apps cluster wide.
Currently the deployment only deploys/undeploys to working members in the cluster
so no WARs are copied upons startup of a broken node.
The deployer watches a directory (watchDir) for WAR files when watchEnabled="true"
When a new war file is added the war gets deployed to the local instance,
and then deployed to the other instances in the cluster.
When a war file is deleted from the watchDir the war is undeployed locally
and cluster wide
-->

<!--
<Cluster className="org.apache.catalina.cluster.tcp.SimpleTcpCluster"
managerClassName="org.apache.catalina.cluster.session.DeltaManager"
expireSessionsOnShutdown="false"
useDirtyFlag="true">

<Membership
className="org.apache.catalina.cluster.mcast.McastService"
mcastAddr="228.0.0.4"
mcastPort="45564"
mcastFrequency="500"
mcastDropTime="3000"/>

<Receiver
className="org.apache.catalina.cluster.tcp.ReplicationListener"
tcpListenAddress="auto"
tcpListenPort="4001"
tcpSelectorTimeout="100"
tcpThreadCount="6"/>

<Sender
className="org.apache.catalina.cluster.tcp.ReplicationTransmitter"
replicationMode="pooled"/>

<Valve className="org.apache.catalina.cluster.tcp.ReplicationValve"
filter=".*\.gif;.*\.js;.*\.jpg;.*\.htm;.*\.html;.*\.txt;"/>

<Deployer className="org.apache.catalina.cluster.deploy.FarmWarDeployer"
tempDir="/tmp/war-temp/"
deployDir="/tmp/war-deploy/"
watchDir="/tmp/war-listen/"
watchEnabled="false"/>
</Cluster>
-->



<!-- Normally, users must authenticate themselves to each web app
individually. Uncomment the following entry if you would like
a user to be authenticated the first time they encounter a
resource protected by a security constraint, and then have that
user identity maintained across *all* web applications contained
in this virtual host. -->
<!--
<Valve className="org.apache.catalina.authenticator.SingleSignOn"
debug="0"/>
-->

<!-- Access log processes all requests for this virtual host. By
default, log files are created in the "logs" directory relative to
$CATALINA_HOME. If you wish, you can specify a different
directory with the "directory" attribute. Specify either a relative
(to $CATALINA_HOME) or absolute path to the desired directory.
-->
<!--
<Valve className="org.apache.catalina.valves.AccessLogValve"
directory="logs" prefix="localhost_access_log." suffix=".txt"
pattern="common" resolveHosts="false"/>
-->

<!-- Logger shared by all Contexts related to this virtual host. By
default (when using FileLogger), log files are created in the "logs"
directory relative to $CATALINA_HOME. If you wish, you can specify
a different directory with the "directory" attribute. Specify either a
relative (to $CATALINA_HOME) or absolute path to the desired
directory.-->
<Logger className="org.apache.catalina.logger.FileLogger"
directory="logs" prefix="localhost_log." suffix=".txt"
timestamp="true"/>

</Host>

</Engine>

</Service>

</Server>
________________________________________________________________________

masterdatabase.jsp

<%Connection con=null;
String url="jdbcracle:thin:@localhost:1521:gj1";
String username="master";
String password="master";
%>
_________________________________________________________________________
verify1.jsp (page where authentication done)

<%@ page language="java" import="java.sql.Connection,java.sql.DriverManager,java.sql.Statement,java.sql.ResultSet,java.io.*,java.sql.SQLException"%>
<%@ include file="masterdatabase.jsp"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META HTTP-EQUIV="Pragma" CONTENT="no-cache"/><META HTTP-EQUIV="Expires" CONTENT="-1"/>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
<%
String usercode=request.getParameter("usercode");
String upassword=request.getParameter("password");
String logyear = "";
String location=null;
String emp_sql = "";
try
{
this.getClass().getClassLoader().loadClass("oracle.jdbc.driver.OracleDriver");
//Class.forName("oracle.jdbc.driver.OracleDriver");
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//String url="jdbcracle:thin:@localhost:1521:gj1";
//String username="master";
//String password = "master";
con=DriverManager.getConnection(url,username,password);
//con=DriverManager.getConnection("jdbcdbcradsn","master","master");
Statement stat1;
stat1=con.createStatement();
//String emp_sql="select vc_user_name, name,vc_user_code,OFFICESHORTNM,count(*) as no from v_users where vc_user_name like '"+usercode+"' and vc_user_password like '"+upassword+"' group by vc_user_name,name,vc_user_code,OFFICESHORTNM" ;
emp_sql="select vc_user_name, name,vc_user_code,loc,count(*) as no from v_users where vc_user_name like '"+usercode+"' and vc_user_password like '"+upassword+"' and vc_status ='A' group by vc_user_name,name,vc_user_code,loc" ;

//out.println(emp_sql);
ResultSet rs1=stat1.executeQuery(emp_sql);
int no=0;
String user=null;
String name=null;
String user_code=null;
//String vc_status=null;
String error=null;
while (rs1.next())
{
user=rs1.getString("vc_user_name");
name=rs1.getString("name");
user_code=rs1.getString("vc_user_code");
//vc_status=rsl.getString("vc_status");
no=rs1.getInt("no");
//location=rs1.getString("loc");
out.println("no " + no);
//String no=rs1.getString("vc_emp_name");
out.println(" Login Status" + no);
}
if (no!=1)
{
error="Not a valid user";
out.println("Not a valid user");
response.sendRedirect("verify.jsp?error="+error) ;
//}
}
else
{
//out.println("old session = " +session.getAttribute("user"));

//if (session.getAttribute("user")!=null)
//{

//out.println("first if condition");

//if((session.getAttribute("user")).equals(empname))
//{
//out.println("first if condition");

//error="You have already logged in the system";
//out.println("Invalid Username and Password");
//response.sendRedirect("verify.jsp?error="+error) ;
//}
//}
//else
//{

java.util.Date now= new java.util.Date();
int mnth = now.getMonth()+1;
int nextyr = 1900+now.getYear()+1;
int curryr = 1900+now.getYear();
int lastyr = 1900+now.getYear()-1;

String cyr = ""+curryr;
String nyr = "";
String lyr = ""+lastyr;

String fyr = "";


if(mnth >= 4 && mnth <= 12){
fyr = cyr.substring(2,4);
nyr = ""+nextyr;
}else{
fyr = lyr.substring(2,4);
nyr = ""+curryr;
}

logyear = fyr+nyr.substring(2,4);

session.setAttribute("logyear",logyear);
session.setAttribute("user",user);
session.setAttribute("name",name );
session.setAttribute("user_code",user_code);
session.setAttribute("user_name",usercode);
session.setAttribute("doc",null);
session.setAttribute("location",location);
//response.sendRedirect("dmenu/iframetest.jsp?error="+error) ;
//response.sendRedirect("menulist.jsp");


%>
<script language="javascript">
//newWindow = window.open("iframetest.jsp",'gjerp','width=790,height=480,scrollbars=yes,menubar=yes,status=yes,resizable=yes,directories=false,location=false,left=0,top=0');
//if (window.focus) {newWindow.focus()}
//history.back()
</script>
<%

response.sendRedirect("iframetest.jsp") ;



//t.println("welcome " + usercode);
}
}
catch(ClassNotFoundException cnfe)
{
out.println("Caught ClassNotFoundException " + cnfe.getMessage());
}
catch(IOException ioe)
{
out.println("Caught IOException " + ioe.getMessage());
}

catch(SQLException sqle){
out.println(emp_sql);
out.println("Caught SQLException " + sqle.getMessage());
}
finally
{
try
{
if( con!=null)
{
con.close();
//con=null;
}
}
catch(SQLException sqle)
{
out.println("Caught SQLException from final catch " +sqle.getMessage());
}

catch(Exception e)
{
out.println("Caught exception from final catch " + e.getMessage());
}
}

%>

</HEAD>
<BODY>

</BODY>
</HTML>

Thanks for your help , this might give you all idea about my application.

Thanks again.Waiting for your reply.

:p
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I kept ojdbc14.jar in WEB-INF/lib of my web application.But still it is not working

Normally in web applications you would not put your driver in your application's classpath, you would put it in Tomcat's classpath. However this is becasue normally you would probably use a DataSource rather than getting a connection manually.

A couple of pointers: you have JSP code and Java code mixed. Not a good idea. Its messy and difficult to maintain and it ties the application view and logic tightly together. You also probably should consider using a DataSource; this way you only need to have JDBC configuration details at one point in you application, rather than littered throughout.

Finally, your problem! The exception you are getting occurs because the DriverManager cannot find a suitable Driver for the URL you supplied at:

This happens for two reasons: either you have not registered the driver, or your URL syntax is wrong. The class oracle.jdbc.driver.OracleDriver is the correct class to register, and you know it is on the classpath because:

would throw a ClassNotFoundException if it could not find your driver. However, this method will not register the Driver, since it loaded the Driver but does not initialize it. Use Class.forName() instead.

There is also the problem of your URL. Looking though your code, I don't see where you assign a value to this. Is it ever anything other than null?
[ April 03, 2007: Message edited by: Paul Sturrock ]
Mandar Velankar
Ranch Hand

Joined: Jul 31, 2006
Posts: 32
Hi Paul,
Thanks a lot. Now everything is working.I followed all your instructions and learn a lot from such example. Now I learned how to use oracle thin dirver.
Thanks again for such great help.
Regards
Mandar Velankar
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Exception No suitable driver
 
Similar Threads
mysql JDBC driver classpath problem.
No suitable driver
database connection in jsp of type 4 driver
Regarding including jar files for directory
ClassNotFoundException while running jdbc program