Java™ user-defined routines

The behaviors of installing and invoking UDRs written in Java™ code follow the SQLJ: SQL Routines specification. Every UDR written in Java™ code maps to an external Java™ static method whose class is in a Java™ Archive (JAR) file that was installed in a database. The SQL-to-Java data type mapping is done according to the Informix® JDBC Driver specification.

UDRs can be user-defined functions or user-defined procedures, which can return values or not, as follows:
  • A user-defined function returns one or more values and therefore can be used in SQL expressions.
    For example, the following query returns the results of a UDR called area() as part of the query results: 
    SELECT diameter, area(diameter) FROM shapes
WHERE diameter > 6
  • A user-defined procedure is a routine that optionally accepts a set of arguments and does not return any values.
    A procedure cannot be used in SQL expressions because it does not return a value. However, you can call it directly, as the following example shows: 
    EXECUTE PROCEDURE myproc(1, 5)

    You can also call user-defined procedures within triggers.

For general information about UDRs, see the Informix® User-Defined Routines and Data Types Developer's Guide.

UDRs written in Java™ code can perform the following tasks.
Table 1. Tasks that can be performed by UDRs written in Java™
Type of UDR Purpose
End-user routine A UDR that performs some common task for an end user
User-defined aggregate A UDR that calculates an aggregate value on a PROCEDURE particular column or value
Parallelizable UDR A UDR that can run in parallel when executed within an SQL statement

(UDRs that open JDBC connections cannot run in parallel.)
Cast function A UDR that converts or casts one data type to another
Operator function A UDR that implements some operator symbol (such as +, -, or /)
Iterator function A user-defined function that returns more than one row of data

Iterator functions written in Java™ code are supported by using some HCL Informix® extensions.
Functional index A UDR on which an index can be built
Opaque data type support function A user-defined function that tells the database server how to handle the data of an opaque data type
Negator function A function that calculates the not operation for a particular operator or function
You cannot use UDRs written in Java™ code for any of the following features:
  • Commutator functions
  • Cost functions
  • Operator-class functions
  • Selectivity functions
  • User-defined statistics functions