Informix DataBlade API Programmer's Manual Developing a User-Defined Routine

The CREATE FUNCTION and CREATE PROCEDURE statements register user-defined functions and user-defined procedures, respectively, in the database. These functions store information about the UDR in the sysprocedures system catalog table.

Tip: The Informix BladeSmith development tool automatically generates scripts that contain the SQL registration statements for the database objects it helps you develop. For more information, see the "DataBlade Developers Kit User's Guide."

Registration for a C UDR requires the following special clauses of the CREATE FUNCTION and CREATE PROCEDURE statements to help the database server identify the routine:

• The required EXTERNAL NAME clause specifies the path to the shared-object file that contains the compiled C code for the UDR.
• The required LANGUAGE clause specifies the language in which the body of the UDR is written.
• The optional WITH clause specifies the routine modifiers for the UDR.

For example, Figure 11-1 shows a CREATE FUNCTION statement that registers a user-defined function called abs_eq() whose corresponding C function is in a shared-object file called abs.so.

Figure 11-1 Registering a C UDR

Tip: The Informix BladeSmith development tool, which is part of the DataBlade Developers Kit, automatically generates a file with the SQL statements needed to register a DataBlade. The BladeManager development tool can install a DataBlade module. For more information, see the "DataBlade Developers Kit User's Guide."

The External Name

The EXTERNAL NAME clause of the CREATE FUNCTION or CREATE PROCEDURE statement tells the database server where to find the object code for the UDR. These statements store this location in the externalname column of the sysprocedures system catalog table. When the database server executes an SQL statement that contains a UDR, it loads into memory the shared-object file that contains its executable code. The database server examines the externalname column to determine which shared-object file to load.

In Figure 11-1 on page 11-20, the EXTERNAL NAME clause of this CREATE FUNCTION statement tells the database server that the object code for the abs_eq() user-defined function is in a Solaris shared-object file called abs.so, which resides in the /usr/code directory.

By default, the database server assumes that the entry point into the shared-object file for the UDR object code has the same name as the user-defined routine. Therefore, the CREATE FUNCTION statement in Figure 11-1 on page 11-20 specifies that the entry point in the abs.so shared-object file for the abs_eq() user-defined function is a C function called abs_eq().

To specify an entry point whose name is different from the name of the user-defined routine, put the name of the actual entry point in parentheses after the name of the shared-object file. You must specify an entry point when your UDR has a different name from the user-defined routine that it implements. The following CREATE FUNCTION statement specifies that the entry point for the object code of the abs_eq() UDR is a C function called abs_equal():

CREATE FUNCTION abs_eq(INTEGER, INTEGER)

	RETURNS boolean 

	EXTERNAL NAME '/usr/code/abs.so(abs_equal)'

	LANGUAGE C

The database server invokes the C function abs_equal() whenever an SQL statement calls the abs_eq() function with two arguments of INTEGER data type.

You can include environment variables in the external-name specification of the EXTERNAL NAME clause. This environment variable must be set in the server environment; that is, it must be set before the database server starts. For example, the following function registration specifies to evaluate the USERFUNCDIR environment variable when determining the location of the my_func() user-defined function:

CREATE FUNCTION my_func(arg INTEGER)
RETURNING FLOAT
EXTERNAL NAME "$USERFUNCDIR/funcs.udr"
LANGUAGE C

For more information about the EXTERNAL NAME clause, see the External Reference segment of the Informix Guide to SQL: Syntax.

The LANGUAGE clause of the CREATE FUNCTION or CREATE PROCEDURE statement tells the database server in which language the UDR is written. For C UDRs, the LANGUAGE clause must be as follows:

LANGUAGE C

The database server stores valid UDR languages in the sysroutinelangs system catalog table. These statements store the UDR language as an integer, called a language identifier, in the langid column of the sysprocedures system catalog table.

By default, only users with DBA privilege have the Usage privilege on the C language for UDRs. These users include user informix and the user who created the database. If you attempt to execute the CREATE FUNCTION or CREATE PROCEDURE statement with the LANGUAGE C clause as some other user, the database server generates an error.

To allow other users to register C UDRs in the database, a user with the DBA privilege can grant the Usage privilege on the C language with the GRANT statement. The following GRANT statement allows any user to register C UDRs:

GRANT USAGE ON LANGUAGE C TO public

For more information on the syntax of the GRANT statement, see the Informix Guide to SQL: Syntax.

The routine modifiers tell the database server about attributes of the UDR. You specify routine modifiers in the WITH clause of the CREATE FUNCTION or CREATE PROCEDURE statement. The database server supports routine modifiers for C UDRs to perform the following tasks.

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. These SQL data types must be compatible with the DataBlade API data types in the routine declaration. Figure 1-1 on page 1-11 lists the SQL data types that correspond to the different DataBlade API data types.

For example, suppose you have a C UDR with 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 a C UDR whose parameter or return type has no SQL data-type equivalent. For example, suppose you have a C UDR that has the following C declaration:

my_private_struc *func2(parm1, parm2)
	mi_integer parm1, parm2;

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

CREATE FUNCTION func2(INTEGER, INTEGER)
RETURNS POINTER

This CREATE FUNCTION statement uses the POINTER data type because the data structure to which func2() returns a pointer is a private data type, not one that is surfaced to users by registering it in the database.

Tip: If the C implementation of your UDR requires an MI_FPARAM structure in its declaration, omit this structure from the parameter list of the CREATE FUNCTION or CREATE PROCEDURE statement. For more information about when a C UDR requires an MI_FPARAM structure, see For the MI_FPARAM Argument.

For more information about how to declare a C UDR, see Coding the User-Defined Routine.