After some experimenting, I think the problem is that your stored
procedure is selectable (ie it contains SUSPEND) and a non-zero
RDB$PROCEDURE_TYPE.

Under Firebird 1.5 you would need to explicitly inform Jaybird to handle
the procedure as selectable, but on Firebird 2.1 or newer this is
automatically inferred from the procedure type. As a result, in Firebird
1.5, the procedure was executed using EXECUTE PROCEDURE name(..), and
now with SELECT .. FROM name(...).

You are subsequently confronted with a combination of ambiguous behavior
in Jaybird that presents the stored procedure as producing a result set
and having OUT parameters, and spring-data-jpa unconditionally
retrieving (and discarding) all rows of the result set and trying to
extract OUT parameters. Behind the surface the OUT parameters in this
case are backed by the same result set, which then yields a result set
is closed error because all rows have already been processed.

This ambiguous behavior is a form of backwards compatibility that works
fine if a human writes the stored procedure data extraction, but less so
when a library tries to automatically infer from the metadata. The
problem is that I probably can't fix this without breaking existing code
relying on that ambiguity.

Currently, the problem is likely best fixed on your side by removing
SUSPEND from the stored procedure so it will always be executed with
EXECUTE PROCEDURE. If it was accidentally selectable and produces a
single row this will work ok unless you have code that uses select *
from procedure_name(..) as Firebird 3 disallows selecting from
executable procedures. Otherwise, you'll probably need to write an
explicit query.

I'll try and think of other options to address this, but they will
likely require a significant rewrite in Jaybird which will break
backwards compatibility (or require more complexity in the form of
compatibility connection properties).

On 16-2-2019 09:35, mark@... [Firebird-Java] wrote:
> Which Jaybird version are you using (full version please), and are you using the same Jaybird version for both Firebird 1.5 and 3.0?
>
> What is the definition of the stored procedure? At minimum I need to know the declaration (CREATE PROCEDURE up to AS) and if the body contains SUSPEND or not.
>
> Did you recreate the procedure in Firebird 3.0, or did you only backup and restore?
>
> What is the output - both on Firebird 1.5 and 3.0 - for
>
> select
> RDB$PROCEDURE_NAME,
> RDB$PROCEDURE_INPUTS,
> RDB$PROCEDURE_OUTPUTS,
> RDB$SYSTEM_FLAG,
> RDB$PROCEDURE_TYPE
> from RDB$PROCEDURES
> where RDB$PROCEDURE_NAME = '<your procedure>'
>
> (on Firebird 1.5, remove the RDB$PROCEDURE_TYPE column)
>
> and the output for
>
> select
> RDB$PARAMETER_NAME,
> RDB$PROCEDURE_NAME,
> RDB$PARAMETER_NUMBER,
> RDB$PARAMETER_TYPE
> from RDB$PROCEDURE_PARAMETERS
> where RDB$PROCEDURE_NAME = '<your procedure>'
>
> It would also be helpful if you can provide the result set data (again for both Firebird 1.5 and Firebird 3.0) of
>
> - DatabaseMetaData.getProcedures(null, null, "<your procedure>")
> - DatabaseMetaData.getProcedureColumns(null, null, "<your procedure>", "%")


--
Mark Rotteveel