wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL error" Watch "SQL error" New topic
Author

SQL error

Aris Doxakis
Ranch Hand

Joined: Dec 05, 2004
Posts: 136

Hello all,
I have a ordersDone page for administrative use on a e-bookstore.Im trying to find the books selled retrieving the data from a database.This is my code:
<%
Connection con;
con = null;

try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/e_store");
}
catch (ClassNotFoundException e) {
}
catch (SQLException e) {
}

Statement stmtOrder = con.createStatement();
Statement stmtOrderProduct = con.createStatement();
Statement stmtProduct = con.createStatement();

ResultSet rsOrder = stmtOrder.executeQuery("Select * From Orders where OrderStatus = 'Done';");

while (rsOrder.next())
{
String CustomerID = rsOrder.getString("Customer_ID");
String OrderID = rsOrder.getString("Order_ID");
String AddressID = rsOrder.getString("Address_ID");
%>
<%=CustomerID%>----<%=OrderID%>------<%=AddressID%>
<%
ResultSet rsOrderProduct = stmtOrderProduct.executeQuery("select * from Order_Products where Order_ID = OrderID grouped by ProductID;");

while (rsOrderProduct.next())
{

String ProductID = rsOrder.getString("ProductID");
String ProdQuantity = rsOrderProduct.getString("ProductQuantity");
ResultSet rsProduct = stmtProduct.executeQuery("select * from Products where ISBN = ProductID;");
String Title = rsProduct.getString("Title");
%>
<%=ProductID%>-----<%=Title%>-----<%=ProdQuantity%>
<%
}
}
stmtOrder.close();
stmtOrderProduct.close();
stmtProduct.close();
rsOrder.close();
%>

i get null pointer exception though all my tables contain data..
leavung this to the experts.
thnx in advance
;)
Muhammad Saifuddin
Ranch Hand

Joined: Dec 06, 2005
Posts: 1321



productID & OrderID are string type variable. i am preety sure that these values are not inside in your Product_ID and Order_ID Field..

Please just use concatenate by "+" sign to comparing the value of its String type variable

JSP-JDBC Example
[ August 28, 2006: Message edited by: Saif Uddin ]

Saifuddin..
[Blog][Linkedin] How To Ask Questions On JavaRanch My OpenSource
Aris Doxakis
Ranch Hand

Joined: Dec 05, 2004
Posts: 136

you were right about that.
Know i have in a other oage same sql error.Null pointer exception
my code is:
<%@ page contentType="text/html; charset=windows-1253" language="java" import="java.text.*,java.sql.*,java.util.*,javax.servlet.*,javax.servlet.http.*" %>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
<meta http-equiv="Content-Language" content="el">
<title>Books Selled by Category</title>
</head>

<body>

<p>
ISBN---------Τίτλος---------Τιμή Μονάδας---------Ποσότητα Πώλησης---------Γενικό Σύνολο
</p>

<%
Connection con;
con = null;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn=DriverManager.getConnection("jdbcdbc:BookstoreDatabase");
}
catch (ClassNotFoundException e) {
}
catch (SQLException e) {
log("sql exception");
}

Statement stmtProduct = con.createStatement();
ResultSet rsProduct = stmtProduct.executeQuery("Select * From ProductsInWarehouse;");

while (rsProduct.next())
{
String ISBN = rsProduct.getString("ISBN");
String TQS = rsProduct.getString("QuantitySelled");
int Quantity_Selled = Integer.parseInt(TQS);

Statement stmtPrice = con.createStatement();
ResultSet rsPrice = stmtPrice.executeQuery("select Title,Price from Products where ISBN ='" + ISBN + "';");

while(rsPrice.next())
{
String TP = rsPrice.getString("Price");
float Price = Float.parseFloat(TP);
String Title = rsPrice.getString("Title");
%>
<%=ISBN%>-------<%=Title%>-------<%=Price%>--------<%=Quantity_Selled%>-------<%=Price * Quantity_Selled%>
<%
}
}
rsProduct.close();
stmtProduct.close();
con.close();
%>
</body>
</html>

this code just lets the administrator view the books selled.Quantity and prices.
Thnx again in advance..
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30116
    
150

Aris,
Do you know what line the NullPointer is being thrown on?

I suspect there is a problem obtaining the connection. The code currently logs the exception and then continues with a null connection. You could test this theory, but putting everything in the try/catch block or by looking at the log.

Two other comments:
1) It is good practice to close the connection in a finally block. That way the connection is closed even if an exception is thrown. This allows the driver/database to better recover resources.
2) In a production application, it is good practice to keep code out of the JSP. Especially JDBC code. This makes the code easier to maintain.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Chetan Raju
Ranch Hand

Joined: Aug 02, 2006
Posts: 109


When you see the bold highlighted text in the above code, you can trace out whats wrong. you are calling a method on a reference variable which is null (con).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL error
 
Similar Threads
jsp shopping cart problem
displaying sql query results
What is wrong?
getObject () not returning values
JSP-DataBase problem