asked on

database connection

Hi Experts,

I am having trouble connecting to a mysql database. For simplicity i have two files 1) dbconnect.jsp and 2) display_results.jsp.

--------------dbconnect.jsp------------------
<%
      String inServer = "irpclanadmin1";
      String inDBName = "leave";
      String DATABASE_URL = "odbc:mysql://" + inServer + ":3306/" + inDBName;
      Class.forName("com.mysql.odbc.Driver").newInstance();
      Connection con = DriverManager.getConnection(DATABASE_URL, "username","pass");
%>

-------------display_results.jsp
<%@ page language ="java" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ include file="dbconnect.jsp" %>
<%
      Statement stmt = con.createStatement();

      ResultSet check = stmt.executeQuery("SELECT userSName FROM userdetails");
      String userName = "";

      while(check.next())
      {
            userName = check.getString(1);
            out.print(userName);
      }
%>

The problem is i am getting this displayed when i open display_results.jsp
javax.servlet.ServletException: com.mysql.odbc.Driver
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:471)
at org.apache.jsp.display_0005fresults$jsp._jspService(display_0005fresults$jsp.java:100)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)

...
...
Any help would be greatly appreciated
Raftor....

SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raftor

ASKER

Sorry it is "jdbc" alright but i when i pasted that in i was trying to connect using the odbc....

CEHJ

Please post the error(s) you get after the corrections i mentioned

Raftor

ASKER

its the exact same error really:

javax.servlet.ServletException: com.mysql.jdbc.Driver
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:471)
at org.apache.jsp.display_0005fresults$jsp._jspService(display_0005fresults$jsp.java:93)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
at java.lang.Thread.run(Thread.java:479)

root cause

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1406)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappCassLoader.java:1254)
at org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:198)
at org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:132)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:310)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:115)
at org.apache.jsp.display_0005fresults$jsp._jspService(display_0005fresults$jsp.java:73)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContextValve.invok(StandardContextValve.java:190)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
t org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
t org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
at java.lang.Thread.run(Thread.java:479)

Raftor

ASKER

This is the one file i am using now:

<%@ page language ="java" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>

<%
      String inServer = "irpclanadmin1";
      String inDBName = "leave";
      String DATABASE_URL = "jdbc:mysql://" + inServer + ":3306/" + inDBName;

      Class.forName("com.mysql.jdbc.Driver").newInstance();

      Connection con = DriverManager.getConnection(DATABASE_URL, "user", "pass");
      out.print("in display");
      Statement stmt = con.createStatement();

      ResultSet check = stmt.executeQuery("SELECT userSName FROM userdetails");
      int counter = 0;
      String userName = "";

      while(check.next())
      {
            counter += 1;
            userName = check.getString(1);
            out.print(userName);
      }
%>

Raftor

ASKER

This is probably a more helpfull output. This was displayed when i tryed to run the followin code:
<%

      String inServer = "irpclanadmin1";
      String inDBName = "leave";
      String DATABASE_URL = "jdbc:mysql://" + inServer + ":3306/" + inDBName;

      Class.forName("com.mysql.jdbc.Driver").newInstance();
      Connection con = DriverManager.getConnection(DATABASE_URL, "user", "pass");

%>

**********************output***************

org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred between lines: 1 and 10 in the jsp file: /leave/dbconnect.jsp

Generated servlet error:
C:\tomcat4\work\Standalone\localhost\examples\leave\dbconnect$jsp.java:61: Class org.apache.jsp.Connection not found.
Connection con = DriverManager.getConnection(DATABASE_URL, "crafter", "colin");
^

An error occurred between lines: 1 and 10 in the jsp file: /leave/dbconnect.jsp

Generated servlet error:
C:\tomcat4\work\Standalone\localhost\examples\leave\dbconnect$jsp.java:61: Undefined variable or class name: DriverManager
Connection con = DriverManager.getConnection(DATABASE_URL, "crafter", "colin");
       ^
2 errors

Raftor

ASKER

Sorry, that was just displayed because i didnt have the <% include.... %> in the code....

ASKER CERTIFIED SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CEHJ

>>
> Class.forName("com.mysql.odbc.Driver").newInstance();

That should be:

Class.forName("com.mysql.jdbc.Driver");
>>

Already mentioned

b612_forever

jdbc or odbc is depend on wat type of driver used. Although it is recommended to used JDBC type 4 driver, but there is nothing wrong using JDBC-ODBC bridge.

I think objects have the point, do you sure that u have include the neessasary .jar file in ur classpath? If not, just put in to the folder mentioned by objects.

Good Luck.

Raftor

ASKER

Nice one lads. Im not back at work untill tomorrow so ill try that then. Thanks again and ill be in touch....

Raftor...

Raftor

ASKER

By the way i am running the Tomcat server on one machine and connecting to the mysql database remotely on another machine, i wonder does that make a difference??

CEHJ

>> but there is nothing wrong using JDBC-ODBC bridge.

Can you tell us which bridge is of type:

>>com.mysql.odbc.Driver

?

b612_forever

>> Can you tell us which bridge is of type:

oh, very sorry. I have been not using JDBC-ODBC for a long period of time, so I have forget about the Driver class for JDBC-ODBC is "sun.jdbc.odbc.JdbcOdbcDriver".....:D

By the way, are u sing the Connector/J driver or MM.MySQL driver?

if you are using MM.MySQL, it should be "org.gjt.mm.mysql.Driver"

correct me if I am wrong.Thanks.

Raftor

ASKER

Thanks lads...

CEHJ

:-)