Solved

select statements in the methods

Posted on 2004-09-21
10
212 Views
Last Modified: 2010-04-01
Anybody see anything wrong for the following two method(select statements)? It doesn’t seem to work…
+++++++++++++++
      public void getStairManagement(String StairManagementId, StairManagement sm){
            //String sql="SELECT stair_management_id" +
            String sql="SELECT stair_management_id"  +
                    ",visit_id" +
                        ",total_steps" +
                        ",flights" +
                        ",steps_per_flight" +
                        ",assistance" +
                        ",gait_pattern" +
                        ",handrail" +      
                        ",comments" +
                        " FROM stair_management" +
                        " WHERE stair_management_id =" + StairManagementId;
            ResultSet rs = null;
            Statement stmt = null;
            try {
                  stmt=conn.createStatement();
                  rs=stmt.executeQuery(sql);
                  if(rs.next()){
                        sm.setStairManagementId("stair_management_id");
                        sm.setVisitId(rs.getString("visit_id"));
                        sm.setStepsTotal(rs.getString("total_steps"));
                        sm.setFlights(rs.getString("flights"));
                        sm.setStepsPerFlight(rs.getString("steps_per_flight"));
                        sm.setAssistance(rs.getString("assistance"));
                        sm.setGaitPattern(rs.getString("gait_pattern"));
                        sm.setHandrail(rs.getString("handrail"));
                        sm.setComments(rs.getString("comments"));
                  }
                  rs.close();
            } catch (SQLException e) {
                  log.error(sql + "\n"+e);
            }finally{
                  if(stmt!=null){
                        try {
                              stmt.close();
                        } catch (SQLException e1) {
                              log.error(e1);
                        }
                  }
            }//end try/catch
            System.out.println("rs : "+ rs);
      }//end get
      
      public StairManagement[] getStairManagements(String visitId){
            String sql="SELECT stair_management_id" +
                        ",total_steps" +
                        ",flights" +
                        ",steps_per_flight" +
                        ",assistance" +
                        ",gait_pattern" +
                        ",handrail" +
                        ",comments" +
                        " FROM stair_management" +
                        " WHERE visit_id =" + visitId;
            ResultSet rs = null;
            Statement stmt = null;
            StairManagement[] sms=null;
            try {
                  stmt=conn.createStatement();
                  rs=stmt.executeQuery(sql);
                  if(!rs.isBeforeFirst()){
                        stmt.close();
                        return null;
                  }
                  ArrayList al =new ArrayList();
                  while(rs.next()){
                        StairManagement sm = new StairManagement();
                        sm.setStairManagementId(rs.getString("stair_management_id"));
                        sm.setStepsTotal(rs.getString("total_steps"));
                        sm.setFlights(rs.getString("flights"));
                        sm.setStepsPerFlight(rs.getString("steps_per_flight"));
                        sm.setAssistance(rs.getString("assistance"));                  
                        sm.setGaitPattern(rs.getString("gait_pattern"));
                        sm.setHandrail(rs.getString("handrail"));
                        sm.setComments(rs.getString("comments"));
                        al.add(sm);
                  }//end loop
                  rs.close();
                  sms=(StairManagement[])al.toArray(new StairManagement[al.size()]);
            } catch (SQLException e) {
                  log.error(sql + "\n"+e);
            }finally{
                  if(stmt!=null){
                        try {
                              stmt.close();
                        } catch (SQLException e1) {
                              log.error(e1);
                        }
                  }
            }//end try/catch
            return sms;
      }//end get      
0
Comment
Question by:dkim18
[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
  • 2
  • 2
  • +1
10 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 12113568
I would try:

---------------------------

     public void getStairManagement(String StairManagementId, StairManagement sm){
          //String sql="SELECT stair_management_id" +
          String sql="SELECT stair_management_id"  +
                    ",visit_id" +
                    ",total_steps" +
                    ",flights" +
                    ",steps_per_flight" +
                    ",assistance" +
                    ",gait_pattern" +
                    ",handrail" +    
                    ",comments" +
                    " FROM stair_management" +
                    " WHERE stair_management_id = ?";
          ResultSet rs = null;
          PreparedStatement stmt = null;
          try {
               stmt=conn.prepareStatement( sql );
               stmt.setString( 1, StairManagementId ) ;
               rs=stmt.executeQuery();
               if(rs.next()){
                    sm.setStairManagementId("stair_management_id");
                    sm.setVisitId(rs.getString("visit_id"));
                    sm.setStepsTotal(rs.getString("total_steps"));
                    sm.setFlights(rs.getString("flights"));
                    sm.setStepsPerFlight(rs.getString("steps_per_flight"));
                    sm.setAssistance(rs.getString("assistance"));
                    sm.setGaitPattern(rs.getString("gait_pattern"));
                    sm.setHandrail(rs.getString("handrail"));
                    sm.setComments(rs.getString("comments"));
               }
               rs.close();
          } catch (SQLException e) {
               log.error(sql + "\n"+e);
          }finally{
               if(stmt!=null){
                    try {
                         stmt.close();
                    } catch (SQLException e1) {
                         log.error(e1);
                    }
               }
          }//end try/catch
          System.out.println("rs : "+ rs);
     }//end get
     
     public StairManagement[] getStairManagements(String visitId){
          String sql="SELECT stair_management_id" +
                    ",total_steps" +
                    ",flights" +
                    ",steps_per_flight" +
                    ",assistance" +
                    ",gait_pattern" +
                    ",handrail" +
                    ",comments" +
                    " FROM stair_management" +
                    " WHERE visit_id = ?" ;
          ResultSet rs = null;
          PreparedStatement stmt = null;
          StairManagement[] sms=null;
          try {
               stmt=conn.prepareStatement( sql );
               stmt.setString( 1, visitId ) ;
               rs=stmt.executeQuery();
               ArrayList al =new ArrayList();
               while(rs.next()){
                    StairManagement sm = new StairManagement();
                    sm.setStairManagementId(rs.getString("stair_management_id"));
                    sm.setStepsTotal(rs.getString("total_steps"));
                    sm.setFlights(rs.getString("flights"));
                    sm.setStepsPerFlight(rs.getString("steps_per_flight"));
                    sm.setAssistance(rs.getString("assistance"));              
                    sm.setGaitPattern(rs.getString("gait_pattern"));
                    sm.setHandrail(rs.getString("handrail"));
                    sm.setComments(rs.getString("comments"));
                    al.add(sm);
               }//end loop
               rs.close();
               sms=(StairManagement[])al.toArray(new StairManagement[al.size()]);
          } catch (SQLException e) {
               log.error(sql + "\n"+e);
          }finally{
               if(stmt!=null){
                    try {
                         stmt.close();
                    } catch (SQLException e1) {
                         log.error(e1);
                    }
               }
          }//end try/catch
          return sms;
     }//end get    
0
 
LVL 35

Accepted Solution

by:
TimYates earned 120 total points
ID: 12113584
ACTUALLY:

     public void getStairManagement(String StairManagementId, StairManagement sm){
          //String sql="SELECT stair_management_id" +
          String sql="SELECT stair_management_id"  +
                    ",visit_id" +
                    ",total_steps" +
                    ",flights" +
                    ",steps_per_flight" +
                    ",assistance" +
                    ",gait_pattern" +
                    ",handrail" +    
                    ",comments" +
                    " FROM stair_management" +
                    " WHERE stair_management_id = ?";
          ResultSet rs = null;
          PreparedStatement stmt = null;
          try {
               stmt=conn.prepareStatement( sql );
               stmt.setString( 1, StairManagementId ) ;
               rs=stmt.executeQuery();
               if(rs.next()){
                    sm.setStairManagementId("stair_management_id");
                    sm.setVisitId(rs.getString("visit_id"));
                    sm.setStepsTotal(rs.getString("total_steps"));
                    sm.setFlights(rs.getString("flights"));
                    sm.setStepsPerFlight(rs.getString("steps_per_flight"));
                    sm.setAssistance(rs.getString("assistance"));
                    sm.setGaitPattern(rs.getString("gait_pattern"));
                    sm.setHandrail(rs.getString("handrail"));
                    sm.setComments(rs.getString("comments"));
               }
          } catch (SQLException e) {
               log.error(sql + "\n"+e);
          }finally{
               if(rs!=null){
                    try {
                         rs.close();
                    } catch (SQLException e1) {
                         log.error(e1);
                    }
               }
               if(stmt!=null){
                    try {
                         stmt.close();
                    } catch (SQLException e1) {
                         log.error(e1);
                    }
               }
          }//end try/catch
          System.out.println("rs : "+ rs);
     }//end get
     
     public StairManagement[] getStairManagements(String visitId){
          String sql="SELECT stair_management_id" +
                    ",total_steps" +
                    ",flights" +
                    ",steps_per_flight" +
                    ",assistance" +
                    ",gait_pattern" +
                    ",handrail" +
                    ",comments" +
                    " FROM stair_management" +
                    " WHERE visit_id = ?" ;
          ResultSet rs = null;
          PreparedStatement stmt = null;
          StairManagement[] sms=null;
          try {
               stmt=conn.prepareStatement( sql );
               stmt.setString( 1, visitId ) ;
               rs=stmt.executeQuery();
               ArrayList al =new ArrayList();
               while(rs.next()){
                    StairManagement sm = new StairManagement();
                    sm.setStairManagementId(rs.getString("stair_management_id"));
                    sm.setStepsTotal(rs.getString("total_steps"));
                    sm.setFlights(rs.getString("flights"));
                    sm.setStepsPerFlight(rs.getString("steps_per_flight"));
                    sm.setAssistance(rs.getString("assistance"));              
                    sm.setGaitPattern(rs.getString("gait_pattern"));
                    sm.setHandrail(rs.getString("handrail"));
                    sm.setComments(rs.getString("comments"));
                    al.add(sm);
               }//end loop
               sms=(StairManagement[])al.toArray(new StairManagement[al.size()]);
          } catch (SQLException e) {
               log.error(sql + "\n"+e);
          }finally{
               if(rs!=null){
                    try {
                         rs.close();
                    } catch (SQLException e1) {
                         log.error(e1);
                    }
               }
               if(stmt!=null){
                    try {
                         stmt.close();
                    } catch (SQLException e1) {
                         log.error(e1);
                    }
               }
          }//end try/catch
          return sms;
     }//end get    
0
 
LVL 35

Expert Comment

by:TimYates
ID: 12113587
That 2nd one closes the result set in the finally block too....just to be sure ;-)

What errors do you get?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Assisted Solution

by:ldbkutty
ldbkutty earned 60 total points
ID: 12113621
Do you get any errors/exceptions ?

>> sm.setStairManagementId("stair_management_id");
should be:
sm.setStairManagementId(rs.getString("stair_management_id"));
0
 
LVL 35

Expert Comment

by:TimYates
ID: 12113641
Well spotted! :-D
0
 

Author Comment

by:dkim18
ID: 12114350
Still nothing working...
no errors or exceptions...
0
 
LVL 35

Expert Comment

by:TimYates
ID: 12114395
Is the code running?

Try putting System.out.println( "XX" ) ; messages at relevant points

Does the SQL give you results if you type it straight into the db tool for your database?
0
 
LVL 21

Assisted Solution

by:MogalManic
MogalManic earned 20 total points
ID: 12118643
Try the SQL statements interactivly using SQL tool such as Squirrel(http://www.squirrelsql.org) or DBVisualizer(http://www.minq.se/products/dbvis/).

Make sure the keys(StairManagementId) you are passing in results in 1 or more rows.
0
 

Author Comment

by:dkim18
ID: 12122552
TimYates,

I fixed the bug. It was from other files...
Since you all put some effort, I will give 80% of point for  you if you can explain the change you made in those methods and what are the differences.

I will give give rest of points to others.

thanks,
0
 
LVL 32

Assisted Solution

by:ldbkutty
ldbkutty earned 60 total points
ID: 12125160
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Handle form fields in spring mvc controller 1 72
excpetion with multiple catch 11 141
how to add new optional parameter to JSP 1 61
ArrayList Adding/settign data 3 38
We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

751 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