Retrieving data from database

[Shahid Kahn]

package backoffice.order;

import backoffice.order.*;
import gbazaar.dbtool.*;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Products{
	private String orderId;
	private ArrayList<OrderProductBean> products;

	public Products(){
		orderId = null;
		products = null;
	}
	
	public void setOrderId(String orderId){
		this.orderId = orderId;
	}

	public ArrayList<OrderProductBean> getProducts(){
		this.setProducts();
		return products;
	}

	public void setProducts(){
		DBConnectionBean cb = new DBConnectionBean();
		ResultSet resultSet = null, rs = null;
		PreparedStatement stmt = null, stmt2 = null;
		Connection conn = null;
		OrderProductBean product = null;

		products = new ArrayList<OrderProductBean>();
		
		try{					
			conn = (Connection)cb.getPooledConnection();
			stmt = conn.prepareStatement("SELECT * FROM ORDR_PRODUCT WHERE ordr_id = ?");
			stmt.setString(1, orderId);
			resultSet = stmt.executeQuery();
			
			while(resultSet.next()){
				product = new OrderProductBean();				
				product.setProductId(resultSet.getString("prod_id"));
				product.setProductQuantity(resultSet.getString("qty"));
				product.setProductUnitMrp(resultSet.getString("mrp"));
				product.setProductDiscount(resultSet.getString("discount"));

				stmt2 = conn.prepareStatement("SELECT prod_name FROM product WHERE prod_id = ?");
				stmt2.setString(1, resultSet.getString("prod_id"));
				rs = stmt2.executeQuery();
				if(rs.next()){
					product.setProductName(rs.getString("prod_name"));
				}
				System.out.println(""+product.getProductId());					
				this.products.add(product);
			}
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		finally{
			try{
				conn.close();
				stmt.close();
				rs.close();
			}
			catch(Exception e){}
		}		
	}
}

At Line 41, no data is retrieved. But there is no compile time error, no runtime error.
If I try to query data from other table it works fine. It does not work for ordr_product table.
I ran the query on sqlplus, it worked fine.
Can you tell me where I am going wrong.

my JSP is

			<jsp:useBean id="orderProductsObject" class="backoffice.order.Products">
				<jsp:setProperty name="orderProductsObject" property="orderId" value="${param.orderId}"/>
			</jsp:useBean>
				
			<c:set var="prods" value="${orderProductsObject.products}" />

					<c:if test="${not empty prods}">
						<table>
							<tr>
								<th>Prod ID</th>
								<th>Prod Name</th>
								<th>Quantity</th>
								<th>Unit Price</th>
								<th>Total</th>
							</tr>
							
							<c:forEach var="prod" items="${prods}" varStatus="loop">
								<tr>
									<td><label style="color: red;">${prod.productId}</label></td>
									
								</tr>							
									
							</c:forEach>					
						</table>
					</c:if>

[Jeanne Boyarsky]

Can you confirm the data is committed? (Type commit; in SQL Plus) That would explain the query working in one and not the other.

Also, can you try changing your JDBC code to run the query SELECT * FROM ORDR_PRODUCT. This will tell if the problem is in the where clause. Maybe it is lowercase vs uppercase or there is trailing whitespace?

[Shahid Kahn]

Yes, the issue was with the WHERE clause.
The database field was ORDR_ID CHAR(12), but the value I was matching against was of 11 bytes.
I changed the database column and it is working now.
But why is it like this? My string to be match was of smaller length than that of database.

[Jeanne Boyarsky]

Shadi,
Yes, that's the difference between char and varchar. char stores exactly X characters. varchar stores a smaller amount if present. Varchar also has the advantage of using less disk space since it only stores what is needed.

[Javier Arroyo-Figueroa]

For cases like this you may want to do an isolated test of your JSP using a tool like JspTester. This way you make sure that nothing is wrong with your JSP, so you can isolate the problem better.

Link: http://www.jsptester.com. Hope this helps.

[Brian Tkatch]

On a side note, please do not use "SELECT *" outside of EXISTS() and ad hoc queries. Specifying the column name(s) is self documenting, and protects against new and removed columns, and possible reordering. It also makes sure you only get the data you need, adding a bit of network and processing efficiency.