Re: ODP: [firebird-support] Query optimization on FB3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> However, could you clarify what you mean between a session or a transaction?

Session == connection
I guess you know what a transaction is.

You can define a GTT with a life that lasts as long as the transaction
in which it is instantiated, i.e.,

CREATE GLOBAL TEMPORARY TABLE name
  ( [, { | } ...])
  [ON COMMIT {DELETE | PRESERVE} ROWS]

So, ON COMMIT DELETE ROWS empties the GTT instance when the
trasnaction commits (the default), while ON COMMIT PRESERVE ROWS keeps
the data until the session (connection) ends.

For more info, see

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-gtt

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

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

[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 
> 
> [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: ODP: [firebird-support] Query optimization on FB3

[blackfalconsoftw...@outlook.com [firebird-support]]

Hi Helen...
 

 Thank you for clearing this up for me.  
 

 However, could you clarify what you mean between a session or a transaction?
 

 Steve Naidamast
 Sr. Software Engineer

Re: ODP: [firebird-support] Query optimization on FB3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> By using a global table in Firebird, could not multiple users cause
> a conflict if two such users were to issue the same query against the global 
> table?

No.  A GTT definition is persistent, of course, but persistent data
are not stored in it. A GTT instance is created for use within a
single session or transaction and dies when the session or transaction
ends.  One instance has no knowledge of another - other than multiple
GTTs within the same session or transaction - depending on the life
defined for that GTT.

> Coming from a SQL Server background, I am used to using local
> temporary tables, which are isolated on a query by query basis...

Not the same thing.  The life of a GTT instance can be transaction or
session. (Life depends on a property in the definition.)

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Query optimization on FB3

[blackfalconsoftw...@outlook.com [firebird-support]]

By using a global table in Firebird, could not multiple users cause a conflict 
if two such users were to issue the same query against the global table?
 

 Coming from a SQL Server background, I am used to using local temporary 
tables, which are isolated on a query by query basis...
 

 Steve Naidamast
 Sr. Software Engineer

ODP: ODP: [firebird-support] Stored procedure stops functioningcorrectlyafter restore under Firebird 3

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Good points Mark.

Then maybe some switch to restore process should be added?
Now this is nightmare for the end user.

regards,
Karol Bieniaszewski

ODP: [firebird-support] Query optimization on FB3

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi.

There are 2 common ways
1. Create global temporary table 
Do INSERT INTO T(DSTART, DEND) SELECT …
and then do simple join with this table
2. Use derived table e.g.
SELECT
DATENLOGGING.* …
FROM
(SELECT D.DSTART, D.DEND FROM TABLED) X
LEFT JOIN DATENLOGGING ON DATENLOGGING.DATUMZEIT>=X. DSTART AND 
DATENLOGGING.DATUMZEIT<=X.DEND
WHERE
DATENLOGGING.KEY IS NOT NULL /* do hidden inner join by providing some not 
nullable field check */

Regards,
Karol Bieniaszewski

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

[firebird-support] Query optimization on FB3

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

Does anybody has a idea to optimization my follow query?
 

 

 SELECT
  ELEMENTE.BEZEICHNUNG,
  DATENLOGGING.DATUMZEIT,
  DATENLOGGING.WERT,
  DATENLOGGINGTYPEN.MASSEINHEIT,
  DATENLOGGINGTYPEN.DATA_TYPE,
  DATENLOGGINGTYPEN.FAKTOR
FROM
  DATENLOGGING
  INNER JOIN ELEMENTE
ON DATENLOGGING.SPS_NODE = ELEMENTE.SPS_NODE AND DATENLOGGING.TYP =
ELEMENTE.TYP AND DATENLOGGING.ELEMENT = ELEMENTE.ELEMENT AND
DATENLOGGING.JOBID = ELEMENTE.JOBID
  INNER JOIN DATENLOGGINGTYPEN
ON DATENLOGGING.SPS_NODE = DATENLOGGINGTYPEN.SPS_NODE AND
DATENLOGGING.TYP = DATENLOGGINGTYPEN.TYP AND DATENLOGGING.ELEMENT =
DATENLOGGINGTYPEN.ELEMENT AND DATENLOGGING.JOBID = DATENLOGGINGTYPEN.JOBID
WHERE
  (
  (DATENLOGGING.DATUMZEIT >= '11.01.2017 12:51:26') AND (DATENLOGGING.DATUMZEIT 
<= '11.01.2017 13:51:37') OR
  (DATENLOGGING.DATUMZEIT >= '11.01.2017 13:50:13') AND (DATENLOGGING.DATUMZEIT 
<= '11.01.2017 22:07:10') OR
  (DATENLOGGING.DATUMZEIT >= '11.01.2017 22:07:38') AND (DATENLOGGING.DATUMZEIT 
<= '12.01.2017 22:04:20') OR

 

 

 her between are about 200 Lines!!

 

 

   (DATENLOGGING.DATUMZEIT >= '12.09.2017 14:03:20') AND 
(DATENLOGGING.DATUMZEIT <= '12.09.2017 16:59:18') OR
  (DATENLOGGING.DATUMZEIT >= '14.09.2017 10:54:03') AND (DATENLOGGING.DATUMZEIT 
<= '14.09.2017 16:46:48') OR
  (DATENLOGGING.DATUMZEIT >= '15.09.2017 14:29:02') AND (DATENLOGGING.DATUMZEIT 
<= '15.09.2017 14:59:27') OR
  (DATENLOGGING.DATUMZEIT >= '18.09.2017 09:58:56') AND (DATENLOGGING.DATUMZEIT 
<= '18.09.2017 10:59:10') OR
  (DATENLOGGING.DATUMZEIT >= '18.09.2017 13:58:56') AND (DATENLOGGING.DATUMZEIT 
<= '18.09.2017 14:59:10')
  ) AND
  DATENLOGGING.SPS_NODE = 100 AND DATENLOGGING.TYP = 20 AND DATENLOGGING.JOBID =
  4 AND ((DATENLOGGING.ELEMENT = 32) OR (DATENLOGGING.ELEMENT = 38) OR
(DATENLOGGING.ELEMENT = 20) OR (DATENLOGGING.ELEMENT = 35) OR
(DATENLOGGING.ELEMENT = 41) OR (DATENLOGGING.ELEMENT = 23)) AND
  DATENLOGGING.WERT IS NOT NULL
ORDER BY
  ELEMENTE.BEZEICHNUNG,
  DATENLOGGING.DATUMZEIT


 

 The follow statement in the where clause 

   (DATENLOGGING.DATUMZEIT >= '12.09.2017 14:03:20') AND 
(DATENLOGGING.DATUMZEIT <= '12.09.2017 16:59:18') OR

 

 are a result of a separate query like the follow:
 SELECT

  bm.KOMMT,
  bm.GEHT
 FROM
  bm
 
WHERE
  bm.SPS_NODE=20 and bm.TYP=25 and bm.ELEMENT=1 and
   bm.KOMMT > '01.01.2017 00:00:00' and bm.KOMMT < '31.12.2017 23:59:59'
 

 Are there are a better solution to combine the two querys?
 

 May thanks for any hints

 

 Best Regards
 Gregor

 

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

[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: ODP: [firebird-support] Stored procedure stops functioning correctlyafter restore under Firebird 3

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

On 13-2-2019 17:48, Karol Bieniaszewski liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
>  >>Recompiling stored
>  >>procedures, triggers, etc is always a good thing to do when upgrading.
>  >>Mark
> 
> Why it is not done automatically on restore if there is source present?

I shouldn't have called it recompiling, because technically it is more 
recreating (or altering).

And it is not so simple: the BLR of a stored procedure might still be 
valid and continue to work, but the stored source might not (eg consider 
reserved words).

For example:

create procedure dummy
as
declare boolean smallint;
begin
boolean = 1;
end

If you create this on Firebird 2.5 and restore it on Firebird 3.0, it 
will 'work' just fine. But if you create / recreate / create or alter it 
with this body on Firebird 3 it will fail with a 'Token unknown - line 
4, column 9 boolean' as boolean is a reserved word since Firebird 3.

Another situation would be for example certain ambiguous syntax that is 
no longer allowed, but when the statement was compiled it was 
transformed to low-level BLR which is not ambiguous and is still 
executable, but recompiling it will no longer work (or worse, in a 
version where the ambiguity was allowed, it could 'switch' between 
behavior depending on optimizer choices, although that is probably more 
a theoretical concern).

In the above cases, it could of course fallback to the BLR, but now you 
have two possible routes for stored procedure creation during restore, 
which has its own cost in terms of complexity and maintenance.

And a bit more in the 'farfetched' realm, but less benign than the 
previous, given the historic mutability of the metadata tables, it is 
impossible for Firebird to distinguish between the original and valid 
source and altered (but still compilable) source (eg a programmer/vendor 
that the replaced the source not with null, but with - for example -an 
empty procedure body). In such a situation, recreating from source may 
render the database inoperable because the new code does nothing, or 
maybe even introduce 'malicious' code (as someone replaced the body with 
a form of stored SQL injection).

In short, the current way of working may not be ideal, but the 
alternative is probably worse.

And, you should really test your upgrade between major versions anyway 
(even if Firebird were to recreate procedures from source).

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

[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

ODP: [firebird-support] Stored procedure stops functioning correctlyafter restore under Firebird 3

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

>>Recompiling stored 
>>procedures, triggers, etc is always a good thing to do when upgrading.
>>Mark

Why it is not done automatically on restore if there is source present?

Pozdrawiam,
Karol Bieniaszewski

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

[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

[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] firebird 1.5 installation in linux

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

On 13-2-2019 07:57, wang...@yahoo.com [firebird-support] wrote:
> firebird 1.5 linux built is for 32 bits, is it possible to install it in 
> a 64 bit linux? Thanks.

Firebird 1.5 has been unsupported for almost 10 years now. It is really 
time to start upgrading.

I think the only way is to try it yourself, or hope someone else still 
stuck on Firebird 1.5 has already tried and solved (or not solved) this 
same problem.

Mark
-- 
Mark Rotteveel

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

[david.holli...@parcelperfect.com [firebird-support]]

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?

Thanks and regards
David

Re: {Disarmed} [firebird-support] firebird 1.5 installation in linux

[Elmar Haneke el...@haneke.de [firebird-support]]

> firebird 1.5 linux built is for 32 bits, is it possible to install it
> in a 64 bit linux? Thanks.


As long as your 64Bit Linux does have support for 32Bit and the required
32Bit runtimes are installed it should be possible to install.