Hello Mark,

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 (

    if_stno smallint,

    if_genno integer,

    if_saletype char(1),

    if_userstno char(5),

    if_host char(1))

returns (

    item_count smallint)

as

​

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

​

select

RDB$PROCEDURE_NAME,

RDB$PROCEDURE_INPUTS,

RDB$PROCEDURE_OUTPUTS,

RDB$SYSTEM_FLAG,

RDB$PROCEDURE_TYPE

from RDB$PROCEDURES

where RDB$PROCEDURE_NAME = 'INV_FINALIZE'

​

Response:

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

​

SELECT

RDB$PARAMETER_NAME,

RDB$PROCEDURE_NAME,

RDB$PARAMETER_NUMBER,

RDB$PARAMETER_TYPE

FROM RDB$PROCEDURE_PARAMETERS

WHERE RDB$PROCEDURE_NAME = 'INV_FINALIZE'

​

Response:

Firebird 1.5:  

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

explicit query.

​

Response:

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,