Registering a user-defined routine

About this task

A database server administrator (DBSA), the user informix by default, can implement security measures that establish which users can register external routines. This prevents unauthorized users from registering the external routines.

To grant a user privileges to create or drop a UDR that has the EXTERNAL clause, the DBSA must set the IFX_EXTEND_ROLE configuration parameter to On. When this parameter is set to On, the EXTEND role is operational and the DBSA can grant a user privileges to create or drop a external routine that has the EXTERNAL clause.

After setting the IFX_EXTEND_ROLE configuration parameter to On, the DBSA uses the following syntax to grant and revoke privileges to and from specific users.
  • GRANT extend To username
  • REVOKE extend From username

If the IFX_EXTEND_ROLE configuration parameter is set to Off, the EXTEND role is not operational and any user can register external routines.

For more information about the EXTEND role, see the HCL OneDB™ Administrator's Guide and the HCL OneDB Guide to SQL: Syntax.

If you have privileges to register a UDR or if the IFX_EXTEND_ROLE configuration parameter is set to Off and you are responsible for registering UDRs, you must register UDRs in all databases in which they will be used, unless the database is on the secondary database server of an HDR pair.

The database server recognizes the following SQL statements for the registration of UDRs in the database:
  • The CREATE FUNCTION statement registers UDRs that return a value.
  • The CREATE PROCEDURE statement registers UDRs that do not return a value.

To register a user-defined routine:

Procedure

  1. Ensure that you have the correct privileges to register a UDR.
  2. Use a CREATE FUNCTION or CREATE PROCEDURE statement to register the UDR:
    • For SPL routines, the statement lists the routine code and then compiles and registers the routine.
    • For external-language routines, the statement specifies the location of the routine code (with an EXTERNAL NAME clause) and registers the routine.

Example

The following example shows the syntax of a CREATE FUNCTION statement: 
CREATE FUNCTION func_name(parameter_list) RETURNS ret_type
   WITH (NOT VARIANT)
   EXTERNAL NAME 'pathname'
   LANGUAGE C
This SQL statement provides the following information to the database:
  • The name, func_name, and owner of the support function
  • An optional specific name for the support function (not shown)
  • The data types of the parameters, parameter_list, and return value, ret_type, of the support function
  • The location, pathname, of the source code for the support function
  • The language of the support function: LANGUAGE C.
  • The routine modifier NOT VARIANT that indicates that the function does not return different results with different arguments.

You cannot use the CREATE FUNCTION directly in the program. To register an opaque-type support function from within the application, you must put the CREATE FUNCTION statement in an operating-system file. Then use the CREATE FUNCTION FROM statement to identify the location of this file. The CREATE FUNCTION FROM statement sends the contents of the operating-system file to the database server for execution.