I have small problem and I'm looking for some guidence. I have a query that returning only one row, how ever when I use while(rs.next()), it's not falling in the loop. Any query that I run that returns more than one row works fine..any ideas?
Here is a sample of the code I'm using..again it works fine when the resultset has more than one row, but if it has one row, it doesn't make it into the while loop...thanks in advance for your assistance
Do "results" and "rs" refer to the same ResultSet object? If so, then I'd say that's your problem. ResultSet.next() not only tells you if there is another record, but also physically moves the "cursor" to the next row. So, if the the ResultSet contained only one row, then when your code reaches your while loop, rs.next() would return false.
If that doesn't help, then post some more code. I appreciate the brevity of your code sample, but I think it's too brief - I can't tell exactly what you're doing. Maybe if you add class names and method names so I can see how the 2 code fragments are tied together...
I'm passing there resultsset object into the todocument(results,out) method and referring to it in the todocument method as "rs"...here are the classes and methods....(I took out the formatting to try to make it more readable).
Just to give you a little more detail....I'm running a query based on an ID..passing the results to the todocument method to create a XML representation of the resultset....which is stored in the variable "doc" and returned to the calling program...
public class printreport extends HttpServlet { private DataSource dataSource;
public void init(ServletConfig config) throws ServletException{ try{ Context init = new InitialContext(); dataSource = (DataSource) init.lookup("java:comp/env/jdbc/lawcom");
}catch (NamingException ex){ throw new ServletException("Cannot retrieve java:comp/env/jdbc/conversion",ex); }
}
public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/html"); PrintWriter out = res.getWriter(); Connection con = null; Document doc = null; xmlutils prt = new xmlutils(); out.println("<html><head><Title>Print report</title></head><body>");
try{ synchronized(dataSource){ con = dataSource.getConnection(); } PreparedStatement pstmt=con.prepareStatement("Select IRLocation, IRFromDate, IRToDate, IRToTime,IRWeaponDesc, IRStranger from IRLocationData where ID<9");
Ok, then my original suspicion is correct. The code: is actually moving the cursor to the first row. Then this code in your toDocument() method is trying to read the next row (i.e. the second row) which doesn't exist if your query returned only one row and therefore returns false and doesn't fall into your loop.
If you run the query so that more rows are returned I believe that you will find that the first row of your query is always missing because it does not get processed within the while (rs.next()) loop.
You need to consider an alternative strategy to generating an error if there are no rows. The way you currently do it is inadvertently ignoring the first row of every ResultSet. Is not returning any rows really an exception?
I just updated my code with the suggestions you made and it worked perfectly. Thanks so much for your assistance, and I hope I can be as helpful to you as you were to me sometime in the future.
THANKS again!!!
Post by:autobot
No prison can hold Chairface Chippendale. And on a totally different topic ... my stuff:
a bit of art, as a gift, that will fit in a stocking