SQLJ user-defined functions

The create function command specifies a SQLJ function name and signature for a Java method. You can use SQLJ functions to read and modify SQL and to return a value described by the referenced method.

The SQLJ syntax for create function is:

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 }
		...]])]'

When creating a SQLJ function:

The SQL function signature is the SQL datatype sql_datatype of each function parameter.
To comply with the ANSI standard, do not include an @ sign before parameter names.

Sybase adds an @ sign internally to support parameter name binding. You will see the @ sign when using sp_help to print out information about the SQLJ stored procedure.
When creating a SQLJ function, you must include the parentheses that surround the sql_parameter_name and sql_datatype information—even if you do not include that information.

For example:
```
create function sqlj_fc()
		language java
		parameter style java
	external name 'SQLJExamples.method'
```
The modifies sql data clause specifies that the method invokes SQL operations and reads and modifies SQL data. This is the default value. You do not need to include it except for syntactic compatibility with the SQLJ Part 1 standard.
es returns null on null input and called on null input specify how Adaptive Server handles null arguments of a function call. returns null on null input specifies that if the value of any argument is null at runtime, the return value of the function is set to null and the function body is not invoked. called on null input is the default. It specifies that the function is invoked regardless of null argument values.

Function calls and null argument values are described in detail in “Handling nulls in the function call”.
You can include the deterministic or not deterministic keywords, but Adaptive Server does not use them. They are included for syntactic compatibility with the SQLJ Part 1 standard.
Clauses exportable keyword specifies that the function is to run on a remote server using Sybase OmniConnect™ capabilities. Both the function and the method on which it is based must be installed on the remote server.
Clauses language java and parameter style java specify that the referenced method is written in Java and that the parameters are Java parameters. You must include these phrases when creating a SQLJ function.
The external name clause specifies that the routine is not written in SQL and identifies the Java method, class and, package name (if any).
The Java method signature specifies the Java datatype java_datatype of each method parameter. The Java method signature is optional. If it is not specified, Adaptive Server infers the Java method signature from the SQL function signature.

Sybase recommends that you include the method signature as this practice handles all datatype translations. See “Mapping Java and SQL datatypes”.
You can define different SQL names for the same Java method using create function and then use them in the same way.

Writing the Java method

Before you can create a SQLJ function, you must write the Java method that it references, compile the method class, and install it in the database.

In this example, SQLJExamples.region() maps a state code to a region number and returns that number to the user.


	public static int region(String s) 
			throws SQLException {
		s = s.trim();
		if (s.equals(“MN”) || s.equals(“VT”) ||
			s.equals(“NH”) ) return 1;
		if (s.equals(“FL”) || s.equals(“GA”) ||
			s.equals(“AL”) ) return 2;
		if (s.equals(“CA”) || s.equals(“AZ”) ||
			s.equals(“NV”) ) return 3;
		else throw new SQLException
			(“Invalid state code”, “X2001”);	}

Creating the SQLJ function

After writing and installing the method, you can create the SQLJ function. For example:

create function region_of(state char(20)) 
		returns integer
language java parameter style java
external name 
		'SQLJExamples.region(java.lang.String)'

The SQLJ create function statement specifies an input parameter (state char(20))and an integer return value. The SQL function signature is char(20). The Java method signature is java.lang.String.

Calling the function

You can call a SQLJ function directly, as if it were a built-in function. For example:

select name, region_of(state) as region
	from sales_emps
where region_of(state)=3

The search sequence for functions in Adaptive Server is:

Built-in functions
SQLJ functions
Java-SQL functions that are called directly