Function Calls - Firebird

Out of the box, Firebird comes with a minimal set of internally implemented SQL functions. Although new functions get implemented from time to time, the broad view of the user community is to retain one of Firebird’s main benefits: the small footprint of the server.

The server’s functional capabilities can be extended simply, by its ability to access functions in externally implemented libraries. Traditionally, such functions were called user-defined functions (UDFs). Correctly, they are external function libraries. In reality, most DBAs use well-tested libraries that are in common use and freely distributed.

Internal SQL Functions

Table lists the SQL internal functions available in Firebird.

Internally Implemented SQL Functions

Internally Implemented SQL Functions

Conversion Functions

Conversion functions transform data types, for example, by converting them from one type to another compatible type, changing the scale or precision of numeric values, or by distilling some inherent attribute from a data item. Many string functions can be said to be conversion functions, too, because they transform the way stored string values are represented in output.

CAST( )

CAST( ) is a wide-ranging function that allows a data item of one type to be converted to or treated as another type.

Availability

DSQL, PSQL, ISQL, ESQL. Any platform.

Syntax

Arguments

Value is a column or expression that evaluates to a data type that can validly be converted to the data type named by the AS keyword.

<data-type> must be a native Firebird data type. A domain is not valid.

Return Value

The function returns a computed field of the designated data type.

Example

In the following PSQL snippet, a TIMESTAMP field, LOG_DATE, is cast to a DATE type because a calculation needs to be performed on whole days:

The following statement takes the value of an integer column, casts it as a string, and concatenates it to a CHAR(3) column to form the value for another column:

Related Material

Refer to Chapter About Firebird Data Types for a more detailed discussion of CAST( ) and to the succeeding chapters that deal with each data type individually.

EXTRACT( )

This function extracts a part of a DATE, TIME, or TIMESTAMP field as a number.

Availability

DSQL, PSQL, ISQL, ESQL. Any platform.

Syntax

Arguments

Part is one member of the preceding optional keywords set. WEEKDAY extracts the day of the week (having Sunday = 1, Monday = 2, and so on), and YEARDAY extracts the day of the year (from January 1 = 1, to 366).

Field is a valid DATE, TIME, or TIMESTAMP field (column, variable, or expression).

Return Value

All parts return SMALLINT except SECOND, which is DECIMAL(6,4).

EXTRACT will work only with values that evaluate as date-time fields.

Example

This statement returns names and birthdays, in BIRTHDAY order, of all members who have their birthdays in the current month:

String Functions

Firebird has only two internal string functions. A large variety of string functions is available in external functions (see the next section in this chapter).

SUBSTRING( )

SUBSTRING( ) is an internal function implementing the ANSI SQL SUBSTRING() function. It will return a stream consisting of the byte at startpos and all subsequent bytes up to the end of the value string. If the optional FOR length clause is specified, it will return the lesser of length bytes or the number of bytes up to the end of the input stream.

Availability

DSQL, PSQL, ISQL, ESQL. Any platform.

Syntax

SUBSTRING(value FROM startpos [FOR length])

Optional Keywords

FOR precedes an optional clause specifying the length of the substring to return.

Arguments

Value can be any expression, constant, or column identifier that evaluates to a string.

Pos must evaluate to an integer >= 1. It cannot be a replaceable parameter.

Length must evaluate to an integer >= 1. It cannot be a replaceable parameter.

Return Value

The return value is a string.

The values pos and length are byte positions, which matters for multi-byte character sets.

For a string argument, the function will handle any character set. The calling statement is responsible for handling any issues arising with multi-byte character sets.

For BLOB column arguments, the column named can be a binary BLOB (SUB_TYPE 0) or a text BLOB (SUB_TYPE 1) with an underlying 1-byte-per-character character set. The function currently does not handle text BLOBs with Chinese (2-byte-per-character maximum) or Unicode (3-byte-per-character maximum) character sets.

Example

The following statement will update the value in COLUMNB to be a string of up to 99 characters, starting at the fourth position of the original string:

Related or Similar Functions

See also the external functions SUBSTR( ), SUBSTRLEN( ), and RTRIM( ).

UPPER( )

Converts a string to all uppercase characters.

Availability

DSQL, PSQL, ISQL, ESQL, provided the string is of a character set or collation sequence that supports a lowercase -to -uppercase conversion. Any platform.

Syntax

UPPER(value)

Arguments

Value is a column, variable, or expression that evaluates to a character type.

Return Value

If the character set or collation sequence supports uppercasing, it returns a string of converted, all-uppercase characters of the same length as the input value. For unsupported character sets, it returns the input value unchanged.

The input value cannot be a BLOB type.

Example

The following CHECK constraint validates a string input by testing whether it consists of all uppercase characters:

Related or Similar Functions

See also the external functions LOWER( ) and F_PROPERCASE( ).

Function for Getting a Generator Value

The GEN_ID( ) function is the mechanism by which PSQL modules and applications draw numbers from generators. Generators are discussed in detail in Chapter Number Types. See also “Implementing Auto-incrementing Keys” in Chapter Triggers.

GEN_ID( )

GEN_ID( ) calculates and returns a value from a generator.

Availability

DSQL, PSQL, ISQL, ESQL. Any platform.

Syntax

GEN_ID(value1, value2)

Arguments

value1 is the identifier of an existing generator.

value2 is the stepping value, an integer type, or an expression that evaluates to an integer type.

Return Value

It returns a BIGINT.

Normally, the stepping value is 1. A stepping value of 0 will return the last value generated. Larger steps as well as negative ones are possible. However, you should avoid negative stepping unless you really mean to break the forward sequence.

GEN_ID( always executes outside of all transactions. It is the only user-accessible operation in Firebird that can do so. Once a number has been acquired from a generator, it can never be generated again from the same generator, unless a user intervenes and breaks the sequence using a negative step or a SET GENERATOR statement.

Example

The following statement returns a new value from a generator named GEN_SERIAL:

SELECT GEN_ID(GEN_SERIAL, 1) FROM RDB$DATABASE;

In the next example, a generator is used in a BEFORE INSERT trigger to populate a primary key:

Aggrgating Functions

Aggregating functions perform calculations over a column of values, such as the values selected in a numeric column from a queried set. Firebird has a group of aggregating functions that are most typically used in combination with grouping conditions to calculate group-level totals and statistics. The aggregating functions are SUM( ), which calculates totals; MAX( ) and MIN( ), which return the highest and lowest values, respectively; and AVG( ), which calculates averages. The COUNT( ) function also behaves as an aggregating function in grouped queries.

Chapter Ordered and Aggregated Sets looks more closely at the participation of aggregating functions in grouped queries.

“Non-grouped”Aggregations

In a few situations, aggregating functions can operate on sets that are not subject to GROUP BY, returning at most a single row. Logically, the result of such a query cannot output any database column values or values derived from non-aggregating functions. To illustrate, the following query aggregates the budgets for one project for one fiscal year. The table has one budget record for each of five departments:

The output is a single line: the runtime string field and the calculated total.

External Functions (UDFs)

External functions are code routines written in a host language such as C, C++, or Pascal and compiled as shared binary libraries—DLLs on Windows and shared objects on other platforms that support dynamic loading. Like the standard, built-in SQL functions, external functions can be designed to do conversions or calculations that are either too complex or impossible to do with the SQL language.

You can access external functions by using them in expressions, just as you use a built-in SQL function. Like the internal functions, they can also return values to variables or SQL expressions in stored procedure and trigger bodies.

The “user-defined” part comes in because you can write your own functions. The possibilities for creating custom functions for your Firebird server are limited only by your imagination and skill as a host-language programmer. Possibilities include statistical,string, date, and mathematical functions; data-formatting routines; or even a custom engine for processing regular expressions (regexes).

External functions should not be designed to make their own connections to databases. Like the internal functions, they must operate on data, in expressions, in the context of the current server process and transaction and a single statement. Because external functions can take only arguments that are (or can be resolved as) native Firebird data types, they cannot take a query set specifier as an argument. Thus, for
example, it is not possible to write aggregate functions that do not operate on constant arguments.

Existing Libraries

Firebird ships with two pre-built libraries of external functions (UDFs). The default installations place these shared object libraries in the ./UDF directory beneath the Firebird root directory. File extensions are .dll on Windows and .so on other supported platforms.

  • ib_udf: Firebird inherited this library of useful, basic functions with its InterBase ancestry. This library needs to call some memory utilities that are located in a companion shared object name ib_util, located in the ./bin directory. This library passes parameters either by value or by reference, in the conventional InterBase style. Several routines have been bug-fixed in Firebird, so do make certain you avoid versions that ship with Borland products.
  • fbudf: Created by Claudio Valderrama, this library passes parameters by Firebird descriptor ,which is considered a more robust way to ensure that internal errors do not occur as a result of memory allocation and type conversion errors.

Also freely available are several public domain UDF libraries, including FreeUDFLib, originally written in Borland Delphi by Gregory Deatz. FreeUDFLib contains a vast number of string, math, BLOB, and date functions. This library has been maintained, corrected, and augmented by several people over the years. Care should be taken to obtain a version of this library that works properly with dialect 3 date and time types. Such a version for Windows is available from C source code for a POSIX version is available as FreeUDFLibC but, at the time of this writing, no trusted binary was available for download.

Configuration and Security Issues

External code modules are inherently vulnerable to malicious intruders and careless administrators. They are, after all, just files in the filesystem.

In Firebird 1.0.x, you can—and should —configure the location(s) of external function libraries for the server explicitly, using the external _function _directory parameter in the configuration file (isc _config on POSIX servers and ibconfig on Windows). It behooves the DBA to ensure that libraries cannot be overwritten by accident or by unauthorized visitors.

From Firebird 1.5 onward, access to external files of any sort can be restricted to various levels of access. By default, if you decide to place external function libraries in non-default locations, they will be inaccessible to the server. Study the notes in Chapter Configuration and Special Features about configuring the UDFAccess parameter in firebird.conf to resolve such problems.

Stability

A bad UDF will crash the server and is capable of corrupting data. It is important to test your home -built external function routines with utter thoroughness, both outside and from within the server, before deciding to deploy them into production databases.

Declaring a Function to a Database

Once a UDF has been written, compiled, tested thoroughly , and installed into the appropriate directory on the server, it must be declared to the database in order to be used as an SQL function. To do this, use the DDL statement DECLARE EXTERNAL FUNCTION. You can declare functions using isql, another interactive SQL tool, or a script.

After the function has been declared in any database on the server, the library that contains it will be loaded dynamically at runtime the first time an application calls any function included in it. It is necessary to declare each function you want to use to each database in which it will be used.

Declaring a function to a database informs the database regarding

  • The function name as it will be used in SQL statements. You can use your own custom name in the declaration—see the following syntax notes.
  • The number and data types of its arguments.
  • The data type of the return value.
  • The name (ENTRY_POINT) of the function as it exists in the library.
  • The name of the library (MODULE_NAME) that contains the function.

This is the declaration syntax:

Table describes the arguments in detail.

Arguments for DECLARE EXTERNAL FUNCTION

Arguments for DECLARE EXTERNAL FUNCTION

Arguments for DECLARE EXTERNAL FUNCTION

External Function Library Scripts

Most external function libraries are distributed with their own DDL scripts, containing a declaration for each function and, usually, some brief documentation. The convention is to name the script after the library, using the file extension .SQL or .sql. Not all public domain libraries adhere to that convention, however.

The ib_udf.sql and fbudf.sql scripts are in the /UDF directory of your server installation. The FreeUDFLib kit comes with a script named ext_funcs.sql. You can freely copy and paste declarations to assemble your own library of favorite declarations.

This declaration is an example from the ib_udf.sql script:

The next comes from fbudf, which passes arguments by descriptor in some functions:

The Function Identifier (Name)

When declaring an external function to a database, you are not restricted to using the name that appears in the script. A function name must be unique among all function declarations in the database. The libraries in general circulation usually conform to a convention of supplying declarations that do not “step on” identifiers that are commonly in use in another library, hence the strangeness of some names that you see in the scripts.

Sometimes, you want to declare the same function more than once in your database. As long as you use different names for the EXTERNAL FUNCTION, you can declare a function having the same ENTRY _POINT and MODULE _NAME as many times as you need to.

Never alter the ENTRY_POINT argument. The MODULE_NAME argument should not be altered except where it is necessary to use the library’s full path—see “Path Names in Function Declarations.”

String Argument Sizes

The scripts contain default size declarations for external functions that take variable string arguments. For security reasons, the default sizes are kept small, to avoid the risk of accidental or malicious overflows. If string functions receive input or return results that are larger than the declared size, exceptions are thrown.

If you need an argument for a string function that is larger than the default, declare the argument according to your requirement, ensuring that the inputs and outputs are consistent with one another and no argument exceeds the maximum VARCHAR size of 32,765 bytes—note bytes, not characters.

In the following example, the function that is scripted in fbudf.sql as sright is declared with the name sright200, and the parameters are resized to permit 200 bytes:

Path Names in Function Declarations

On any platform, the module can be referenced with no path name or file extension. This is desirable if you want to be able to transport a database containing function declarations to multiple operating systems. The default configuration of Firebird 1.5 enables this.

On v.1.0.x, it will be necessary to configure the external_function_directory parameter in isc _config/ibconfig on each platform where the functions will be used, since that version has no generalized “default path” for function modules. The /UDF directory should work as a default, but experience has shown it is unreliable unless specifically configured.

For Firebird 1.5 and onward, there are other configuration (UDFAccess) options for function library locations, including RESTRICT, which lets you specify one or more directory locations for them. If you use RESTRICT, it is necessary to include the module’s full path specification, including file extension, in the declaration. In this case, the database will not be portable to another operating system unless you drop the functions and their dependencies from the database first.

This example shows a function declaration with a full path specification:


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics