• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Java Result Set

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please post a small sample of the code you're having a problem with and we'll see if we can help.
 
jr williams
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 331
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 331
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!!!
 
Everyone is a villain in someone else's story. Especially this devious tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic