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 HCL OneDB™ 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 HCL OneDB 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 OneDB 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