Replace external function BIN_AND by internal BIN_AND after upgrade from Firebird 2.5 to FB 4.0

[Johan Van De Putte]

Hi

I'm preparing a Firebird upgrade from our current version 2.5 to version 4.0. Using the short migration guide to FB4.0, I managed to restore a FB2.5 database backup with FB4.0, but I ran into problems with the deprecated UDFs. In FB2.5 we use BIN_AND as an external function from ib_udf.dll. As these are deprecated in FB4.0, we need to start using the builtin BIN_AND function. The short migration guide to FB4.0 refers to the udf_replace.sql script, but running it didn't repclace the function. When I looked into the script and in the  FB 4.0 release notes, I noticed BIN_AND is not in the script. 

I also tried to simply drop the external function (DROP EXTERNAL FUNCTION BIN_AND;), but that  did not work because there are 113 dependencies. 

My question: is there an elegant way to get rid of the external function declarations and start using the builtin BIN_AND, other than 

1. deleting all dependencies by removing (one way or another) all BIN_AND calls from our SPs, triggers, constraints
2. dropping the BIN_AND external function declarations
3. putting all BIN_AND calls back in place

If more information is needed, I'm happy to provide it.

Thanks in advance

Johan

[Dimitry Sibiryakov]

Johan Van De Putte wrote 18.08.2022 21:09:
> My question: is there an elegant way to get rid of the external function
> declarations and start using the builtin BIN_AND, other than
>

> 1. deleting all dependencies by removing (one way or another) all BIN_AND calls

> from our SPs, triggers, constraints

> 2. dropping the BIN_AND external function declarations
> 3. putting all BIN_AND calls back in place

I would wuggest:

1. Create a fresh database from master-script.
2. Pump data.

--
WBR, SD.

[Johan Van De Putte]

128 / 5.000

Vertaalresultaten

star_border

Thanks for the suggestion, I'll check that out.

Op vr 19 aug. 2022 om 11:56 schreef Dimitry Sibiryakov <s...@ibphoenix.com>:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3717ab59-1fb3-4807-316e-41ef4a1349c2%40ibphoenix.com.

[Johan Van De Putte]

Thanks for the suggestion, I'll check that out.

Op vr 19 aug. 2022 om 11:56 schreef Dimitry Sibiryakov <s...@ibphoenix.com>:

Johan Van De Putte wrote 18.08.2022 21:09:

[Gabor Boros]

2022.08.18. 21:09 keltezéssel, Johan Van De Putte írta:
> I managed to restore a FB2.5 database backup with FB4.0, but I ran into
> problems with the deprecated UDFs. In FB2.5 we use BIN_AND as an
> external function from ib_udf.dll.

You can use ib_udf.dll from an earlier version with 4.0 for a simpler
conversion. Just copy the udf directory from an earlier release and
modify the firebird.conf.

UDF's name are not hardcoded. You can

DECLARE EXTERNAL FUNCTION udf_bin_and
INTEGER, INTEGER
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_bin_and' MODULE_NAME 'ib_udf';

Replace in the 2.5 database bin_and with udf_bin_and at the
dependencies. After restore with 4.0 replace udf_bin_and with bin_and
and the internal function used (if bin_and dropped with 2.5 before backup).

Gabor

[Johan Van De Putte]

Thank you for the suggestions

In the meantime I have been able to perform the upgrade by using scripts generated with 'Firebird SQL Studio' (via 'extract metadata' tool). For stored procedures such a script contains a first part in which all our stored procedures are created as 'empty' procedures (without a 'body'), for example: 

CREATE PROCEDURE MyProcedure(...) RETURNS (...) AS
BEGIN
  SUSPEND;
  EXIT;
END ^

In a second part all 'bodies' are added. (The same for triggers, but unfortunately there was no section in that script that created triggers with 'empty' bodies, so I made these manually, which was doable because of the limited number of dependencies we have in triggers; Constraints and views only contained a handful of dependencies, so that was also no problem)

That way I relatively easily put together a script that removed all BIN_AND/BIN_OR dependencies, and a second one to add back the bodies of SPs and triggers, and the handful of constraints and views. After running the first script on the 2.5 database, I could delete the BIN_AND and BIN_OR external functions. Running the second script on the database (restored in firebird 4), added all the SPs, trigger,... implementations again.  

Op ma 22 aug. 2022 om 12:45 schreef Gabor Boros <mlng...@bgss.hu>:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/29a1bf70-1940-ba52-922e-e8ba09272dbe%40bgss.hu.