Product Documentation

FairCom Java & .NET Stored Procedures

Previous Topic

Next Topic

Structure of User-Defined Functions

The CREATE FUNCTION command creates a new User Defined Scalar Function in the current database

function_name is the name that is to be assigned to the function. Different owners can have functions with the same name in the same database

Each parameter_decl is of the form:

[ARG_TYPE] ARG_NAME DATA_TYPE

and is separated by a comma (‘,’)

  • ARG_TYPE is optional. When specified it can only be IN.
  • ARG_NAME specifies the name of the argument to the function. The function can access the values through the corresponding Java variables.
  • DATA_TYPE is any valid supported FairCom DB SQL data type which specifies the type of the argument.

The return value of the function is specified with the RETURNS key word followed by the data_type declaration.

The Java IMPORT statements which are needed for the correct functioning of the Java snippet (the code between BEGIN and END -- see below) need to be placed in the section between the IMPORT and BEGIN keywords. This section need not be present if there are no import statements.

The function cannot be created without specifying all the import statements needed by the snippet. (For details refer to any Java manual or book)

The Function body, referred to as the Java snippet, needs to be placed between the BEGIN and END keywords. Any valid Java code other than class or function definitions can be present here. The snippet must have a return statement.

Note: Each of the keywords IMPORT, BEGIN and END need to be upper case and be present on a separate line. Once the IMPORT or BEGIN are specified, the statement is terminated by an END but not a semicolon (‘;’).

Creating a User Defined Scalar Function

ISQL> CREATE FUNCTION str_cat(IN org_string VARCHAR(20), IN string_to_concat VARCHAR(20))

RETURNS VARCHAR(40)

IMPORT

import java.math.*;

BEGIN

String new_str = org_string + string_to_concat ;

return new_str;

END

The above example creates a User Defined Scalar Function, str_cat, that takes two input arguments and returns the concatenated string.

TOCIndex