db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: unable to execute procedure
Date Tue, 04 Apr 2006 17:12:43 GMT
Hi Dan,

> 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.

I think this is a very important point, and if you look at the  
procedure, you might think that there is no value in using prepared  
statements, as the statement appears as if it is only going to be  
executed once. But preparing the statement allows Derby to cache the  
statement the first time, and to find an identical statement (with  
parameter markers) in the cache on subsequent invocations, even if  
you close the Connection. If you bind the values into the prepared  
statement, there is little chance that Derby will find the statement  
in the cache.

Craig

On Apr 4, 2006, at 9:48 AM, Daniel John Debrunner wrote:

> 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.
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message