db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: unable to execute procedure
Date Tue, 04 Apr 2006 16:48:45 GMT
Anil Samuel wrote:


> public class LeaveHelper {
> 
>     public static int TotalLeaveForType(String employee, int leaveTypeId)
>     {
>         int total = 0;
>         try {
>             Connection con = DriverManager.getConnection(
> "jdbc:default:connection");
>             PreparedStatement ps = con.prepareStatement("select TOTAL_TAKEN
> from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and LEAVE_TYPE_ID="
> + leaveTypeId);
>             ResultSet rs = ps.executeQuery();
>             ps.close();
>             con.close();
>             total = rs.getInt(1);
>         }
>         catch (SQLException e)
>         {
>             e.printStackTrace();
>         }
> 
>         return total;
>     }
> }

Couple of problems with the code:

 - You close the PreparedStatement ps before using the ResultSet rs. By
JDBC rules the close of ps will also close rs.

 - You don't call rs.next() on the ResultSet, thus the rs.getInt will fail.

May I suggest that if you are writing samples for others to use that you
demonstrate use of parameter markers in PreparedStatements. This will
perfom better on Derby and all other relational database engines.

Here's a reworked version

>     public static int TotalLeaveForType(String employee, int
leaveTypeId) throws SQLException
>     {
>         int total = 0;

>             Connection con = DriverManager.getConnection(
> "jdbc:default:connection");
>             PreparedStatement ps = con.prepareStatement("select
TOTAL_TAKEN
> from DTP.LEAVE_APPROV where EMPLOYEE_ID=? and LEAVE_TYPE_ID=?");

              ps.setString(1, employee);
              ps.setInt(2, leaveTypeId);
>             ResultSet rs = ps.executeQuery();
              rs.next();

>             total = rs.getInt(1);
              rs.close();
              ps.close();
>             con.close();

>
>         return total;
>     }
> }


Dan.


Mime
View raw message