Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE FUNCTION statementUse the CREATE FUNCTION statement to create a user-defined function, to register an external function, or to write and register an SPL function.
External Functions

External Functions

About this task

External functions are functions you write in an external language (that is, a programming language other than SPL) that HCL OneDB™ supports.

To create a C user-defined function

Procedure

Write the C function.
Compile the function and store the compiled code in a shared library (the shared-object file for C).
Register the function in the database server with the CREATE FUNCTION statement.

Results

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

Write a Java static method, which can use the JDBC functions to interact with the database server.
Compile the Java source file and create a .jar file (the shared-object file for Java).
Execute the install_jar( ) procedure with the EXECUTE PROCEDURE statement to install the JAR file in the current database.
If the UDR uses user-defined data types, create a map between SQL data types and Java classes. Use the setUDTextName( ) procedure that is explained in EXECUTE PROCEDURE statement.
Register the UDR with the CREATE FUNCTION statement.

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. When it executes the external routine, the database server invokes the external object code.

The database server stores information about an external function in system catalog tables, including sysprocbody and sysprocauth. For more information on the system catalog, see the HCL OneDB Guide to SQL: Reference.