On 18-2-2019 18:05, blyons3@... [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?
> Response: We are using jaybird-full-3.0.5 for both Firebird 1.5 and 3.0.

Ok, so that eliminates cross-Jaybird-version changes :)
​

> 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

Is this procedure intended to return only a single value ever, or can it
produce multiple rows? The @Procedure annotation in spring-data-jpa is
extremely limited (only one OUT/return column, and only a single result)

> Did you recreate the procedure in Firebird 3.0, or did you only backup
> and restore?
> Response: We did a backup and restore.

The fact Firebird 3 reports RDB$PROCEDURE_TYPE = 1 and not 0 suggests it
was also recreated or altered.

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

Interesting, I wonder why spring-data-jpa generated a call-escape with
different nr of parameters. I'll check if there is a difference in the
reported metadata.

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

Yes it would.

> --- Post 2
> After some experimenting, I think the problem is that your stored

[..]

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

Yes, that should work just fine. From Jaybirds point of view, if
executed as a normal statement or prepared statement, which happens when
you use @Query, it is just a select statement.

Things are slightly more complex when executed through a callable statement.

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

I've created a ticket (http://tracker.firebirdsql.org/browse/JDBC-576)
for a connection property to disable the automatic inference of the
procedure type. It should be pretty straightforward, but I'm not 100%
sure yet if I'm going to implement it (as there are some other oddities
in the CallableStatement implementation that may cause interference and
further weird behaviour).

Mark
--
Mark Rotteveel