Jaybird CallableStatement not executed until getting output parameter

Question

I have this Java code:

Connection conn = connectionProvider.getConnection();
statement = conn.prepareCall("execute procedure rm_set_coordinates(?,?,?)");
statement.setInt(1, userId);
statement.setString(2, String.valueOf(location.getLatitude()));
statement.setString(3, String.valueOf(location.getLongitude()));
statement.execute();

Where rm_set_coordinates is Firebird stored procedure:

create or alter procedure RM_SET_COORDINATES (
    PCAR_LOGIN integer,
    PLAT varchar(20) = 0,
    PLNG varchar(20) = 0)
returns (
    ORESULT integer,
    ORESULT_MSG varchar(500))
as
begin
     update ref_car rc  set rc.rm_last_connect_time='now',rc.rm_lat=:plat,rc.rm_lng=:PLNG  where rc.id=:pcar_login;
     oresult=1;
     oresult_msg='';
  suspend;
end

When I execute this code data in ref_car table is not changing. But if I'll add this line to above code:

statement.getInt(1);

which returns value of oresult output parameter then it's ok, data in ref_car table is updated.

Answer 1

The problem is your use of SUSPEND in the stored procedure. The keyword SUSPEND is intended for selectable stored procedures (stored procedures that produce multiple rows of data). As your stored procedure only produces a single row, the SUSPEND is unnecessary (and the cause).

For a selectable procedure, Jaybird transforms your execute procedure rm_set_coordinates(?,?,?) to SELECT * FROM rm_set_coordinates(?,?,?). I am not 100% sure about the implementation details of Firebird stored procedures, but it looks like a block containing a SUSPEND is either only executed when a row is actually fetched, or all changes upto the previous SUSPEND are reverted when the row is not fetched before the statement is closed or reused.

The end result is however: no rows fetched, no changes. When executing a selectable stored procedure (ie: it contains a SUSPEND), Jaybird retrieves results in a different way than when it is a 'normal' executable stored procedure .

An executable stored procedure is known to have only one row (or no rows) of results, so those values are retrieved immediately on execute, and the result can be retrieved using the getXXX() methods. For a selectable stored procedure the results are retrieved like a normal ResultSet. Normally for a selectable stored procedure you should use executeQuery() and process the ResultSet returned. Due to an implementation artefact and compatibility with older versions of Firebird (where it was not possible to differentiate between selectable and executable procedures), it is possible to retrieve values of the first (or current) row of the ResultSet using the getXXX() methods of the CallableStatement as well.

TL;DR: Remove the SUSPEND.

Disclosure: I am one of the developers of Jaybird