create function (SQLJ)

Description

Creates a user-defined function by adding a SQL wrapper to a Java static method. Can return a value defined by the method.

Syntax

create function [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

sql_function_name: is the Transact-SQL name of the function, must conform to the rules for identifiers, and cannot be a variable.

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.

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.

returns sql_datatype: specifies the 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 Adaptive Server 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.

java_method_name: specifies the name of the external Java method.

java_datatype: specifies a Java datatype that is mappable or result-set mappable. This is the Java method signature.

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'

Usage

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

Adaptive 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, Adaptive Server checks to see if it is a SQL user-defined function or a SQLJ user-defined function. If it is the latter, Adaptive Server checks for “sa” permissions. If it is a SQL function Adaptive Server checks for create function privileges.

Permissions

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

Granular permissions enabled	With granular permissions enabled, you must have the create function privilege. You must have create any function to run create function for other users.
Granular permissions disabled	With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
97	install	create function	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