create function (SQLJ)

Syntax

create function [or replace] [owner.]sql_function_name
		 ([ sql_parameter_name sql_datatype 
			[(length)| (precision[, scale ])]
		[[, sql_parameter_name sql_datatype 
			[(length)| (precision[, scale])]]
		...]])
	returns sql_datatype 
		[(length)| (precision[, scale])]
	[modifies sql data] 
	[returns null on null input | 
		called on null input]
	[deterministic | not deterministic]
	[exportable]
	language java 
	parameter style java
	external name 'java_method_name 
		[([java_datatype[, java_datatype 
		...]])] '

Parameters

or replace – redefines an existing function. Use this clause to change the definition of an existing user defined SQLJ function without dropping, re-creating, and regranting object privileges previously granted on the function.
sql_function_name – is the Transact-SQL name of the function, must conform to the rules for identifiers, and cannot be a variable. The name remains the same even when you use or replace.
sql_parameter_name – is the name of an argument to the function. The value of each input parameter is supplied when the function is executed. Parameters are optional; a SQLJ function need not take arguments.
Parameter names must conform to the rules for identifiers. If the value of a parameter contains nonalphanumeric characters, it must be enclosed in quotes. This includes object names qualified by a database name or owner name, since they include a period. If the value of the parameter begins with a numeric character, it also must be enclosed in quotes.

Use or replace to change the names and the number of parameters.
sql_datatype [(length) | (precision [, scale])] – is the Transact-SQL datatype of the parameter. See create procedure for more information about these parameters.
sql_datatype is the SQL procedure signature.

Use or replace to change the Transact-SQL datatype of the parameter to the function.
returns sql_datatype – specifies the result datatype of the function.
Use or replace to change result datatype of the function .
modifies sql data – indicates that the Java method invokes SQL operations, reads, and modifies SQL data in the database. This is the default and only implementation. It is included for syntactic compatibility with the ANSI standard.
deterministic | not deterministic – included for syntactic compatibility with the ANSI standard. Not currently implemented.
exportable – specifies that the procedure is to be run on a remote server using the SAP ASE OmniConnect^™ feature. Both the procedure and the method it is built on must reside on the remote server.
language java – specifies that the external routine is written in Java. This is a required clause for SQLJ functions.
parameter style java – specifies that the parameters passed to the external routine at runtime are Java parameters. This is a required clause for SQLJ functions.
external – indicates that create function defines a SQL name for an external routine written in a programming language other than SQL.
name – specifies the name of the external routine (Java method). The specified name—‘java_method_name [ java_datatype[{, java_datatype} ...]]’—is a character-string literal and must be enclosed in single quotes.
Use or replace to change the name of the external routine.
java_method_name – specifies the name of the external Java method.
Use or replace to change tthe Java method.
java_datatype – specifies a Java datatype that is mappable or result-set mappable. This is the Java method signature.
Use or replace to change the Java datatype.

Examples

Example 1 – Creates a function square_root that invokes the java.lang.Math.sqrt() method:

create function square_root
    (input_number double precision) returns
        double precision
    language java parameter style java
    external name 'java.lang.Math.sqrt'

Example 2 – Creates a SQLJ function named sqlj_testfun.

create function sqlj_testfun (p1 int)
                      returns int
                      language java
                      parameter style java
                      external name 'UDFSample.sample(int)'

The following replaces the previously created SQLJ function using the or replace clause. Parameter p2 is added and the external java method is changed but the object ID of the SQLJ function remains the same.

create or replace function sqlj_testfun (p1 int,p2 int) 
                      returns int
                      language java
                      parameter style java
                      external name 'UDFSample.sample2(int,int)'

Usage

You cannot create a SQLJ function with the same name as an SAP ASE built-in function.
You can create user-defined functions (based on Java static methods) and SQLJ functions with the same class and method names.

Note: The SAP ASE server searching order ensures that the SQLJ function is always found first.
You can include a maximum of 31 parameters in a create function statement.
When a function is created, the SAP ASE server checks to see if it is a SQL user-defined function or a SQLJ user-defined function. If it is the latter, the SAP ASE server checks for “sa” permissions. If it is a SQL function the SAP ASE server checks for create function privileges.
If the replaced SQLJ function is called by another function, both functions will be recompiled when called.
If the interface of the replaced function does not match that in the calling function, then the calling function must be replaced, otherwise the calling function raises an error. You can execute sp_depends on the replaced function to check for any calling objects.
If a function is referenced in a computed column or functional index, it cannot be replaced.

For objects dependent on replaced functions:

If the replaced SQLJ function is called by another function, both functions will be recompiled when called.
If the interface of the replaced function does not match that in the calling function, then the calling function must be replaced, otherwise the calling function raises an error. You can execute sp_depends on the replaced function to check for any calling objects.

Permissions

Any user who impersonates the function owner through an alias or setuser cannot replace the function.

The permission checks for create function (SQLJ) differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must have the `create function` privilege to create a function (SQLJ). To create a function (SQLJ) for another user, you must have the `create any function` privilege. You must be the function owner to replace the function.
Disabled	With granular permissions disabled, you must be the database owner, a user with sa_role, or have the `create function` privilege to create a function (SQLJ). To create a function (SQLJ) for another user, you must have sa_role. You must be the function owner to replace the function.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	97
Audit option	install
Command or access audited	create function
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if a set proxy is in effect `or replace` – for create or replace