Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query only returning rows after first row. If only one row then nothing returns.

Posted on 2004-10-19
11
Medium Priority
?
228 Views
Last Modified: 2010-03-31
The code below should query the database and returns all records where SSN = the passed SSN. The problem is that it is only returning the rows after the first row found. If there is only one record in the database then nothing returns, if there are three records matching the SSN then only the last two are returned, etc, etc. If I remove the WHERE clause then all data is returned. What am I doing wrong here? SSN is stored as a varchar in a SQL 2K database.

RCMB

public class RiderReport extends HttpServlet {
      

      public String removeNull(Object value) {
            String ret = null;
                  ret = (value == null)? " " : value.toString();
            return ret;
      }

      public void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
              doGet(request, response);
      }

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
            String SSN = request.getParameter("SSN");
            String sql = "Select Rank_Rate AS [Rate], LName AS [Last Name], FName AS [First Name], arrive_time AS [Arrival], depart_time AS [Departure] FROM pers_riders WHERE SSN = '" + SSN + "'";
            Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
       
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

                  out.println("<html><head><title>Personnel Database Report</title></head><body>");
                  out.println("<center>");
                  out.println("<script src=../scripts/xaramenu.js></script>");
                  out.println("<script menumaker src=../scripts/clearlake7.js></script>");
                  out.println("</center>");
                  out.println("<p><center><h2>RIDER HISTORY REPORT</h2></center></p>");
                  out.println("<table align=center border=1 width=100%><tr>");
       
        try{
           
                  conn = ConnectionManager.getConnection();
               
               //Create a statement for executing some SQL

               stmt = conn.createStatement();

            rs = stmt.executeQuery(sql);
                  
            rsm = rs.getMetaData();
                  
                  int colCount =  rsm.getColumnCount();
           
                  //print column names
                  for (int i = 1; i <=colCount; ++i){
               
                        out.println("<th width=20%>" + rsm.getColumnName(i) + "</th>");
                  }
           
                  out.println("</tr>");
         
                  while( rs.next()){
               
                         out.println("<tr>");
               
                        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                        SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                        sdf1.setLenient(false);
                        while (rs.next()) {
                               for (int i = 1;  i <=colCount; ++i) {
                                      String value = removeNull(rs.getString(i));
                                      try {
                                             Date d = sdf1.parse(value);
                                             value = sdf2.format(d);
                                      }
                                      catch(Exception e) {
                                             /* ignore - if its not a Date */
                                      }
                                      out.println("<td>" + value + "</td>");
                               }
                              out.println("</tr>");
                        }
                  }
                        
            } catch (Exception e){
           
                  throw new ServletException(e.getMessage());
                  
            } finally {
           
                  try{
               
                        rs.close();
                        conn.close();
                        
                  } catch (SQLException sqle){ }
           
           
            }
     
            out.println("</table><br><br>");

            out.println("</body>");
            out.println("</html>");
   
            out.close();

       } //doGet

}
0
Comment
Question by:rcmb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 8

Accepted Solution

by:
kiranhk earned 2000 total points
ID: 12349753
the problem is that u r calling rs.next() 2 times so it is moving to the second row fetched.
you can probably take out the first rs.next()

               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {


hope this helps
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12349757
you have 2 rs.next() calls, one outside the SimpleDateFormat setup, and then another inside - the first call is only being used to check if processing should be done, but it is consuming a row...

restructure the code so that there is only one call to ResultSet.next() on each loop iteration...
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12349771
 while( rs.next()){
               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {

can probably become:

                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {
                         out.println("<tr>");
                
0
The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349781
This is a problem

while( rs.next()){
               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349788
cjjclifford ,

you beat me. while reading other threads

you gave the answer
0
 
LVL 8

Expert Comment

by:kiranhk
ID: 12349797
       Just try with this code.
Take out the outer while loop after your print column names code and use the one below

   
                 out.println("<tr>");
           
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                sdf1.setLenient(false);
                while (rs.next())
                {
                      for (int i = 1;  i <=colCount; ++i)
                      {
                            String value = removeNull(rs.getString(i));
                            try
                            {
                                  Date d = sdf1.parse(value);
                                  value = sdf2.format(d);
                            }
                            catch(Exception e)
                            {
                                  /* ignore - if its not a Date */
                            }
                            out.println("<td>" + value + "</td>");
                      }
                     out.println("</tr>");
                     out.println("<tr>");
                }
                out.println("</tr>");
       
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349811
Even kiranhk  also beat me.
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349832

               
                     out.println("<tr>");
               
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                    SimpleDateFormat sdf2 = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a");
                    sdf1.setLenient(false);
                    while (rs.next()) {
                          for (int i = 1;  i <=colCount; ++i) {
                                String value = removeNull(rs.getString(i));
                                try {
                                      Date d = sdf1.parse(value);
                                      value = sdf2.format(d);
                                }
                                catch(Exception e) {
                                      /* ignore - if its not a Date */
                                }
                                out.println("<td>" + value + "</td>");
                          }
                         out.println("</tr>");
                    }
               }
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12349894
It might be a little more portable(*) if you use the MetaData to decide of the column contains a date, and just getTimestamp() for this column, rather than getString, and use a hardcoded date format? There's also slightly less work this way (no conversion from String -> Date in Java...)

(*) when I say portable, I'm guessing that while the (default?) date format is probably pretty standard, its concievable that this might be different on some databases...
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 12349922
While reading the comments,
I think
kiranhk and   cjjclifford  both deserves the points

Regards
Sudhakar
0
 
LVL 12

Author Comment

by:rcmb
ID: 12349928
I must say I really appreciate the quick responses. Cudos to kiranhk for being the first to respond. All answers were basically the same so I awarded to the first responder.

Thanks again,
RCMB
0

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question