JSP write to MySQL problem
Hi, I will be grateful if anyone can share me some pointers or solution to this tutorial that I am currently stuck with. Thank you.
[All my other pages connect to the Database]
Problem Statement: I am doing this OnlineStore (ShoppingCart)[JSP] Tutorial. It is trying to get Data from user's form entry and load it in the database. But it is unable to so. Throwing an error.
This is the problematic code:-
long OrderID = ((org.gjt.mm.mysql.PreparedStatementstatement).getLastInsertID();
This is the Error :-
exception
org.apache.jasper.JasperException: Unable to compileclassfor JSP
An error occurred at line: 15 in the jsp file: /OnlineStore/ProcessCheckout.jsp
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java:93: cannot find symbol
symbol :class PreparedStatement
location:package org.gjt.mm.mysql
long OrderID = ((org.gjt.mm.mysql.PreparedStatement)statement).getLastInsertID();
^
1 error
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:332)
org.apache.jasper.compiler.Compiler.generateClass(Compiler.java:412)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:472)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:451)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:439)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:511)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:295)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
This is my codes
<%@ page import="OnlineStore.*" %>
<%@ page import="java.sql.*,java.io.*,java.util.*" %>
<jsp:useBean id="sCart" class="OnlineStore.ShoppingCart"
scope="session"/>
<html>
<head>
<title>Process Order</title>
</head>
<body bgcolor="#FFFFFF">
<h3>Process Order</h3>
<P>
<%
Class.forName("org.gjt.mm.mysql.Driver");
java.sql.Connection connection =
java.sql.DriverManager.getConnection
("jdbc:mysql://localhost/onlinestore","root","");
String query ="INSERT INTO orders VALUES
('',?,?,?,?,?,?,?,?,?)";
java.sql.PreparedStatement statement =
connection.prepareStatement(query);
statement.setString(1,request.getParameter("Title"));
statement.setString(2,request.getParameter("FirstName"));
statement.setString(3,request.getParameter("Surname"));
statement.setString(4,request.getParameter("Address"));
statement.setString(5,request.getParameter("City"));
statement.setString(6,request.getParameter("Zip"));
statement.setString(7,request.getParameter("State"));
statement.setString(8,request.getParameter("CreditCardType"));
statement.setString(9,request.getParameter("CreditCardNo"));
statement.executeUpdate();
long OrderID = ((org.gjt.mm.mysql.PreparedStatement)
statement).getLastInsertID();
String orderDetailsQuery ="INSERT INTO OrderDetails VALUES
('',?,?,?)";
Enumeration products = sCart.getProducts();
while(products.hasMoreElements()){
Product product = (Product)
products.nextElement();
statement = connection.prepareStatement
(orderDetailsQuery );
statement.setLong(1,OrderID);
statement.setInt(2,Integer.parseInt
(product.getId()));
statement.setInt(3,product.getQuantity());
statement.executeUpdate();
}
statement.close();connection.close();
sCart.emptyCart();
%>
Your order will be processed and shipped within 24 hours. Your
Order Number is <%=OrderID %><P>
<A HREF="DisplayCatalog.jsp">Display Catalog</A>
</BODY>
</HTML>
Nevertheless I am still lost. Please do help me. Thank You Everyone. :)
[5333 byte] By [
rimeteaa] at [2007-10-2 16:34:22]
1 - You shouldn't be writing SQL code in your JSPs. This sort of code belongs in a bean.
2 - Whereever possible you should be using just the standard javax.sql classes. You seem to be wanting to get the last id inserted.
Is there any reason you can't use the method getGeneratedKeys of a statement?
What is the class org.gjt.mm.mysql.PreparedStatement?
What version of java are you using?
What version of JSP?
MySQL version?
Do you have an up-to-date JDBC driver?
Thanks for the reply.
I am using MySQL5.0 . I am merely following tutorial from a book.
I'm new to JSP and MySQL and infact programming.
Nevertheless I read from this old 'post' of a similar problem and they got it solved but I could not really quite understand it.
Please guide me in showing how to get the tutorial working.
greatly apperciate your reply.
This is the link that shows the similar problem and sugest solution that help others to work.:--
http://forum.java.sun.com/thread.jspa?threadID=512118&start=0&tstart=0
Once again Thanks.
I am using Apache Tomcat 5.0MySql server 5.0Java version 1.5.0_06JDBC driver is mysql-connector-java-3.1.12-binThanks :)
How old is the book you are following?
The class org.gjt.mm.mysql.PreparedStatement no longer exists.
It was renamed to com.mysql.PreparedStatement.
You might try:
long OrderID = ((com.mysql.PreparedStatementstatement).getLastInsertID();
However, this might be even better again:
I think it should work - try it out.
long OrderId = 0;
ResultSet lastIdRs = statement.getGeneratedKeys();
if (lastIdRs.next()){
OrderId = lastIdRs.getLong();
}
Hi I am following a 2001 book but I suspect it duplicate alot of codes from else where.
Anyway It did't work out.
These are the errors for the second suggested line :-
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
org.apache.jasper.JasperException: Unable to compile class for JSP
An error occurred at line: 67 in the jsp file: /OnlineStore/ProcessCheckout.jsp
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java:123: cannot find symbol
symbol : method getLong()
location: interface java.sql.ResultSet
OrderId = lastIdRs.getLong();
^
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java:139: cannot find symbol
symbol : variable OrderID
location: class org.apache.jsp.OnlineStore.ProcessCheckout_jsp
statement.setLong(1,OrderID);
^
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java:159: cannot find symbol
symbol : variable OrderID
location: class org.apache.jsp.OnlineStore.ProcessCheckout_jsp
out.print(OrderID );
^
Generated servlet error:
Note: C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java uses or overrides a deprecated API.
Generated servlet error:
Note: Recompile with -Xlint:deprecation for details.
3 errors
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:332)
org.apache.jasper.compiler.Compiler.generateClass(Compiler.java:412)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:472)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:451)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:439)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:511)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:295)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
note The full stack trace of the root cause is available in the Apache Tomcat/5.0.28 logs.
For the first suggested line:-
It did't work out ...
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
org.apache.jasper.JasperException: Unable to compile class for JSP
An error occurred at line: 64 in the jsp file: /OnlineStore/ProcessCheckout.jsp
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java:120: ')' expected
long OrderID = ((com.mysql.PreparedStatementstatement).getLastInsertID();
^
1 error
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:332)
org.apache.jasper.compiler.Compiler.generateClass(Compiler.java:412)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:472)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:451)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:439)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:511)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:295)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
note The full stack trace of the root cause is available in the Apache Tomcat/5.0.28 logs.
--
Please help I am so very lost.
Thank You
Sorry the above is because of the bracket.
This is the new error.
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
org.apache.jasper.JasperException: Unable to compile class for JSP
An error occurred at line: 64 in the jsp file: /OnlineStore/ProcessCheckout.jsp
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java:120: package com.mysql does not exist
long OrderID = ((com.mysql.PreparedStatementstatement).getLastInsertID());
^
An error occurred at line: 64 in the jsp file: /OnlineStore/ProcessCheckout.jsp
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java:120: illegal start of type
long OrderID = ((com.mysql.PreparedStatementstatement).getLastInsertID());
^
An error occurred at line: 64 in the jsp file: /OnlineStore/ProcessCheckout.jsp
Generated servlet error:
Note: C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\_\org\apache\jsp\OnlineStore\ProcessCheckout_jsp.java uses or overrides a deprecated API.
An error occurred at line: 64 in the jsp file: /OnlineStore/ProcessCheckout.jsp
Generated servlet error:
Note: Recompile with -Xlint:deprecation for details.
2 errors
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:332)
org.apache.jasper.compiler.Compiler.generateClass(Compiler.java:412)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:472)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:451)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:439)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:511)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:295)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
note The full stack trace of the root cause is available in the Apache Tomcat/5.0.28 logs.
Thank you please guide me through
Hi,.. Thanks I got it figured out already. I did not uncheck something when installing MySQL
