Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

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.holli...@parcelperfect.com [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

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Kevin Stanton kevin.stan...@rdb-solutions.com [firebird-support]]

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 m...@lawinegevaar.nl 
> [firebird-support]  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.holli...@parcelperfect.com 
>  [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]

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

13.02.2019 16:39, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote:
> My bet is on the addition of the savepoint.

   Yes, this is the way partial cursor stability is implemented in 3.0: 
insert-select 
cannot see changes made inside of the same savepoint, so sub-query in second 
query miss 
data inserted by first insert.
   In any case the procedure smells. It would better to use RETURNING for that 
purpose 
instead of sub-query.

-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 13-2-2019 16:58, Kevin Stanton kevin.stan...@rdb-solutions.com 
[firebird-support] wrote:
> 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.

The choice is to either carefully and exhaustively verify if all your 
stored procedures and triggers continue to work correctly, and only 
recreate those that you know need it, or recreate the whole shebang (you 
will still need to carefully and exhaustively verify them).

Firebird itself has no way to recreate stored procedure and triggers 
automatically, although it is possible to craft scripts that rebuild it 
based on the database metadata (assuming the source blobs haven't been 
cleared). I would recommend against doing that automatically and instead 
you should use known good and verified scripts for the actual upgrade, 
if only to avoid running into issue with things that are now no longer 
syntactically valid or unquoted use of identifiers that are now reserved 
words.

Some people advocate that you shouldn't upgrade a database at all, but 
instead recreate it and pump the data over. In a situation with a lot of 
deployments that might also fix problems with diverging structure (but 
if that is the case, the migration path will be more prone to errors).

Especially when taking the big step from 1.5, it might make sense to 
take it as an opportunity to improve your database design, and get rid 
of things that are no longer needed (eg use built-in functions instead 
of UDFs, switch from dialect 1 to dialect 3 if you are still on dialect 
1), etc. and then migrate the data from the old to the new.

Firebird 1.5 to 2.0 was already a big step with a lot of new features, 
but also some breaking changes, sometimes subtle, sometimes not. Don't 
expect going from 1.5 to be simply backing up and restoring, especially 
not with 909 stored procedures and 785 triggers, and more so if you use 
a lot of UDFs, or relied on some of the ambiguous behavior that is no 
longer valid, used unquoted identifiers that are now reserved words, etc.

And realize that Firebird 2.0 was released 12 years ago. A lot of 
migration knowledge from 1.5 to 2.0 may have been forgotten since (or at 
best not easily recalled). You should really carefully study the 2.0, 
2.1, 2.5 and 3.0 release notes and the Firebird 2 installation and 
migration guide, and as you bought it, Carlos' Migration Guide to 
Firebird 3.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Kevin Stanton kevin.stan...@rdb-solutions.com [firebird-support]]

Thanks Mark.  I realize it’s a huge migration.  I will be exhaustively testing 
everything.  I like the idea of creating an empty database and pumping the data 
over.  Is there a tool for this?  (Actually just found one from Clever 
Components)

I have looked at the migration docs for the 2.x versions and will do more.

Do you think it’s worth me going to 2.5 first or go directly to 3.0?  Both will 
be a ton of work so I’m thinking directly to 3.0.

Thanks again!
Kevin



 

> On Feb 13, 2019, at 9:05 AM, Mark Rotteveel m...@lawinegevaar.nl 
> [firebird-support]  wrote:
> 
> On 13-2-2019 16:58, Kevin Stanton kevin.stan...@rdb-solutions.com 
>  
> [firebird-support] wrote:
> > 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.
> 
> The choice is to either carefully and exhaustively verify if all your 
> stored procedures and triggers continue to work correctly, and only 
> recreate those that you know need it, or recreate the whole shebang (you 
> will still need to carefully and exhaustively verify them).
> 
> Firebird itself has no way to recreate stored procedure and triggers 
> automatically, although it is possible to craft scripts that rebuild it 
> based on the database metadata (assuming the source blobs haven't been 
> cleared). I would recommend against doing that automatically and instead 
> you should use known good and verified scripts for the actual upgrade, 
> if only to avoid running into issue with things that are now no longer 
> syntactically valid or unquoted use of identifiers that are now reserved 
> words.
> 
> Some people advocate that you shouldn't upgrade a database at all, but 
> instead recreate it and pump the data over. In a situation with a lot of 
> deployments that might also fix problems with diverging structure (but 
> if that is the case, the migration path will be more prone to errors).
> 
> Especially when taking the big step from 1.5, it might make sense to 
> take it as an opportunity to improve your database design, and get rid 
> of things that are no longer needed (eg use built-in functions instead 
> of UDFs, switch from dialect 1 to dialect 3 if you are still on dialect 
> 1), etc. and then migrate the data from the old to the new.
> 
> Firebird 1.5 to 2.0 was already a big step with a lot of new features, 
> but also some breaking changes, sometimes subtle, sometimes not. Don't 
> expect going from 1.5 to be simply backing up and restoring, especially 
> not with 909 stored procedures and 785 triggers, and more so if you use 
> a lot of UDFs, or relied on some of the ambiguous behavior that is no 
> longer valid, used unquoted identifiers that are now reserved words, etc.
> 
> And realize that Firebird 2.0 was released 12 years ago. A lot of 
> migration knowledge from 1.5 to 2.0 may have been forgotten since (or at 
> best not easily recalled). You should really carefully study the 2.0, 
> 2.1, 2.5 and 3.0 release notes and the Firebird 2 installation and 
> migration guide, and as you bought it, Carlos' Migration Guide to 
> Firebird 3.
> 
> Mark
> -- 
> Mark Rotteveel
> 
> 



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

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Gregor Kobler gregorkob...@yahoo.com [firebird-support]]

The restore are not validating the Stored Procedures! After recreation in FB3 
you have to validate/compile you SP!
 

 Regards
 Gregor

 

 

 

 Sent from Yahoo Mail. Get the app https://yho.com/148vdq


 

 

 
 
 
 

 
 

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

13.02.2019 18:29, Kevin Stanton kevin.stan...@rdb-solutions.com 
[firebird-support] wrote:
> I like the idea of creating an empty database and pumping the data over. Is 
> there a tool 
> for this? (Actually just found one from Clever Components)

   I'd recommend to look at FBCopy/FBExport tools.



-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 13-2-2019 18:29, Kevin Stanton kevin.stan...@rdb-solutions.com 
[firebird-support] wrote:
> Thanks Mark.  I realize it’s a huge migration.  I will be exhaustively 
> testing everything.  I like the idea of creating an empty database and 
> pumping the data over.  Is there a tool for this?  (Actually just found one 
> from Clever Components)

There are a number of Firebird-specific tools, see for example 
https://www.ibphoenix.com/download/tools/migration

But at times I just resort to writing such things in code or using an 
ETL tool (especially if it isn't between the same structure, or if data 
needs some additional transformations).

> I have looked at the migration docs for the 2.x versions and will do more.
> 
> Do you think it’s worth me going to 2.5 first or go directly to 3.0?  Both 
> will be a ton of work so I’m thinking directly to 3.0.

I'd suggest going to Firebird 3 directly. I'd almost suggest trying to 
go for Firebird 4, but as it is still in development that is probably 
not the best idea.

Mark
-- 
Mark Rotteveel

RE: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support]]

Hi Kevin – I’ve had good results using fbclone to migrate from InterBase 7.5.1 
to Firebird 3 using this method.  Migrating from 1.5 might not be too 
dissimilar.
https://github.com/zedalaye/fbclone

I made a couple of changes to support dialect 1 (we’re going to switch to 
dialect 3 and remove legacy UDFs etc after our migration is complete) and 
reverse migration.
https://github.com/stevedrake/fbclone
https://github.com/stevedrake/uib



From: firebird-support@yahoogroups.com 
Sent: Thursday, 14 February 2019 6:30 AM
To: Firebird Support 
Subject: Re: [firebird-support] Stored procedure stops functioning correctly 
after restore under Firebird 3



Thanks Mark. I realize it’s a huge migration. I will be exhaustively testing 
everything. I like the idea of creating an empty database and pumping the data 
over. Is there a tool for this? (Actually just found one from Clever Components)

I have looked at the migration docs for the 2.x versions and will do more.

Do you think it’s worth me going to 2.5 first or go directly to 3.0? Both will 
be a ton of work so I’m thinking directly to 3.0.

Thanks again!
Kevin

> On Feb 13, 2019, at 9:05 AM, Mark Rotteveel m...@lawinegevaar.nl 
> [firebird-support]  wrote:
>
> On 13-2-2019 16:58, Kevin Stanton kevin.stan...@rdb-solutions.com 
> <mailto:kevin.stan...@rdb-solutions.com>
> [firebird-support] wrote:
> > 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.
>
> The choice is to either carefully and exhaustively verify if all your
> stored procedures and triggers continue to work correctly, and only
> recreate those that you know need it, or recreate the whole shebang (you
> will still need to carefully and exhaustively verify them).
>
> Firebird itself has no way to recreate stored procedure and triggers
> automatically, although it is possible to craft scripts that rebuild it
> based on the database metadata (assuming the source blobs haven't been
> cleared). I would recommend against doing that automatically and instead
> you should use known good and verified scripts for the actual upgrade,
> if only to avoid running into issue with things that are now no longer
> syntactically valid or unquoted use of identifiers that are now reserved
> words.
>
> Some people advocate that you shouldn't upgrade a database at all, but
> instead recreate it and pump the data over. In a situation with a lot of
> deployments that might also fix problems with diverging structure (but
> if that is the case, the migration path will be more prone to errors).
>
> Especially when taking the big step from 1.5, it might make sense to
> take it as an opportunity to improve your database design, and get rid
> of things that are no longer needed (eg use built-in functions instead
> of UDFs, switch from dialect 1 to dialect 3 if you are still on dialect
> 1), etc. and then migrate the data from the old to the new.
>
> Firebird 1.5 to 2.0 was already a big step with a lot of new features,
> but also some breaking changes, sometimes subtle, sometimes not. Don't
> expect going from 1.5 to be simply backing up and restoring, especially
> not with 909 stored procedures and 785 triggers, and more so if you use
> a lot of UDFs, or relied on some of the ambiguous behavior that is no
> longer valid, used unquoted identifiers that are now reserved words, etc.
>
> And realize that Firebird 2.0 was released 12 years ago. A lot of
> migration knowledge from 1.5 to 2.0 may have been forgotten since (or at
> best not easily recalled). You should really carefully study the 2.0,
> 2.1, 2.5 and 3.0 release notes and the Firebird 2 installation and
> migration guide, and as you bought it, Carlos' Migration Guide to
> Firebird 3.
>
> Mark
> --
> Mark Rotteveel
>
>


Note:
This e-mail message has been inspected for malicious content.

Attention:
The information contained in this message and or attachments is intended only 
for the person
or entity to which it is addressed and may contain confidential and/or 
privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action 
in reliance upon,
this information by persons or entities other than the intended recipient is 
prohibited. If you
received this in error, please contact the sender and delete the material from 
any system and
destroy any copies.
Please note that the views and opinions expressed in this message may be those 
of the
individual and not necessarily those of Foodstuffs South Island Ltd.

Thank you.

Re: [firebird-support] Stored procedure stops functioning correctly after restore under Firebird 3

[Kevin Stanton kevin.stan...@rdb-solutions.com [firebird-support]]

Thank you!  Thankfully I made the dialect 1 to 3 conversion years ago.

I will check these out.  It will be a bit before I can devote 100% on this.




 

> On Feb 13, 2019, at 1:03 PM, FSPAPA INCA Team i...@foodstuffs-si.co.nz 
> [firebird-support]  wrote:
> 
> 
> Hi Kevin – I’ve had good results using fbclone to migrate from InterBase 
> 7.5.1 to Firebird 3 using this method.  Migrating from 1.5 might not be too 
> dissimilar.
> 
> https://github.com/zedalaye/fbclone <https://github.com/zedalaye/fbclone>
>  
> 
> I made a couple of changes to support dialect 1 (we’re going to switch to 
> dialect 3 and remove legacy UDFs etc after our migration is complete) and 
> reverse migration.
> 
> https://github.com/stevedrake/fbclone <https://github.com/stevedrake/fbclone>
> https://github.com/stevedrake/uib <https://github.com/stevedrake/uib>
>  
> 
>  
> 
>  
> 
> From: firebird-support@yahoogroups.com 
> <mailto:firebird-support@yahoogroups.com>  <mailto:firebird-support@yahoogroups.com>> 
> Sent: Thursday, 14 February 2019 6:30 AM
> To: Firebird Support  <mailto:firebird-support@yahoogroups.com>>
> Subject: Re: [firebird-support] Stored procedure stops functioning correctly 
> after restore under Firebird 3
> 
>  
> 
>   
> 
> Thanks Mark. I realize it’s a huge migration. I will be exhaustively testing 
> everything. I like the idea of creating an empty database and pumping the 
> data over. Is there a tool for this? (Actually just found one from Clever 
> Components)
> 
> I have looked at the migration docs for the 2.x versions and will do more..
> 
> Do you think it’s worth me going to 2.5 first or go directly to 3..0? Both 
> will be a ton of work so I’m thinking directly to 3.0.
> 
> Thanks again!
> Kevin
> 
> > On Feb 13, 2019, at 9:05 AM, Mark Rotteveel m...@lawinegevaar.nl 
> > <mailto:m...@lawinegevaar.nl> [firebird-support] 
> >  > <mailto:firebird-support@yahoogroups.com>> wrote:
> > 
> > On 13-2-2019 16:58, Kevin Stanton kevin.stan...@rdb-solutions.com 
> > <mailto:kevin.stan...@rdb-solutions.com> 
> > <mailto:kevin.stan...@rdb-solutions.com 
> > <mailto:kevin.stan...@rdb-solutions.com>> 
> > [firebird-support] wrote:
> > > 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.
> > 
> > The choice is to either carefully and exhaustively verify if all your 
> > stored procedures and triggers continue to work correctly, and only 
> > recreate those that you know need it, or recreate the whole shebang (you 
> > will still need to carefully and exhaustively verify them).
> > 
> > Firebird itself has no way to recreate stored procedure and triggers 
> > automatically, although it is possible to craft scripts that rebuild it 
> > based on the database metadata (assuming the source blobs haven't been 
> > cleared). I would recommend against doing that automatically and instead 
> > you should use known good and verified scripts for the actual upgrade, 
> > if only to avoid running into issue with things that are now no longer 
> > syntactically valid or unquoted use of identifiers that are now reserved 
> > words.
> > 
> > Some people advocate that you shouldn't upgrade a database at all, but 
> > instead recreate it and pump the data over. In a situation with a lot of 
> > deployments that might also fix problems with diverging structure (but 
> > if that is the case, the migration path will be more prone to errors).
> > 
> > Especially when taking the big step from 1.5, it might make sense to 
> > take it as an opportunity to improve your database design, and get rid 
> > of things that are no longer needed (eg use built-in functions instead 
> > of UDFs, switch from dialect 1 to dialect 3 if you are still on dialect 
> > 1), etc. and then migrate the data from the old to the new.
> > 
> > Firebird 1.5 to 2.0 was already a big step with a lot of new features, 
> > but also some breaking changes, sometimes subtle, sometimes not. Don't 
> > expect going from 1.5 to be simply backing up and restoring, especially 
> > not with 909 stored procedures and 785 triggers, and more so if you use 
> > a lot of UDFs, or relied on some of the ambiguous