FirebirdSQL logo

Constants

The resulting Firebird.pas file is missing isc_* constants. Theseconstants for C/C++ languages can be found underhttps://github.com/FirebirdSQL/firebird/blob/B3_0_Release/src/include/consts_pub.h.To obtain constants for the Pascal language, we use the AWK script forsyntax transformations. On Windows you will need to install Gawk forWindows or use the Windows Subsystem for Linux (available atWindows 10). This is done with the following command:

awk -f Pascal.Constants.awk consts_pub.h > const.pas

TThe contents of the resulting file must be copied into the empty constsection of the Firebird.pas file immediately after implementation.The file Pascal.Constants.awk can be found athttps://github.com/FirebirdSQL/firebird/tree/B3_0_Release/src/misc/pascal.

Life time management

Firebird interfaces are not based on the COM specification, sotheir lifetime is managed differently.

There are two interfaces in Firebird that deal with lifetime management:IDisposable and IReferenceCounted. The latter is especially active whencreating other interfaces: IPlugin counts links, like many other interfacesused by plug-ins. These include interfaces that describe the databaseconnection, transaction management, and SQL statements.

You don’t always need the extra overhead of a reference-counted interface. Forexample, IMaster, the main interface that calls functions available to therest of the API, has an unlimited lifetime by definition. For other APIs, thelifetime is strictly determined by the lifetime of the parent interface;interface ISatus is notmultithreaded. For interfaces with limited lifetimes, it is useful to have aneasy way to destroy them, i.e. the dispose() function.

Tip
Clue

If you don’t know how an object is destroyed, look up its hierarchy if it hasthe IReferenceCounted interface. For reference-counted interfaces, uponcompletion of work with the object, it is necessary to decrement the referencecount by calling the release() method.

Example 1. Important

Some methods of interfaces derived from IReferenceCounted release the interfaceafter successful completion. There is no need to call release() after calling such methods.

This is done for historical reasons, because similar functions from the ISC API freed the corresponding handle.

Here is a list of such methods:

  • IAttachment interface

    • detach(status: IStatus) - disconnect the connection to the database. On success, releases the interface.

    • dropDatabase(status: IStatus) - drop database. On success, releases the interface.

  • Interface ITransaction

    • commit(status: IStatus) - transaction confirmation. On success, releases the interface.

    • rollback(status: IStatus) - transaction rollback. On success, releases the interface.

  • IStation interface

    • free(status: IStatus) - removes a prepared statement. On success, releases the interface.

  • IResultSet interface

    • close(status: IStatus) closes the cursor. On success, releases the interface.

  • IBlob interface

    • cancel(status: IStatus) - cancels all changes made to the temporary BLOB (if any) and closes the BLOB. On success, releases the interface.

    • close(status: IStatus) - saves all changes made to the temporary BLOB (if any) and closes the BLOB. On success, releases the interface.

  • Interface IService

    • detach(status: IStatus) - disconnect the connection with the service manager. On success, releases the interface.

  • IEvents interface

    • cancel(status: IStatus) - cancels event subscription. On success, releases the interface.

UDR announcements

UDRs can be added to or removed from the database using DDL commands, much like you add or remove normal PSQL procedures, functions, or triggers. In this case, instead of the body of the trigger, its location in the external module is specified using the EXTERNAL NAME clause.

Consider the syntax of this sentence, it will be common to external procedures, functions and triggers.

Syntax
EXTERNAL NAME '<extname>' ENGINE <engine>
[AS <extbody>]

<extname> ::= '<module name>!<routine name>[!<misc info>]'

The argument to this EXTERNAL NAME clause is a string indicating the location of the function in the external module. For plug-ins using the UDR engine, this line contains the name of the plug-in, the name of the function inside the plug-in, and user-defined information separated by a delimiter. An exclamation point is used as a separator(!).

The ENGINE clause specifies the name of the engine to handle the connectionexternal modules. In Firebird, to work with external modules written incompiled languages (C, C++, Pascal) use the UDR engine.External functions written in Java require the Java engine.

After the AS keyword, a string literal can be specified - the "body" of the external module (procedure, function or trigger), it can be used by the external module for various purposes. For example, an SQL query may be specified to access an external database, or text in some language for interpretation by your function.

External functions

Syntax
{CREATE [OR ALTER] | RECREATE} FUNCTION funcname [(<inparam> [, <inparam> ...])]
RETURNS <type> [COLLATE collation] [DETERMINISTIC]
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]


<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<value> ::=  {literal | NULL | context_var}

<param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation]

<extname> ::= '<module name>!<routine name>[!<misc info>]'

<type> ::= <datatype> | [TYPE OF] domain | TYPE OF COLUMN rel.col

<datatype> ::=
    {SMALLINT | INT[EGER] | BIGINT}
  | BOOLEAN
  | {FLOAT | DOUBLE PRECISION}
  | {DATE | TIME | TIMESTAMP}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [CHARACTER SET charset]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(size)]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset]
  | BLOB [(seglen [, subtype_num])]

All parameters of an external function can be changed using the ALTER statementFUNCTION.

Syntax
ALTER FUNCTION funcname [(<inparam> [, <inparam> ...])]
RETURNS <type> [COLLATE collation] [DETERMINISTIC]
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]

<extname> ::= '<module name>!<routine name>[!<misc info>]'

You can remove an external function using the DROP FUNCTION statement.

Syntax
DROP FUNCTION funcname
Table 1. Some parameters of the external function
Parameter Description

funcname

Name of the stored function. Can contain up to 31 bytes.

inparam

Description of the input parameter.

module name

Name of the external module where the function resides.

routine name

The internal name of the function inside the external module.

misc info

User-defined information to pass to the functionexternal module.

engine

Name of the engine to use external functions. Usuallyspecifies the name of the UDR.

extbody

External function body. A string literal that canbe used by UDR for various purposes.

Here we will not describe the syntax of the input parameters and the outputresult. It fully corresponds to the syntax for regular PSQL functions, whichis described in detail in the SQL Language Manual. Instead, we give examplesof declaring external functions with explanations.

create function sum_args (
    n1 integer,
    n2 integer,
    n3 integer
)
returns integer
external name 'udrcpp_example!sum_args'
engine udr;

The implementation of the function is in the udrcpp_example module. Within this module, the function is registered under the name sum_args. The UDR engine is used to operate the external function.

create or alter function regex_replace (
  regex varchar(60),
  str varchar(60),
  replacement varchar(60)
)
returns varchar(60)
external name 'org.firebirdsql.fbjava.examples.fbjava_example.FbRegex.replace(
      String, String, String)'
engine java;

The implementation of the function is in the udrcpp_example module. Withinthis module, the function is registered under the name sum_args. The UDRengine is used to operate the external function.