Forum:

JDBC and Relational Databases

Object has been closed

Greenhorn

Posts: 8

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I'm new to jsp and need help. I have this code working in php, but cant get it to work in jsp. Please help. This page produces this error... I'm running JRun4 with jre1.4.2. I've tried several dsn connections using macromedia's driver, oracle's thin driver and jdbc to odbc bridge and get the same results every time.
Exception in main try block
Error:java.sql.SQLException: [Macromedia][Oracle JDBC Driver]Object has been closed.

<%@ page language="java" buffer="8kb" autoFlush="true" isThreadSafe="true" isErrorPage="false"  %>
<%@ page import="javax.naming.*,javax.sql.*,java.sql.*,java.util.*" %>
<html>
<head>
<title>Cart Grid for Cart: <%= request.getParameter("pick_id") %></title>
<link rel="stylesheet" href="core/style.css">
<STYLE TYPE="text/css">
#floater {
position: absolute;
left: 5;
top: 50;
width: 50;
visibility: visible;
z-index: 10;
}
</STYLE>
</head>
<body class="desktop">
<DIV ID="floater">
<hr color="white" size="1" width="100%"><center><a href="javascript:window.close();"><font size="-2" color="white">Close<br>this<br>Window</font></a><hr color="white" size="1" width="100%"><a href="javascript:window.print();"><font size="-2" color="white">Click<br>here<br>to<br>send<br>to<br>the<br>Printer</font></a></center><hr color="white" size="1" width="100%">
</div>
<table border="0" cellpadding="1" cellspacing="1">
<tr>
<td valign="top" width="5%">
<hr size="0" width="50">
</td>
<td valign="top" width="95%">
<table width="100%" border="0" cellpadding="1" cellspacing="1">
<tr><td><font color="white"><b> Cart Grid for Cart:</b> <%= request.getParameter("pick_id") %> </font></td></tr>
</table>
<% 
String query = "SELECT DISTINCT ct.CART_LEVEL FROM THOMPSON.CART_LOCATION cl, THOMPSON.CART_TYPE ct, THOMPSON.PICK_HEADER ph WHERE ct.CART_TYPE = cl.CART_TYPE AND cl.LOCATION_NO = ph.LOCATION_NO AND ((ph.PICK_ID="+ request.getParameter("pick_id") +"))";
String query1 = "SELECT DISTINCT ct.cart_slot FROM cart_location cl, cart_type ct, pick_header ph WHERE (ph.PICK_ID="+ request.getParameter("pick_id") +") AND (cl.LOCATION_NO=ph.location_no) AND (cl.CART_TYPE=ct.cart_type) ORDER BY ct.CART_SLOT";
String dsName = "main";
Connection dbConnection = null;
Statement dbStatement = null;
ResultSetMetaData rsMetaData = null;
ResultSet dbResultSet = null;
ResultSet dbResultSet1 = null;
ResultSet dbResultSet3 = null;
ResultSet dbResultSet4 = null;
String sqlStatement = null;
String sqlStatement1 = null;
String sqlStatement3 = null;
String sqlStatement4 = null;

out.println("<table border=\"1\" cellspacing=\"1\" cellpadding=\"0\">");
out.println("<tr class=\"form\"><th> </th>");
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(dsName);
dbConnection = ds.getConnection();
dbStatement= dbConnection.createStatement();

sqlStatement1 = query1;
dbResultSet1 = dbStatement.executeQuery(query1);

while (dbResultSet1.next()) {
String slot = dbResultSet1.getString("cart_slot");
out.println("<th align=\"center\"><b>" + dbResultSet1.getString("cart_slot") + "</b></th>");
}
out.println("</tr>");

sqlStatement = query;
dbResultSet= dbStatement.executeQuery(query);
while (dbResultSet.next()) {
out.println("<tr class=form>");
String level = dbResultSet.getString("CART_LEVEL");
out.println("<th ><b>" + level + "</b></th>");

dbResultSet1 = dbStatement.executeQuery(query1);
while (dbResultSet1.next()){
String slot = dbResultSet1.getString("cart_slot");

String query3 = "SELECT distinct pd.ORDER_ID FROM THOMPSON.PICK_DETAIL pd WHERE (pd.PICK_ID="+ request.getParameter("pick_id") +") AND (pd.CART_LEVEL='"+ level +"') AND (pd.CART_SLOT='"+ slot +"')";
sqlStatement3 = query3;
dbResultSet3 = dbStatement.executeQuery(query3);
while(dbResultSet3.next()){
//out.println("Order ID: " + dbResultSet3.getString("ORDER_ID"));
if (dbResultSet3.getString("ORDER_ID") != null){
out.println("<td bgcolor=\"99CCFF\" class=\"objectlist\">");
out.println("<table><tr><td colspan=\"3\"><b>Order: </b>" + dbResultSet3.getString("ORDER_ID") + "</td><tr>");
out.println("<tr><td>Item</td><td>Alc</td><td>Cart</td></tr>");
       out.println("<tr><td></td><td>Qty</td><td>Qty</td></tr>");

String query4 = "select od.product_id, od.allocated_qty, pd.quantity from order_detail od, pick_detail pd where od.order_id = pd.order_id and od.order_type = pd.order_type and od.order_line_no = pd.order_line_no and pd.pick_id = " + request.getParameter("pick_id") + " and od.order_id  = '" + dbResultSet3.getString("ORDER_ID") + "'";
sqlStatement4 = query4;
dbResultSet4 = dbStatement.executeQuery(query4);
while (dbResultSet4.next()){
out.println("<tr><td>" + dbResultSet4.getString("PRODUCT_ID") + "</td><td>" + dbResultSet4.getString("ALLOCATED_QTY") + "</td><td>" + dbResultSet4.getString("QUANTITY") + "</td></tr>");
}
}
}
out.println("</table>");
out.println("</td>");

}
}
out.println("</table>");
}
catch (Exception e){
out.println("<table width=\"600\" align=\"center\"><tr><td><p>Exception in main try block<td></tr> <br>");
out.println("<tr><td><p>Error:" + e + "<td></tr>");
out.println("<tr><td><p>Statement:" + sqlStatement + "<p>Statement1:" + sqlStatement1 + "<p>Statement3:" + sqlStatement3 + "<p>Statement4:" + sqlStatement4 + "<td></tr></table>");
}
finally{
// clean up the connection information
try {
if (dbResultSet != null)
dbResultSet.close();

if (dbStatement != null)
dbStatement.close();

if (dbConnection != null)
dbConnection.close();
} catch (Exception e) { out.println("Problem closing database connection: " + e); }
}
 %>
 </td>
</tr>
</table>
<%@ include file="JavaScript/stalker.js"%>
 </body></html>

[ March 18, 2004: Message edited by: Bear Bibeault ]

Bear Bibeault

Sheriff

Posts: 67752

173

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Despite the fact that you are doing all this processing in a JSP (which I would highly recommend against), this is really a database question. So I'm moving this along to the JDBC forum for you.

[Asking smart questions] [About Bear] [Books by Bear]

HTML 5 (verified skill)
Skill verified by Jeanne Boyarsky

CSS 3 (verified skill)
Skill verified by Jeanne Boyarsky

JavaScript (verified skill)
Skill verified by Jeanne Boyarsky

Jeremy Taylor

Greenhorn

Posts: 8

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Like I said, I'm new to this. I haven't grasped the concept of javabeans yet. What do you recommend I do? What am I looking at? 3 servlets, 3 javabeans, and a jsp page that calls all 3 javabeans and loops over the results? Can you point me to some docs on javabeans?

Bear Bibeault

Sheriff

Posts: 67752

173

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Here in this topic, let's keep to the JDBC problem you are having. With regards to web app architecture, please look through the JSP and Servlets forums for tips and best-practice patterns (hint: search for terms MVC and 'Model 2'). Any questions you may have on web app structure will be gladly handled there.
Another hint: use the UBB code tags around your code fragments (I added them to your original post) to preserve the code formatting. Most people will not take the time to try and decipher unformatted code.
Is there any chance you could post the entire stack trace displaying the exact line number where the problem is occurring?

[Asking smart questions] [About Bear] [Books by Bear]

Gowrishankar Mudaliar

Ranch Hand

Posts: 39

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

You are using the dbstatement object for all the resultsets. try using different statement for each resultset.
Gowrishankar

These are the worst of times and these are the best of times. And this is the best tiny ad:

Gift giving made easy with the permaculture playing cards

https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing