在获取输出参数之前，不会执行Jaybird CallableStatement [英] Jaybird CallableStatement not executed until getting output parameter

问题描述

我有这个Java代码:

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();

rm_set_coordinates是Firebird存储过程的地方:

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

当我执行此代码时，ref_car表中的数据未更改. 但是，如果我将这一行添加到上面的代码中:

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);

返回输出参数oresult的值，就可以了，ref_car表中的数据将被更新.

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

推荐答案

问题是您在存储过程中使用了SUSPEND.关键字SUSPEND用于可选的存储过程(产生多行数据的存储过程).由于您的存储过程仅产生一行，因此SUSPEND是不必要的(及其原因).

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

对于可选过程，Jaybird将您的execute procedure rm_set_coordinates(?,?,?)转换为SELECT * FROM rm_set_coordinates(?,?,?).我不确定100％Firebird存储过程的实现细节，但是看起来包含SUSPEND的块只是在实际获取一行时才执行，或者直到上一个SUSPEND的所有更改都在还原时才执行在关闭或重用该语句之前未获取行.

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.

但是，最终结果是:未获取任何行，未进行任何更改.当执行一个可选的存储过程(即，它包含一个SUSPEND)时，Jaybird检索结果的方式不同于它是常规"可执行存储过程的方式.

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 .

众所周知，可执行存储过程仅具有一行结果(或没有行)，因此在执行时立即检索这些值，并且可以使用getXXX()方法检索结果.对于可选的存储过程，其结果像普通的ResultSet一样被检索.通常，对于可选的存储过程，应使用executeQuery()并处理返回的ResultSet.由于实现伪像并与Firebird的较早版本兼容(无法在可选过程和可执行过程之间进行区分)，因此可以使用ResultSet的第一行(或当前行)的值. > CallableStatement的方法.

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 :删除SUSPEND.

披露:我是Jaybird的开发人员之一

Disclosure: I am one of the developers of Jaybird

这篇关于在获取输出参数之前，不会执行Jaybird CallableStatement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！