Extending INFORMIX-Universal Server: User-Defined Routines

Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 3: Designing a User-Defined Routine

Home Contents Index Master Index New Book

Registering External Routines

Routines can be functions or procedures. You use different SQL statements to register each type of routine.

Registering an External Function

When you want a routine to return a value, you must create that routine as a function. To create an external function, write the body of the function in a language other than SPL, then use the CREATE FUNCTION statement to register the function.

Important: To register an external UDR that is a function, you must use the CREATE FUNCTION statement. You cannot use the CREATE PROCEDURE statement to create an external function.

The following example registers an external function of the name equal in the database server and specifies the library where the function object module is stored. The equal() function takes two arguments of the data type udtype1 and returns a BOOLEAN value.

CREATE FUNCTION equal (arg1 udtype1, arg2 udtype1)
RETURNING BOOLEAN;
EXTERNAL NAME 
"/usr/lib/udtype1/lib/libbtype1.so(udtype1_equal)"
LANGUAGE C
END FUNCTION;

When you register an external function, the END FUNCTION keywords are optional. Registration for an external routine requires two special clauses that help the database server locate the routine:

The EXTERNAL NAME clause specifies the path to the C library where the function is stored. By default, the entry point in that library is the routine name that follows the keywords CREATE FUNCTION. You can specify a different entry point with the EXTERNAL NAME clause. To specify an entry point in the EXTERNAL NAME clause, put the appropriate C module name in parentheses after the library file.

For more information about the EXTERNAL NAME clause, see the External Reference segment in Chapter 1 of the

Informix Guide to SQL: Syntax

In the preceding example, the entry point udtype1_equal() replaces the default entry point, equal(). The C routine udtype1_equal() is invoked whenever an equal() function with two arguments of udtype1 data type is called.

If the creator of the routine does not indicate an entry point in the EXTERNAL NAME clause as shown in the following example, the database server searches the C library for a routine named equal() (whose parameters match the arguments of the calling function):

EXTERNAL NAME "/usr/lib/udtype1/lib/libbtype1.so()"

The required LANGUAGE clause specifies the language in which the body of the function is written.

For more information, see the CREATE FUNCTION statement in Chapter 1 of the Informix Guide to SQL: Syntax.

Registering an External Procedure

When you do not want your routine to return a value, you must create that the routine as a procedure. To create an external procedure, write the body of the procedure in a language other than SPL, then use the CREATE PROCEDURE statement to register the procedure. The following example shows how to register an external procedure:

CREATE PROCEDURE log_compare (arg1 udtype2, 

	arg2 udtype2)
EXTERNAL NAME 
	"/usr/lib/udtype1/lib/libbtype2.so(compare_n_insert)"
LANGUAGE C
END PROCEDURE;

The EXTERNAL NAME and LANGUAGE clauses work the same way for external procedures as is explained for external functions on page 3-10.

In the preceding example, the actual body of the procedure is named compare_n_insert and is located in the C-language library /usr/lib/udtype1/lib/libbtype2.so. If the EXTERNAL NAME clause does not specify an entry point within the library, the database server invokes the module at the default entry point, log_compare().

The following example also includes the SPECIFIC keyword to create a function alias, basetype2_lessthan. Once you use the SPECIFIC keyword to create a routine alias, you can use that alias in DROP statements.

For information about the CREATE PROCEDURE statement, see the CREATE PROCEDURE statement in Chapter 1 of the Informix Guide to SQL: Syntax.

Registering an External Routine with Modifiers

When you create an external routine, you can specify optional modifiers that help optimize how the database server executes the routine. The following example shows how to use the WITH clause to specify a set of modifiers when you create an external function:

CREATE FUNCTION lessthan (arg1 basetype2, arg2 basetype2)
RETURNING BOOLEAN;
SPECIFIC basetype2_lessthan
WITH (HANDLESNULLS,
NOT VARIANT)
EXTERNAL NAME 
"/usr/lib/basetype2/lib/libbtype2.so(basetype2_lessthan)"
LANGUAGE C
END FUNCTION;

Following the WITH keyword, the modifiers that you want to specify are enclosed within parentheses and separated by commas. The handlesnulls modifier indicates that the basetype2_lessthan() function (in the library /usr/lib/basetype1/lib/libbtype1.so) is written so that it can recognize when an SQL NULL argument is passed.

External functions support a different set of modifiers than external procedures.

Modifiers for External Functions

The database server supports the following modifiers for external functions:

CLASS
HANDLESNULLS
INTERNAL
ITERATOR
STACK
VARIANT and NOT VARIANT

For more information on the syntax of these modifiers, see the CREATE FUNCTION statement in Chapter 1 of the Informix Guide to SQL: Syntax.

Modifiers for External Procedures

The database server supports the following modifiers for external procedures:

HANDLESNULLS
CLASS
STACK
INTERNAL

For a list of the supported modifiers for external procedures, see the CREATE PROCEDURE statement in Chapter 1 of the Informix Guide to SQL: Syntax.

Extending INFORMIX-Universal Server: User-Defined RoutinesChapter 3: Designing a User-Defined Routine Home Contents Index Master Index New Book

Registering External Routines

Registering an External Function

Registering an External Procedure

Registering an External Routine with Modifiers

Modifiers for External Functions

Modifiers for External Procedures

Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 3: Designing a User-Defined Routine

Home Contents Index Master Index New Book