CHAPTER 1: Introduction

Since 1983 Empress has provided developers a capability to extend the range of Query Language and 4GL by adding User Defined Functions (UDFs) that can greatly enhance the flexibility of database applications. User Defined Functions can be written in standard C Programming language to do sophisticated pattern matching, statistical calculations, image and sound manipulation, as well as mathematical or lexical functions. This capability was provided way before the standard was defined. Today, a higher level interface to the User Defined Functions has been defined by the ANSI Standard Committee, which is called the Persistent Stored Modules. Empress supports two interfaces for defining User Defined Functions:

1. as SQL Persistent Stored Modules (SQL/PSM) in the Data Dictionary (Dynamic link).

2. as Empress library, by defining function names in the keyword tables at the kernel level (Static link).

The usage of User Defined Functions is the same as built-in functions in Empress. They can be invoked from the Empress SQL, Empress 4GL, Empress Report Writer, Empress Hypermedia and they can also be used in C programs through expressions built with mr expression routines .

This manual describes how to extend the capabilities of Empress by adding of User Defined Functions and Procedures using both SQL/PSM and Empress library methods.

The following conventions are used through out this manual:

• User defined functions, procedures, operators and aggregate functions will be generally referred to as User Defined Functions (UDF).
• Persistent Stored Modules will be referred to as PSM.
• All Empress filenames and directories referred to in this manual are relative to the directory set by the Empress environment variable EMPRESSPATH.

Persistent Stored Modules is the second addendum to the SQL-92 ANSI/ISO standard. It is further defined in the ANSI/IEC 9075-4:1996, Information Technology - Database Languages - SQL - Part 4: Persistent Stored Modules. In addition of being a powerful application development tool, it also provides the foundation support for the object oriented capabilities in SQL3. Empress follows this stand to implement the Persistent Stored Module feature.

Functions and procedures written in C host languages as Persistent Modules can be invoked from SQL statements. New Data Definition Language (DDL) statements have been added to allow easy addition, update and drop of PSM objects.

Underneath the PSM implementation is Empress way of creating User Defined Functions, i.e., defining UDFs directly into the kernel. Although PSM is much easier and is the preferable way of creating UDFs, Empress makes kernel level interface available and documented for those users who prefer using this interface. Keeping kernel level interface is also useful for users who are interested in the internal implementation of the Persistent Stored Modules.

There are four source files with template included with Empress distribution to allow developers to:

• plug-in their functions and procedures written in C programming language into the source file, and
• define keywords to call these functions and procedures in the keyword tables.

The source files might need to be modified to include the necessary entries for defining User Defined Functions. For Empress to recognize a function, the modified source files need to be compiled, their produced object files need to be archived in the Empress library, and some executables need to be rebuilt.