Our replies are inline with your original questions below:
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?
Response: We are using jaybird-full-3.0.5 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.
Response: See below. It does contain SUSPEND.
create or alter procedure inv_finalize (
... lots of code...
some SUSPEND statements
Did you recreate the procedure in Firebird 3.0, or did you only backup and restore?
Response: We did a backup and restore.
What is the output - both on Firebird 1.5 and 3.0 - for
where RDB$PROCEDURE_NAME = 'INV_FINALIZE'
Firebird 1.5: Inputs = 5 Outputs = 1 SysFlag null
Firebird 3.0: Inputs = 5 Outputs = 1 SysFlag 0 PROCEDURE_TYPE 1
(on Firebird 1.5, remove the RDB$PROCEDURE_TYPE column)
and the output for
WHERE RDB$PROCEDURE_NAME = 'INV_FINALIZE'
RDB$PARAMETER_NAME RDB$PROCEDURE_NAME RDB$PARAMETER_NUMBER RDB$PARAMETER_TYPE
IF_STNO INV_FINALIZE 0 0
IF_GENNO INV_FINALIZE 1 0
IF_SALETYPE INV_FINALIZE 2 0
IF_USERSTNO INV_FINALIZE 3 0
IF_HOST INV_FINALIZE 4 0
ITEM_COUNT INV_FINALIZE 0 1
Firebird 3.0: Identical to output for Firebird 1.5.
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, "INV_FINALIZE")
DatabaseMetaData.getProcedureColumns(null, null, "INV_FINALIZE", "%")
These require direct JDBC connection to execute correct? - working on it...
--- Post 2
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. - Correct!
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
In general all of our stored procedures are selectable and contain suspends.
We would prefer to use @Query( ., nativeQuery=true)instead of @Procedure.
This will be much easier than removing all the Suspends from our procedures.
Can we use this approach without concern using the jaybird 3.x driver?
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).
Thanks for your quick response Mark, Bill