Solved

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

Posted on 2004-10-19
11
222 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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 8

Accepted Solution

by:
kiranhk earned 500 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
where is session ID cache stored 1 47
groovy example issue 10 90
What is the latest versions eclipse neon 2 140
Java / Linux and Regular Expressions 11 71
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
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…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now