5.13. FILTER

A BLOB FILTER is a database object that is a special type of external function, with the sole purpose of taking a BLOB object in one format and converting it to a BLOB object in another format. The formats of the BLOB objects are specified with user-defined BLOB subtypes.

External functions for converting BLOB types are stored in dynamic libraries and loaded when necessary.

For more details on BLOB subtypes, see Binary Data Types.

5.13.1. DECLARE FILTER

Declares a BLOB filter in the current database

Available inDSQL, ESQL

Syntax 

   |DECLARE FILTER filtername
   |  INPUT_TYPE <sub_type> OUTPUT_TYPE <sub_type>
   |  ENTRY_POINT 'function_name' MODULE_NAME 'library_name'
   | 
   |<sub_type> ::= number | <mnemonic>
   | 
   |<mnemonic> ::=
   |    BINARY | TEXT | BLR | ACL | RANGES
   |  | SUMMARY | FORMAT | TRANSACTION_DESCRIPTION
   |  | EXTERNAL_FILE_DESCRIPTION | user_defined

Table 5.34. DECLARE FILTER Statement Parameters
ParameterDescription

filtername

Filter name in the database. The maximum length is 63 characters. It need not be the same name as the name exported from the filter library via ENTRY_POINT.

sub_type

BLOB subtype

number

BLOB subtype number (must be negative)

mnemonic

BLOB subtype mnemonic name

function_name

The exported name (entry point) of the function

library_name

The name of the module where the filter is located

user_defined

User-defined BLOB subtype mnemonic name

The DECLARE FILTER statement makes a BLOB filter available to the database. The name of the BLOB filter must be unique among the names of BLOB filters.

5.13.1.1. Specifying the Subtypes

The subtypes can be specified as the subtype number or as the subtype mnemonic name. Custom subtypes must be represented by negative numbers (from -1 to -32,768), or their user-defined name from the RDB$TYPES table. An attempt to declare more than one BLOB filter with the same combination of the input and output types will fail with an error.

INPUT_TYPE

clause defining the BLOB subtype of the object to be converted

OUTPUT_TYPE

clause defining the BLOB subtype of the object to be created.

ⓘ
Note

Mnemonic names can be defined for custom BLOB subtypes and inserted manually into the system table RDB$TYPES system table:

  |INSERT INTO RDB$TYPES (RDB$FIELD_NAME, RDB$TYPE, RDB$TYPE_NAME)
  |VALUES ('RDB$FIELD_SUB_TYPE', -33, 'MIDI');

After the transaction is committed, the mnemonic names can be used in declarations when you create new filters.

The value of the column RDB$FIELD_NAME must always be 'RDB$FIELD_SUB_TYPE'. If a mnemonic names was defined in upper case, they can be used case-insensitively and without quotation marks when a filter is declared, following the rules for other object names.

WarningIn general, the system tables are not writable by users. However, inserting custom types into RDB$TYPES is still possible if the user is an administrator, or has the system privilege CREATE_USER_TYPES.

5.13.1.2. Parameters

ENTRY_POINT

clause defining the name of the entry point (the name of the imported function) in the module.

MODULE_NAME

The clause defining the name of the module where the exported function is located. By default, modules must be located in the UDF folder of the root directory on the server. The UDFAccess parameter in firebird.conf allows editing of access restrictions to filter libraries.

Any user connected to the database can declare a BLOB filter.

5.13.1.3. Who Can Create a BLOB Filter?

The DECLARE FILTER statement can be executed by:

The user executing the DECLARE FILTER statement becomes the owner of the filter.

5.13.1.4. Examples of DECLARE FILTER

  1. Creating a BLOB filter using subtype numbers.

      |DECLARE FILTER DESC_FILTER
  |  INPUT_TYPE 1
  |  OUTPUT_TYPE -4
  |  ENTRY_POINT 'desc_filter'
  |  MODULE_NAME 'FILTERLIB';

  2. Creating a BLOB filter using subtype mnemonic names.

      |DECLARE FILTER FUNNEL
  |  INPUT_TYPE blr OUTPUT_TYPE text
  |  ENTRY_POINT 'blr2asc' MODULE_NAME 'myfilterlib';

See alsoSection 5.13.2, “DROP FILTER”

5.13.2. DROP FILTER

Drops a BLOB filter declaration from the current database

Available inDSQL, ESQL

Syntax 

  |DROP FILTER filtername

Table 5.35. DROP FILTER Statement Parameter
ParameterDescription

filtername

Filter name in the database

The DROP FILTER statement removes the declaration of a BLOB filter from the database. Removing a BLOB filter from a database makes it unavailable for use from that database. The dynamic library where the conversion function is located remains intact and the removal from one database does not affect other databases in which the same BLOB filter is still declared.

5.13.2.1. Who Can Drop a BLOB Filter?

The DROP FILTER statement can be executed by:

  • Administrators

  • The owner of the filter

  • Users with the DROP ANY FILTER privilege

5.13.2.2. DROP FILTER Example

Dropping a BLOB filter. 

  |DROP FILTER DESC_FILTER;

See alsoSection 5.13.1, “DECLARE FILTER”