Re: unable to execute procedure

[Craig L Russell]

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.

Craig

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

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.

Anil Samuel wrote:

public class LeaveHelper {


    {
        int total = 0;
        try {
            Connection con = DriverManager.getConnection(
"jdbc:default:connection");

+ leaveTypeId);
            ResultSet rs = ps.executeQuery();
            ps.close();
            con.close();
            total = rs.getInt(1);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }

        return total;
    }
}

public static int TotalLeaveForType(String employee, int leaveTypeId) PreparedStatement ps = con.prepareStatement("select TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and LEAVE_TYPE_ID="

Couple of problems with the code:


JDBC rules the close of ps will also close rs.




demonstrate use of parameter markers in PreparedStatements. This will
perfom better on Derby and all other relational database engines.

Here's a reworked version

- You close the PreparedStatement ps before using the ResultSet rs. By - 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

    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:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature