Link to home
Start Free TrialLog in
Avatar of randyzapata
randyzapata

asked on

JSP MySQL 500 error

Can someone help me fix this error.

HTTP Status 500 -

--------------------------------------------------------------------------------

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: Cannot convert value '0000-00-00 00:00:00' from column 10 to TIMESTAMP.
      org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:848)
      org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)
      org.apache.jsp.search_jsp._jspService(org.apache.jsp.search_jsp:155)
      org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)


root cause

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 10 to TIMESTAMP.
      com.mysql.jdbc.ResultSet.getTimestampFromString(ResultSet.java:6368)
      com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:2162)
      com.mysql.jdbc.ResultSet.getString(ResultSet.java:2077)
      com.mysql.jdbc.ResultSet.getString(ResultSet.java:2195)
      org.apache.jsp.search_jsp._jspService(org.apache.jsp.search_jsp:127)
      org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)


note The full stack trace of the root cause is available in the Apache Tomcat/5.5.9 logs.

*****************************

CREATE TABLE logs (Counter int not null auto_increment,
primary key (Counter),
Entered TIMESTAMP,
Title VARCHAR (80),
Salutation VARCHAR (5),
INUM VARCHAR (50),
TrainerName VARCHAR (80),
logType VARCHAR (50),
logNumber VARCHAR (50),
Country VARCHAR (80),
StartTime DATETIME,
StopTime DATETIME,
ContactedName  VARCHAR (80),
ContactedRelationship VARCHAR (30),
AdditionalInfo VARCHAR (255));


*******************************


<%@ page language="java" import="java.sql.*" %>
<HTML>
      <HEAD>
            <TITLE>Trainer</TITLE>
            <META http-equiv="UrlId" content="com.sun.sunit.sdlc.content.RegisterWebPageInfo">
            <META http-equiv="content-type" content="text/html; charset=iso-8859-1">
            <LINK title="JSCstyle" href="AdminScripts/jscdscstyle.css" type="text/css" rel="Stylesheet">
                  <META content="MSHTML 6.00.2800.1498" name="GENERATOR"></HEAD>
      <BODY bgColor="#ffffff" leftMargin="0" topMargin="0" rightMargin="0" marginwidth="0" marginheight="0">
      
      <%
      // define variables
      String Counter;
      String TIMESTAMP;
      String Title;
      String Salutation;
      String INUM;
      String TrainerName;
      String logType;
      String logNumber;
      String Country;                        
      String StartTime;            
      String StopTime;
      String ContactedName;
      String ContactedRelationship;
      String AdditionalInfo;      
      %>
                  
            <SCRIPT src="" type="text/JavaScript"></SCRIPT>
            <DIV class="vnv1" id="vnv1v0">
                  <DIV class="cornerTL">
                        <DIV class="cornerTR">
                              <DIV class="cornerBL">
                                    <DIV class="cornerBR">
                                          <DIV class="vnv1search">&nbsp;</DIV>
                                    </DIV>
                              </DIV>
                        </DIV>
                  </DIV>
            </DIV>
            <DIV class="pagetitle">Trainer log Tracking System<SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA"></SPAN></DIV> <!-- END PAGETITLE ONE LINE --> <!-- BEGIN WRAPPER TABLE, 2 COLUMN, LEFT/MAIN -->
            <TABLE height="608" cellSpacing="0" cellPadding="10" width="1375" border="0">
                  <TBODY>
                        <TR>
                              <TD vAlign="top" width="55" height="624"></TD>
                              <TD vAlign="top" width="789" height="624"><A name="skip2content"></A>
                                          <!--START TABLE-->
                                          <TABLE height="631" cellSpacing="0" cellPadding="0" width="596" border="0">
                                                <TBODY>
                                                      <TR>
                                                            <TD class="black" width="737" height="321">
                                                            
                                                                        <%

                                                                        Class.forName("org.gjt.mm.mysql.Driver");
                                                                        
                                                                        // pass database parameters to JDBC driver
                                                                        Connection Conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Trainer?user=root");

                                                                        // query statement
                                                                        Statement SQLStatement = Conn.createStatement();
                                                                        
                                                                        // generate query
                                                                        String Query = "SELECT Counter,TIMESTAMP,Title,Title,Salutation,INUM,TrainerName,logType,logNumber,Country,StartTime,StopTime,ContactedName,ContactedRelationship,AdditionalInfo FROM logs";

                                                                        // get result
                                                                        ResultSet SQLResult = SQLStatement.executeQuery(Query);

                                                                        while(SQLResult.next())
                                                                        {
                                                                        Counter = SQLResult.getString("Counter");
                                                                        TIMESTAMP = SQLResult.getString("TIMESTAMP");
                                                                        
                                                                        Title = SQLResult.getString("Title");
                                                                        Salutation = SQLResult.getString("Salutation");
                                                                        INUM = SQLResult.getString("INUM");
                                                                        TrainerName = SQLResult.getString("TrainerName");
                                                                        logType = SQLResult.getString("logType");
                                                                        logNumber = SQLResult.getString("logNumber");
                                                                        Country = SQLResult.getString("Country");
                                                                        StartTime = SQLResult.getString("StartTime");
                                                                        StopTime = SQLResult.getString("StopTime");
                                                                        ContactedName = SQLResult.getString("ContactedName");
                                                                        ContactedRelationship = SQLResult.getString("ContactedRelationship");
                                                                        AdditionalInfo = SQLResult.getString("AdditionalInfo");

                                                                        out.println("<tr><td>" + Counter + "</td><td>" +  TIMESTAMP + "</td><td>" + Title + "</td><td>" + Salutation + "</td><td>" + INUM + "</td><td>" + TrainerName + "</td><td>" + logType + "</td><td>" + logNumber + "</td><td>" + Country + "</td><td>" + StartTime + "</td><td>" + StopTime + "</td><td>" + ContactedName + "</td><td>" + ContactedRelationship + "</td><td>" + AdditionalInfo + "</td></tr>");                                                                         }
                                                                        
                                                                        // close connection
                                                                        SQLStatement.close();
                                                                        Conn.close();

                                                                        %>
                                                                        
                                                            </TD>
                                                      </TR>
                                                </TBODY></TABLE>
                              </TD>
                        </TR>
                  </TBODY></TABLE>
      </BODY>
</HTML>

Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
               String Query = "SELECT Counter,TIMESTAMP,Title,Title,Salutation,INUM,TrainerName,logType,logNumber,Country,StartTime,StopTime,ContactedName,ContactedRelationship,AdditionalInfo FROM logs";

Errr...shouldn't that be:

                String Query = "SELECT Counter,Entered,Title,Title,Salutation,INUM,TrainerName,logType,logNumber,Country,StartTime,StopTime,ContactedName,ContactedRelationship,AdditionalInfo FROM logs";

ie: Enterered, not TIMESTAMP

Why are you using getString() for everything too?

I guess it would work, but you'll be stuck if you want to format anything...

Tim
SOLUTION
Avatar of bloodredsun
bloodredsun
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
Hiya :-)  Didn't go away, but there were plenty of bbqs to attend, a new PDA to play with, and Big Brother to watch ;-)  *blush*  

Hope you had a cool bank holiday!

Roll on August ;-)
Avatar of randyzapata
randyzapata

ASKER

how would I do a null check?  Can I still output the data if I have a null in the database.  I am new to all this.

Thanks,

Randall
ASKER CERTIFIED SOLUTION
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
It's because you are trying to get everything as a String, no matter what it really is...  The database is choking trying to convert a null DATETIME to a String (as bloodredsun said)

StartTime and StopTime will be NULL if they are NULL in the database...
Avatar of randyzapata
randyzapata

ASKER

Thank you so much for helping me out,

Randall
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
Glad I could help :-)

Good luck with it all :-)