wood burning stoves*
The moose likes JDBC and the fly likes Java Result Set Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java Result Set" Watch "Java Result Set" New topic
Author

Java Result Set

jr williams
Greenhorn

Joined: Jul 05, 2004
Posts: 4
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?
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
Please post a small sample of the code you're having a problem with and we'll see if we can help.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
jr williams
Greenhorn

Joined: Jul 05, 2004
Posts: 4
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()){
.......
}
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
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...
jr williams
Greenhorn

Joined: Jul 05, 2004
Posts: 4
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;

.....
}
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
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.
jr williams
Greenhorn

Joined: Jul 05, 2004
Posts: 4
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!!!
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Java Result Set