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]
	language java 
	parameter style java
	external name 'java_method_name 
		[([java_datatype[ {, java_datatype }

When creating a SQLJ function:

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 

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

NoteThe search sequence for functions in Adaptive Server is:

  1. Built-in functions

  2. SQLJ functions

  3. Java-SQL functions that are called directly