FreeAdhocUDF for InterBase and FireBird in deutschin englishen français
homeprevious pagecontentnext pagelast page mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprint
Possibility to replace UDFs EVEN if there are dependendies

If you want to change to FreeAdhocUDF there is often the problem that there are dependendies on the samenamed UDFs (because they where used in ComputedBy fields, triggers, views or stored procedures) and you could not drop them (until InterBase 6 this was possible).
Here’s a (not clean but save) trick to do this:
/* UDF-dependendies inactivate */
Version for InterBase
 UPDATE RDB$DEPENDENCIES SET RDB$DEPENDED_ON_NAME = 'xyz' || RDB$DEPENDED_ON_NAME
WHERE RDB$DEPENDED_ON_TYPE = 14;
Version for FireBird
UPDATE RDB$DEPENDENCIES SET RDB$DEPENDED_ON_NAME = 'xyz' || RDB$DEPENDED_ON_NAME
WHERE RDB$DEPENDED_ON_TYPE = 15;
/* now drop the UDF-functions */
DROP  EXTERNAL FUNCTION .....;
DROP  EXTERNAL FUNCTION .....;
...
/* now append FreeAdhocUDF Funktionen with the script */
...
/* activate the (old) dependendies */
Version for InterBase
UPDATE RDB$DEPENDENCIES
SET RDB$DEPENDED_ON_NAME = F_REPLACESTRING(RDB$DEPENDED_ON_NAME, 'xyz', '', 0, 0)
WHERE RDB$DEPENDED_ON_TYPE = 14 AND RDB$DEPENDED_ON_NAME STARTING WITH 'xyz';     
Version for FireBird
UPDATE RDB$DEPENDENCIES
SET RDB$DEPENDED_ON_NAME = F_REPLACESTRING(RDB$DEPENDED_ON_NAME, 'xyz', '', 0, 0)
WHERE RDB$DEPENDED_ON_TYPE = 15 AND RDB$DEPENDED_ON_NAME STARTING WITH 'xyz';   

Remark:
In InterBase 6.0 and befor there was no table RDB$DEPENDENCIES - and also no dependencies where journalised.
Since InterBase 6.5 and FireBird 1.5 dependencies where journalised in the table RDB$DEPENDENCIES.
With the following script you get an overview of the dependencies in your database:
Version for InterBase from 7.5
SELECT RDB$DEPENDENT_NAME AS DEPENDENT_NAME,
  RDB$DEPENDED_ON_NAME AS DEPENDS_ON,
  RDB$FIELD_NAME AS FIELD_NAME,
  RDB$DEPENDENT_TYPE DEPENDENT_TYPE,
    CASE
      WHEN RDB$DEPENDENT_TYPE = 0 THEN 'TABLE'
      WHEN RDB$DEPENDENT_TYPE = 1 THEN 'VIEW'
      WHEN RDB$DEPENDENT_TYPE = 2 THEN 'TRIGGER'
      WHEN RDB$DEPENDENT_TYPE = 3 THEN 'COMPUTED'
      WHEN RDB$DEPENDENT_TYPE = 4 THEN 'VALIDATION'
      WHEN RDB$DEPENDENT_TYPE = 5 THEN 'PROCEDURE'
      WHEN RDB$DEPENDENT_TYPE = 6 THEN 'EXPRESSION_INDEX'
      WHEN RDB$DEPENDENT_TYPE = 7 THEN 'EXCEPTION'
      WHEN RDB$DEPENDENT_TYPE = 8 THEN 'USER'
      WHEN RDB$DEPENDENT_TYPE = 9 THEN 'FIELD'
      WHEN RDB$DEPENDENT_TYPE = 10 THEN 'INDEX'
      WHEN RDB$DEPENDENT_TYPE = 11 THEN 'GENERATOR'
      WHEN RDB$DEPENDENT_TYPE = 14 THEN 'UDF'
      ELSE 'UNKNOWN'
      END AS DEPENDENT_TYPE_STR,
    RDB$DEPENDED_ON_TYPE AS DEPEND_ON_TYPE,
    CASE
      WHEN RDB$DEPENDED_ON_TYPE = 0 THEN 'TABLE'
      WHEN RDB$DEPENDED_ON_TYPE = 1 THEN 'VIEW'
      WHEN RDB$DEPENDED_ON_TYPE = 2 THEN 'TRIGGER'
      WHEN RDB$DEPENDED_ON_TYPE = 3 THEN 'COMPUTED'
      WHEN RDB$DEPENDED_ON_TYPE = 4 THEN 'VALIDATION'
      WHEN RDB$DEPENDED_ON_TYPE = 5 THEN 'PROCEDURE'
      WHEN RDB$DEPENDED_ON_TYPE = 6 THEN 'EXPRESSION_INDEX'
      WHEN RDB$DEPENDED_ON_TYPE = 7 THEN 'EXCEPTION'
      WHEN RDB$DEPENDED_ON_TYPE = 8 THEN 'USER'
      WHEN RDB$DEPENDED_ON_TYPE = 9 THEN 'FIELD'
      WHEN RDB$DEPENDED_ON_TYPE = 10 THEN 'INDEX'
      WHEN RDB$DEPENDED_ON_TYPE = 11 THEN 'GENERATOR'
      WHEN RDB$DEPENDED_ON_TYPE = 14 THEN 'UDF'
      ELSE 'UNKNOWN'
      END AS DEPENDS_ON_TYPE_STR
FROM RDB$DEPENDENCIES
ORDER BY RDB$DEPENDENT_NAME
Version for FireBird
SELECT RDB$DEPENDENT_NAME AS DEPENDENT_NAME,
  RDB$DEPENDED_ON_NAME AS DEPENDS_ON,
  RDB$FIELD_NAME AS FIELD_NAME,
  RDB$DEPENDENT_TYPE DEPENDENT_TYPE,
    CASE
      WHEN RDB$DEPENDENT_TYPE = 0 THEN 'TABLE'
      WHEN RDB$DEPENDENT_TYPE = 1 THEN 'VIEW'
      WHEN RDB$DEPENDENT_TYPE = 2 THEN 'TRIGGER'
      WHEN RDB$DEPENDENT_TYPE = 3 THEN 'COMPUTED'
      WHEN RDB$DEPENDENT_TYPE = 4 THEN 'VALIDATION'
      WHEN RDB$DEPENDENT_TYPE = 5 THEN 'PROCEDURE'
      WHEN RDB$DEPENDENT_TYPE = 6 THEN 'EXPRESSION_INDEX'
      WHEN RDB$DEPENDENT_TYPE = 7 THEN 'EXCEPTION'
      WHEN RDB$DEPENDENT_TYPE = 8 THEN 'USER'
      WHEN RDB$DEPENDENT_TYPE = 9 THEN 'FIELD'
      WHEN RDB$DEPENDENT_TYPE = 10 THEN 'INDEX'
      WHEN RDB$DEPENDENT_TYPE = 14 THEN 'GENERATOR'
      WHEN RDB$DEPENDENT_TYPE = 15 THEN 'UDF'
      ELSE 'UNKNOWN'
      END AS DEPENDENT_TYPE_STR,
    RDB$DEPENDED_ON_TYPE AS DEPEND_ON_TYPE,
    CASE
      WHEN RDB$DEPENDED_ON_TYPE = 0 THEN 'TABLE'
      WHEN RDB$DEPENDED_ON_TYPE = 1 THEN 'VIEW'
      WHEN RDB$DEPENDED_ON_TYPE = 2 THEN 'TRIGGER'
      WHEN RDB$DEPENDED_ON_TYPE = 3 THEN 'COMPUTED'
      WHEN RDB$DEPENDED_ON_TYPE = 4 THEN 'VALIDATION'
      WHEN RDB$DEPENDED_ON_TYPE = 5 THEN 'PROCEDURE'
      WHEN RDB$DEPENDED_ON_TYPE = 6 THEN 'EXPRESSION_INDEX'
      WHEN RDB$DEPENDED_ON_TYPE = 7 THEN 'EXCEPTION'
      WHEN RDB$DEPENDED_ON_TYPE = 8 THEN 'USER'
      WHEN RDB$DEPENDED_ON_TYPE = 9 THEN 'FIELD'
      WHEN RDB$DEPENDED_ON_TYPE = 10 THEN 'INDEX'
      WHEN RDB$DEPENDED_ON_TYPE = 14 THEN 'GENERATOR'
      WHEN RDB$DEPENDED_ON_TYPE = 15 THEN 'UDF'
      ELSE 'UNKNOWN'
      END AS DEPENDS_ON_TYPE_STR
FROM RDB$DEPENDENCIES
ORDER BY RDB$DEPENDENT_NAME

You see differences in types from 10 on:
- in InterBase 11 is GENERATOR - in FireBird 11 not used
- in InterBase 14 is UDF - in FireBird 14 is GENERATOR
- in InterBase 15 not used - in FireBird 15 is UDF   
nach oben • go top • vers le hautnächste Seite • next page • prochain site