Forums Register Login

Java Result Set

+Pie Number of slices to send: Send
Hi all,

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?
+Pie Number of slices to send: Send
Please post a small sample of the code you're having a problem with and we'll see if we can help.
+Pie Number of slices to send: Send
Thanks for the reply.

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

calling file...

......
ResultSet results = pstmt.executeQuery();
if(!results.next()){
throw new SQLException("Missing Row");}
doc = prt.toDocument(results, out);
out.println(prt.serialize(doc));



receiving file...

Element results =doc.createElement("Results");
doc.appendChild(results);

ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
while(rs.next()){
.......
}
+Pie Number of slices to send: Send
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...
+Pie Number of slices to send: Send
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");

ResultSet results = pstmt.executeQuery();
ResultSet results = stmt.executeQuery(getLocation);
if(!results.next()){
throw new SQLException("Missing Row");}
doc = prt.toDocument(results, out);
out.println(prt.serialize(doc));

....
}





public class xmlutils
{

public static Document toDocument(ResultSet rs, PrintWriter out) throws ParserConfigurationException, SQLException
{
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document doc = builder.newDocument();

Element results =doc.createElement("Results");
doc.appendChild(results);

ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();

out.println(colCount);
while(rs.next())
{out.println("one record");
Element row = doc.createElement("Row");
results.appendChild(row);

for (int i=0; i <= colCount; i++)
{out.println("two record");
String columnName = rsmd.getColumnName(i);
Object value = rs.getObject(i);

Element node = doc.createElement(columnName);
node.appendChild(doc.createTextNode(value.toString()));
row.appendChild(node);

}

}

return doc;

.....
}
+Pie Number of slices to send: Send
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?

Let me know if you need further clarification.
+Pie Number of slices to send: Send
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!!!
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
https://gardener-gift.com


reply
reply
This thread has been viewed 2042 times.
Similar Threads
highlight back ground of a row in datatable
how to find all tablename and rowcount of each table from database?
Putting Database values in JavaBeans
Hibernate Delete
Syntax error
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 17:03:53.