Solved

select statements in the methods

Posted on 2004-09-21
10
200 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 35

Expert Comment

by:TimYates
Comment Utility
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
Comment Utility
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
Comment Utility
That 2nd one closes the result set in the finally block too....just to be sure ;-)

What errors do you get?
0
 
LVL 32

Assisted Solution

by:ldbkutty
ldbkutty earned 60 total points
Comment Utility
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
Comment Utility
Well spotted! :-D
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dkim18
Comment Utility
Still nothing working...
no errors or exceptions...
0
 
LVL 35

Expert Comment

by:TimYates
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
password protect pdf 11 72
error when compiling my project in eclipse luna 30 305
iterator example remove 8 74
doubleChar java challenge 7 96
Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
In this step by step tutorial with screenshots, we will show you HOW TO: Enable SSH Remote Access on a VMware vSphere Hypervisor 6.5 (ESXi 6.5). This is important if you need to enable SSH remote access for additional troubleshooting of the ESXi hos…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

6 Experts available now in Live!

Get 1:1 Help Now