External Procedures

About this task

External procedures are procedures you write in an external programming language that the database server supports. (Procedures written in the SPL language are not external procedures.)

To create a C user-defined procedure

Procedure

  1. Write a C function that does not return a value.
  2. Compile the C function and store the compiled code in a shared library (the shared-object file for C).
  3. Register the C function in the database server with the CREATE PROCEDURE statement.

Results

To create a user-defined procedure written in the Java™ language:

  1. Write a Java static method, which can use the JDBC functions to interact with the database server.
  2. Compile the Java source and create a JAR file (the shared-object file).
  3. Execute the install_jar( ) procedure with the EXECUTE PROCEDURE statement to install the JAR file in the current database.
  4. If the UDR uses user-defined types, create a mapping between SQL data types and Java classes, using the setUDTextName( ) procedure that is explained in EXECUTE PROCEDURE statement.
  5. Register the UDR with the CREATE PROCEDURE statement. (If an external routine returns a value, you must register it with the CREATE FUNCTION statement, rather than with CREATE PROCEDURE.)

Rather than storing the body of an external routine directly in the database, the database server stores only the pathname of the shared-object file that contains the compiled version of the routine. The database server executes an external routine by invoking the external object code.

You must also hold either the Resource privilege or the DBA privilege on the database in which the external procedure will be registered, as well as the Usage privilege on the programming language in which the routine is written. (For the syntax of granting Usage privileges on the C language or on the Java language to a user, or to a role, or to the PUBLIC group, see Language-Level Privileges.)

When the IFX_EXTEND_ROLE configuration parameter is set to 1 or to ON, only users who have the built-in EXTEND role can create external procedures.