I’m starting to look into migrating from 1.56 to either 2.5 or directly to 3.0. I’ve bought the Migration Guide but haven’t read it yet.

It concerns me that I would/will have to recompile all SPs and triggers. Is there an automated way to do this? I have 909 SPs and 785 triggers. This will be one heck of a task with my installation base.

Thanks,
Kevin

> On Feb 13, 2019, at 7:39 AM, Mark Rotteveel mark@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
>
> I can reproduce it (Firebird 2.5.8 to Firebird 3.0.4).
>
> I suggest that you create a ticket in the tracker. The problem doesn't
> sound familiar to me, and if it is a known problem, the core developers
> will close appropriately to an existing duplicate.
>
> Looking at the BLR (using set blob all in isql) and then selecting the
> BLR from RDB$PROCEDURES does show some differences (after recreating the
> procedure in Firebird 3):
>
> --- v25blr.txt 2019-02-13 16:33:58.530790700 +0100
> +++ v30blr.txt 2019-02-13 16:33:48.811658200 +0100
> @@ -18,6 +18,8 @@
> blr_parameter2, 0, 0,0, 1,0,
> blr_field, 0, 2, 'P','K',
> blr_end,
> + blr_begin,
> + blr_start_savepoint,
> blr_store,
> blr_relation, 7, 'T','A','B','L','E','T','O', 2,
> blr_begin,
> @@ -35,14 +37,14 @@
> blr_parameter2, 0, 0,0, 1,0,
> blr_end,
> blr_cast, blr_float,
> - blr_value_if,
> - blr_missing,
> + blr_coalesce, 2,
> blr_field, 1, 6,
> 'A','M','O','U','N','T',
> blr_literal, blr_long, 0, 0,0,0,0,
> - blr_field, 1, 6,
> 'A','M','O','U','N','T',
> blr_null,
> blr_field, 2, 6, 'A','M','O','U','N','T',
> blr_end,
> + blr_end_savepoint,
> + blr_end,
> blr_end,
> blr_end,
> blr_end,
>
> My bet is on the addition of the savepoint.
>
> I'm not sure if this is fixable during a restore. Recompiling stored
> procedures, triggers, etc is always a good thing to do when upgrading.
>
> Mark
>
> On 13-2-2019 14:57, david.hollings@... <mailto:david.hollings@...> [firebird-support]
> wrote:
> > When migrating from Firebird 2.5 to Firebird 3 we have a number of stored
> > procedures that stop functioning correctly. They start working again once
> > the procedures have been rebuilt from script.
> > The procedures all perform something along the lines of inserting a record
> > with a default value and then copying this record to a different table.
> >
> > The copy (insert) fails with:
> >
> > validation error for column "TABLETO"."AMOUNT", value "*** null ***"
> >
> >
> > Simple script to reproduce:
> >
> > CREATE TABLE TABLETO (
> > PK INTEGER NOT NULL,
> > AMOUNT FLOAT DEFAULT 0.00 NOT NULL,
> > PRIMARY KEY(PK));
> >
> > CREATE TABLE TABLEFROM (
> > PK INTEGER NOT NULL,
> > AMOUNT FLOAT DEFAULT 0.00 NOT NULL,
> > PRIMARY KEY(PK));
> >
> > SET TERM ^;
> > CREATE PROCEDURE TESTCOPY (PK INTEGER)
> > AS BEGIN
> > INSERT INTO TABLEFROM(PK) VALUES (:PK);
> > INSERT INTO TABLETO (PK, AMOUNT) VALUES (:PK, (SELECT
> > COALESCE(AMOUNT, 0) FROM TABLEFROM WHERE PK = :PK));
> > END ^
> >
> >
> >
> > Steps to reproduce:
> >
> > - Create new database under Firebird 2.5
> > - Run sample script
> > - Back up the database
> > - Restore database under Firebird 3
> > - Run command "execute procedure testcopy(1);" via isql
> >
> > Versions:
> > Firebird 2.5.8
> > Firebird 3.0.4
> >
> > Is this a known issue or bug?
>
> --
> Mark Rotteveel
>
>

[Non-text portions of this message have been removed]