Calling Firebird Stored procedure from Java Servlet

Question

I have a Firebird Database with some stored procedure, and I am developing a Java web/database application. I want to know if it is possible and how to call those stored procedures from a java Class or a Servlet.

example:

1. Stored procedure deletep, used to delete a row from database
2. my webapp Jsp/servlets
3. I want to create a link that execute my delete stored procedure

Answer 1

To execute a Firebird stored procedure in Java, there are several ways. The JDBC-default method would be something like:

try (CallableStatement cstmt = connection.prepareStatement(
        "{call yourProcedure(?, ?, ?)}")) {
    cstmt.setString(1, "value1");
    cstmt.setString(2, "value2");
    cstmt.setString(3, "value3");
    cstmt.execute();
}

If the Firebird stored procedure was selectable (that is: contains SUSPEND and can return multiple rows), you would need to do something like:

try (CallableStatement cstmt = connection.prepareStatement(
        "{call yourProcedure(?, ?, ?)}")) {
    cstmt.setString(1, "value1");
    cstmt.setString(2, "value2");
    cstmt.setString(3, "value3");
    try (ResultSet rs = cstmt.executeQuery()) {
        while(rs.next()) {
            // do something with result set rows ...
        }
    }
}

This is sufficient if the stored procedure is executable and doesn't return any values. If the stored procedure is executable with (multiple) return columns, you'd need to something like:

try (CallableStatement cstmt = connection.prepareStatement(
        "{call yourProcedure(?, ?, ?, ?, ?)}")) {
    cstmt.setString(1, "value1");
    cstmt.setString(2, "value2");
    cstmt.setString(3, "value3");
    cstmt.registerOutParameter(4, Types.VARCHAR);
    cstmt.registerOutParameter(5, Types.VARCHAR)
    cstmt.execute();

    String out1 = cstmt.getString(4);
    String out2 = cstmt.getString(5);
    // do something with result
}

Alternatively, you could use the Firebird specific syntax EXECUTE PROCEDURE yourProcedure(?, ?, ?) for executable stored procedures and SELECT * FROM yourProcedure(?, ?, ?).