Creating an External-Language Routine

You can write a routine in an external language that the database server supports. After you create a routine, you register the routine with a CREATE FUNCTION or CREATE PROCEDURE statement.

The CREATE FUNCTION and CREATE PROCEDURE statements specify the location of the external routine, as follows:

• For C UDRs, the location is the full pathname of the shared-object module, qualified with the name of the C function that implements the function or procedure.
• For Java UDRs, location is the name of the .jar file, followed by the name of the Java class and the name of the method within that class, including its arguments.

For example, Figure 5 shows a CREATE FUNCTION statement that registers a user-defined function called abs_eq() that is written in C. The corresponding C function is in a shared-object file called abs.bld.

Figure 5. Registering an External-Language Function

Registering a Routine Written in C

To register a C routine, write the body of the routine, compile it, and create a shared-object file, and then use the CREATE FUNCTION or CREATE PROCEDURE statement to register the function. The RETURNING clause of CREATE FUNCTION specifies the return data type of the function.

For example, the following CREATE FUNCTION statement registers a C function called equal() that takes two arguments, arg1 and arg2, of data type udtype1 and returns a single value of the data type BOOLEAN:

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

For more information, see the CREATE FUNCTION and CREATE PROCEDURE statements in the IBM Informix: Guide to SQL Syntax. For information about how to create a shared-object file, refer to the IBM Informix: DataBlade API Programmer's Guide.

Registering a Routine Written in Java

To register a Java routine, write the body of the routine, compile it, create a .jar file, and register the .jar file with install_jar(). Then use the CREATE FUNCTION or CREATE PROCEDURE statement to register the function. For example:

CREATE PROCEDURE showusers()
   WITH (class='jvp')
   EXTERNAL NAME 'thisjar:admin.showusers()'
   LANGUAGE java;

A UDR written in Java runs on a JVP by default. Therefore, the CLASS routine modifier in the preceding example is optional. However, Informix recommends that, to improve readability of your SQL statements, you include the CLASS routine modifier when you register a UDR.

For more information, see the CREATE FUNCTION and CREATE PROCEDURE statements in the IBM Informix: Guide to SQL Syntax. For information about how to create a Java routine, refer to the IBM Informix: J/Foundation Developer's Guide.

Registering an External Routine with Modifiers

When you create a routine in an external language, you can specify optional modifiers that tell the database server about attributes of the UDR. Use the WITH clause of the CREATE FUNCTION and CREATE PROCEDURE statements to list routine modifiers. Following the WITH keyword, the modifiers that you want to specify are enclosed within parentheses and separated by commas.

For more information about using routine modifiers, refer to the IBM Informix: DataBlade API Programmer's Guide.

Modifiers in a C UDR

The following table shows the routine modifiers that are valid for C routines.

The following example shows how to use the WITH clause to specify a set of modifiers when you create an external-language function:

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

In this example, the HANDLESNULLS modifier indicates that the basetype2_lessthan() function (in the shared library /usr/lib/basetype2/lib/libbtype2.so) is coded to recognize SQL null. If HANDLESNULL is not set, the routine manager does not execute the UDR if any arguments of the routine are null; it simply returns null.

Modifiers in a Java UDR

The following table shows the routine modifiers that are valid for Java routines.

Routine Modifier

Type of UDR

CLASS

Access to JVP

HANDLESNULLS

UDR that handles SQL null values as arguments

ITERATOR

Iterator function

NEGATOR

Negator function

NOT VARIANT

All invocations of the UDR with the same arguments return the same value

PARALLELIZABLE

Parallelizable UDR

VARIANT

All invocations of the UDR with the same arguments do not necessarily return the same value

Registering Parameters and a Return Value

The CREATE FUNCTION and CREATE PROCEDURE statements specify any parameters and return value for a C UDR. These statements use SQL data types for parameters and the return value. For example, suppose a C UDR has the following C declaration:

mi_double_precision *func1(parm1, parm2)
   mi_integer parm1;
   mi_double_precision *parm2;

The following CREATE FUNCTION statement registers the func1() user-defined function:

CREATE FUNCTION func1(INTEGER, FLOAT)
RETURNS FLOAT

Use the opaque SQL data type, POINTER, to specify a data type for an external-language routine whose parameter or return type has no equivalent SQL data type. The CREATE FUNCTION or CREATE PROCEDURE statement uses the POINTER data type when the data structure that the routine receives or returns is a private data type, not one that is available to users.