This page describes how to implement SQL routines -- functions and procedures -- in java. Feel free to edit. Especially please add working examples.

Table of Contents

Functions vs. Procedures

There is overlap between SQL Functions and Procedures, but each can also do things the other cannot and the syntax for invoking each is different.

SQL functions execute as part of a SQL statement and can be used pretty much anywhere a SQL expression is allowed, such as in the SELECT list or the WHERE clause. SQL functions can also be invoked in triggers. However, they are read-only -- they cannot modify data in the database.

Below is an example of invoking the built-in sql LOWER function in ij. LOWER simply takes an input string and converts each character to lowercase, for example:

ij> values lower('LowerCASE me!');
1
-------------
lowercase me!

Procedures are invoked with the CALL statement or the CallableStatement method in a Java client application. Procedures support IN, OUT, and INOUT parameters. If the procedure has just IN parameters, you can invoke it anywhere with the CALL statement, including in ij. If the procedure has OUT or INOUT parameters, it can't be invoked from ij, it must be invoked from a client application using the CallableStatement method. Starting in Derby 10.2, a java procedure can also be invoked in a trigger.

Here's an example of invoking two built-in procedures using ij. The first, SQLJ.install_jar, loads my 'myStuff.jar' jar file into the database and the second, SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY, sets my database class path to include that jar:

ij> CALL SQLJ.install_jar
('myStuff.jar', 'APP.MyStuffJar', 0);

ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
('derby.database.classpath', 'APP.MyStuffJar');

You can't invoke a sql function using CALL -- and you can't invoke a procedure using VALUES. That's just one of the differences between them. More comparisons are summarized in the table below:

Feature

Procedure

Function

Execute in a trigger

no (yes in 10.2)

yes

Return result set(s)

yes

no

Process OUT / INOUT Params

yes

no

Execute SQL select

yes

yes

Execute SQL update/insert/delete

yes

no

Execute DDL (create/drop)

yes

no

Execute in a SQL expression

no

yes

Creating Functions

The Reference Guide provides the syntax for creating functions.

The Apache Derby Fortune Server tutorial from ApacheCon 2004 shows how to create three SQL functions:

The Derby functional tests are an excellent source for function samples.

Examples from the Derby mail archives include:

A Few Working Function Examples

Below are a few code samples developed for Apache Derby by Students of Indian Institute of Technology Guwahati under the guidance of Girish Sundaram ( gisundar@in.ibm.com ). Users can download the source code for their use. Users can contact the developers mentioned below for function specific queries.

Developers Involved:

  1. Sreedish PS ( sreedish@iitg.ernet.in )

  2. Swarup K Malik ( m.swarup@iitg.ernet.in )

  3. Rinku Das ( rinku@iitg.ernet.in )

Project Guide and Technical Lead:

  1. Mr. Girish Sundaram ( gisundar@in.ibm.com )

  2. Prof. Dr. Pinaki Mitra ( pinaki@iitg.ernet.in )

Source Code is available for download at Google Code: http://code.google.com/p/apache-derby-functions/downloads/list

Function name   :dayname

input           :string, string
return  :string

This function takes an input string as date and a string as format of the date and returns a

mixed case character string containing the name of the day for the day portion of the argument

based on the locale when the database was started.
It uses previously defined function “validateADate” to validate the date first.
It returns null if not valid;
If the argument is null, the result is null.

Function name   :gmtdate

input           :void
return  :string

This function returns a string value representing the current date/time in the GMT time zone.

Create function statement:
create function Gmt_date() returns varchar(50) parameter style java no sql language java

external name 'apache.Gmtdate.gmtdate';

Function name   :localtimezone

input           :void
return  :string

This function returns an integer as interval value representing the local time zone displacement

from GMT .

Create function statement:
create function Local_TimeZone() returns varchar(50) parameter style java no sql language java

external name 'apache.Local_Timezone.localtimezone';

Function name   :monthname

input           :string, string
return  :string
This function takes an input string as date and a string as format of the date and returns a

mixed case character string containing the name of the month for the month portion of the

argument based on the locale when the database was started.
It uses previously defined function “validateADate” to validate the date first.
It returns null if not valid;
If the argument is null, the result is null.

Create function statement:
create function monthname(dt varchar(20), format2 varchar(20)) returns varchar(10) parameter

style java no sql language java external name 'apache.Monthname.monthname';

Function name   :number_format

input           :double, int, int, int, int, boolean
return  :string

This function returns a string representing a number with grouped thousands with specified

proper input.

Create function statement:
create function number_format(number double, minfracdgt integer, maxfracdgt integer, minintdgt

integer, maxintdgt integer, grouping boolean ) returns varchar(50) parameter style java no sql

language java external name 'apache.Number_format.number_format';

Function name   :quarter

input           :string, string
return  :int

This function takes an input string as date and a string as format of the date and returns an

integer value in the range 1 to 4, representing the quarter of the year for the date specified

in the argument.
It uses previously defined function “validateADate” to validate the date first.
It returns zero(0) if not valid.
If the argument is null, the result is zero(0).

Create function statement:
create function quarter(dt varchar(20), format2 varchar(20)) returns integer parameter style

java no sql language java external name 'apache.Quarter.quarter';

Function name   :replaceAt

input           :string, integer, string
return  :string

This function takes an input string and a string as replacement and an integer as a specified

position and returns a string.
If input string is null, it returns null.

Create function statement:
create function replace_at(str varchar(20),  pos integer, r varchar(10)) returns varchar(20)

parameter style java no sql language java external name

'apache.ReplaceAtSpecifiedPos.replaceAt';

Function name   :replacewithin

input           :string, integer, integer, string
return  :string

This function takes an input string and a string as replacement and two integers to specify the

position within which replacement takes place and returns a string.
If input string is null, it returns null.

Create function statement:
create function replace_within(str varchar(20),  startpos integer, endpos integer, r varchar

(10)) returns varchar(20) parameter style java no sql language java external name

'apache.ReplaceWithin.replacewithin';

Function name   :sign
input           :double
return  :integer

This function takes a double as input and returns integer value 1, 0 and -1 if the number is

positive, zero or negative respectively.

Create function statement:
create function sign_of_a_number(number double) returns integer parameter style java no sql

language java external name 'apache.Sign.sign';

Function name   :stringreverse

input           :string
return  :string

This function takes a string as input and returns the reverse of the string. If string is null

it returns null.

Create function statement:
create function reverse_a_string(arg varchar(20)) returns varchar(20) parameter style java no

sql language java external name 'apache.StringReverse.stringreverse';

Function name   :truncate

input           :double, integer
return  :double

This function takes a double and an integer as inputs and returns double truncated to integer

places to the right of the decimal point if integer is positive, or to the left of the decimal

point if integer is zero or negative.

Create function statement:
create function truncate(value double, places integer) returns double parameter style java no

sql language java external name 'apache.Truncate.truncate';

Function name   :validateADate

input           :string, string, boolean
return  :boolean

This function takes a string for a date, another string for format of date and a boolean to set

lenient as inputs and returns boolean  true if the specified date is valid, and false otherwise.

Create function statement:
create function validate_a_date(maybeDate varchar(20), format1 varchar(20), lenient boolean )

returns boolean parameter style java no sql language java external name

'apache.ValidateAGivenDate.validateADate';

Function name   :week_of_month

input           :string, string
return  :int

This function takes an input string as date and a string as format of the date and returns an

integer as the week of the month of the argument in range 1-5. The week starts with Sunday.
It uses previously defined function “validateADate” to validate the date first.
It returns zero(0) if not valid;

Create function statement:
create function week_of_month(dt varchar(20), format2 varchar(20)) returns integer parameter

style java no sql language java external name 'apache.Weekofmonth.week_of_month';

Function name   :week_of_year

input           :string, string
return  :int

This function takes an input string as date and a string as format of the date and returns an

integer as the week of the year of the argument in range 1-52. The week starts with Sunday.
It uses previously defined function “validateADate” to validate the date first.
It returns zero(0) if not valid;

Create function statement:
create function week_of_year(dt varchar(20), format2 varchar(20)) returns integer parameter

style java no sql language java external name 'apache.Weekofyear.week_of_year';

Function name   :wordWrap

input           :string, integer
return  :string

This function takes a string and an integer as inputs and returns a string after wrapping the

input string into new lines when it reaches a specified input length.
If input string is null, it returns null.

Create function statement:
create function wrap_a_word(string varchar(30), length integer) returns varchar(50) parameter

style java no sql language java external name 'apache.Wordwrap.wordWrap';

Function name   :anagram

input: String,String
return: boolean

This function takes two strings as input and check whether the first string is a permutation of

the second one or not. Based on this the function will return true or false.

Create function statement:
create function Anagram(str1 varchar(20), str2 varchar(20)) returns boolean parameter style java

no sql language java external name 'Anagram.findAnagram';

Function name   :daysBetween

input:Date,Date
return: int

This function takes two instances of Dates as input and returns the number of days between them.

Create function statement:

create function DateDifference(d1 Date,d2 Date) returns int parameter style java no sql language java external name 'DateDifference.daysBetween';

Function name   :findDayOfTheWeek

input:int,int,int
return: int

This method takes three integers(date, month, and year) as input arguments and returns values

from 0 to 6 indicating from Sunday to Saturday.

Create function statement:

create function DayOfTheWeekFinder(day int,month int,year int) returns double parameter style java no sql language java external name 'DayOfTheWeekFinder.findDayOfTheWeek';

Function name   :findExpm

input:Double
return:Double

This function takes a double value as input and returns e^input -1 . This functions is very

useful while calculating values when they are very much close to zero and they will be truncated

because of precision issues.

Create function statement:

create function Expm(d double) returns double parameter style java no sql language java external name 'Expm.findExpm';

Function name   :findDate

input: int, int, int, int
return: int

This functions takes four integer arguments as parameters, which are respectively values

corresponding to date, month, year and count. Count indicates the number of days to moved

forward. Example: If you want to find the date which is 100 days from 11/03/2011, make a call

like FindDate(11,3,2011,100). Returns 1 if function executred perfectly.

Create function statement:
create function FindDate(dd int,mm int,yy int,int count) returns int parameter style java no sql language java external name 'FindDate.findDate';

Function name   :findIsPrime

input:int
return:boolean

This function takes an integer as input and returns true or false based on the primality of the number.

Create function statement:
create function IsPrime(n int) returns boolean parameter style java no sql language java external name 'IsPrime.findIsPrime';

Function name   :findMD5

input:String
return:String

This function takes a string as input and calculates its MD5 checksum and returns the checksum value as a string.

Create function statement:
create function MD5(str varchar(100)) returns varchar(100) parameter style java no sql language java external name 'MD5.findMD5';

Function name   :findNextPrime

input:int
return:int

This function finds the next prime number followed by the given number

Create function statement:
create function NextPrime(n int) returns int parameter style java no sql language java external name

Function name   :findNumberofPrimes

input: int
return: int

This function returns an integer and takes an integer as input. The return value indicates the

number of prime numbers which are less than or equal to the passed argument.

Create function statement:
create function NumberOfPrimes(n int) returns int parameter style java no sql language java external name 'NumberOfPrimes.findNumberOfPrimes'

Function name   :shuffleString

input: String
return: string

This functions takes a string as input and returns a randomly shuffled permutation of the

string. This shuffling is done based on several random numbers generated.

Create function statement:
create function Shuffle(str varchar(20)) returns varchar(20) parameter style java no sql language java external name 'Shuffle.shuffleString';

Function name   :findSimilarText

input: string,string
return: double

This function takes two strings as input and based on Jaccard index, this function calculates a

double value in the range from 0 to 100 indicating how much similar these strings are. Two

identical strings will produce a return value of 100 and two completely disjoint strings will produce a return value of 0.

Create function statement:
create function SimilarText(str1 varchar(20),str2 varchar(20)) returns double parameter style java no sql language java external name 'SimilarText.FindSimilarText';

Function name   :SpaceBuilder

input: int
return:int

This function returns a string composed only of spaces and the number of spaces is.

Create function statement:
create function Space(num int) returns varchar(200) parameter style java no sql language java external name 'Space.SpaceBuilder';

Function name   :MethodStripTag

input:string
return:string

This method takes a string as inpuit and based on a regular expression, it strips of all the

tags such as HTML tags from the string and returns the string wihtout any tags.

Create function statement:
create function StripTag(str varchar(200)) returns varchar(200) parameter style java no sql language java external name 'StripTag.MethodStripTag';

Function name   :strSet

input:string , char
return:String

This function takes a string and character as input and returns a string re writed with that input argument.

Create function statement:
create function StrSet(str varchar(200)) returns varchar(200) parameter style java no sql language java external name 'StrSet.convert';

Function name   :convert

input:string
return:String

This function takes a string as input and returns a string with the first letter changed to

uppercase. This function can be made useful in cases like formatting names etc.

Create function statement:
create function UppercaseFirst(str varchar(200)) returns varchar(200) parameter style java no sql language java external name 'UppercaseFirst.convert';

Function name   :bitAnd

input           : int , int
return          :int

The bitAnd numeric function performs a bitwise AND on the binary
representation of two numbers.

Create function statement:

create function BitAnd(a int,b int) returns int parameter style java no sql language java

external name 'BitAnd.bitAnd';

Function name   :bitNot

input           : int
return          :int

The bitNot numeric function performs a bitwise complement on the
binary representation of a number.

Create function statement:

create function BitNot(a int) returns int parameter style java no sql language java external

name 'BitNot.bitNot';

Function name   :bitOr

Input: int , int
Return: int

The BITOR numeric function performs a bitwise OR on the binary
representation of two numbers.

Create function statement:

create function BitOr(a int,b int) returns int parameter style java no sql language java

external name 'BitOr.bitOr';

Function name   :bitXor

Input: int , int
Return: int

The BITXOR numeric function performs a bitwise XOR on the binary
representation of two numbers.

Create function statement:

create function BitXor(a int,b int) returns int parameter style java no sql language java

external name 'BitXor.bitXor';

Function name   :contain

Input: String , String
Return: boolean

contain returns TRUE if the SearchExpression is present within the
SourceExpression, otherwise it returns FALSE.

Create function statement:

create function Contains(SourceExpression varchar(20),SearchExpression varchar(20)) returns

boolean parameter style java no sql language java external name 'Contains.contain';

Function name   :endsWith

input           :String , String
return          :boolean

endsWith returns TRUE if the SearchExpression is present at the end of SourceExpression,

otherwise it returns FALSE.

Create function statement:

create function EndsWith(SourceExpression varchar(20),SearchExpression varchar(20)) returns

boolean parameter style java no sql language java external name 'EndsWith.endswith';

Function name   :left

input           :String , int
return          :String

LEFT returns a string consisting of the source string truncated to the length given by the

length expression. The truncation discards the final characters of the source string.
The result is of the same type as the source string. If the length is negative or zero, a zero

length string is returned. If either parameter is NULL, the result is NULL.

Create function statement:

create function Left(SourceExpression varchar(20),len int) returns boolean parameter style java

no sql language java external name 'Left.left';

Function name   :overLay

input           :String , String ,int , int
return          :String

If any of the inputs are NULL, the OVERLAY function returns a NULL.The embedded_string replaces

the length characters in string starting at character position start. If the length is not

specified, then the embedded_string will replace all characters after start in string.

Create function statement:

create function OverLay(SourceString varchar(50), EmbeddedString varchar(50),  FromStart int,

ForLength int)) returns varchar(50) parameter style java no sql language java external name

'OverLay.overLay';

Function name   :position

input           :String , String ,int , int
return          :int

POSITION returns an integer giving the position of one string (SearchExpression) in a second

string (SourceExpression). A position of one corresponds to the first character of the source

string.

Create function statement:

create function Position(String SearchExpression , String SourceExpression) returns int)

parameter style java no sql language java external name 'Position.position';

Function name   :power

input           :double , double
return          :double

POWER returns the given value raised to the given power. The parameters can be any built-in

numeric data type. The result is FLOAT unless any parameter is NULL.
 An exception occurs, if the value is either:
Zero and the power is negative, or
Negative and the power is not an integer. In these cases it will return -1.


Create function statement:

create function Power( a double, b double) returns double parameter style java no sql language

java external name 'Power.power';

Function name   :replace

Input: String ,String , String
Return:String

REPLACE replaces parts of a string with supplied substrings.


Create function statement:

create function Replace( SourceStringExpression varchar(50),  SearchStringExpression varchar

(50),  ReplaceStringExpression varchar(50)) returns varchar(50) parameter style java no sql

language java external name 'Replace.replace';

Function name   :replicate

Input: String , int
Return:String

REPLICATE returns a string consisting of the pattern string given by PatternStringExpression

repeated the number of times given by CountNumericExpression.

Create function statement:

create function Replace( PatternStringExpression varchar(50),  CountNumericExpression int )

returns varchar(50) parameter style java no sql language java external name

'Replicate.replicate';

Function name   :right

Input: String , int
Return:String

RIGHT returns a string consisting of the source string truncated to the length given by the

length expression. The truncation discards the initial characters of the source string. The

result is of the same type as the source string. If the length is negative or zero, a zero

length string is returned. If either parameter is NULL, the result is NULL.


Create function statement:

create function Right( SourceExpression varchar(50),  len int ) returns varchar(50) parameter

style java no sql language java external name 'Right.right';

Function name   :startsWith

Input: String , String
return:boolean

STARTSWITH returns TRUE if SourceExpression begins with SearchExpression, otherwise it returns

FALSE.


Create function statement:

create function Right(SourceExpression varchar(50),  SearchExpression varchar(50) ) returns

boolean parameter style java no sql language java external name 'StartsWith.startswith';

Note that for this to work, Derby's security policy must allow System.getProperty calls to be made to retrieve the properties in question.

Creating Procedures

The Reference Guide provides the syntax for creating procedures.

The Derby functional tests also include procedures.

Examples from the Derby mail archives include:

Returning java.sql.ResultSets from Java procedures

Derby follows the SQL standard part 13 (aka SQL-J part 1) for returning ResultSets through Java procedures. Any procedures written this way will work on other database engines such as DB2 and Oracle.

Each ResultSet is returned through a separate argument to the java method for the procedure that is a ResultSet[] with one element. Here is a simple example:

SQL create statement

CREATE PROCEDURE DRS2(DP1 INTEGER, DP2 INTEGER)
PARAMETER STYLE JAVA
LANGUAGE JAVA
READS SQL DATA
DYNAMIC RESULT SETS 2
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'

Body of public static void Java method for procedure, using standard server-side JDBC. Must be in a public class.

public static void selectRows(int p1, int p2, ResultSet[] data1,
ResultSet[] data2) throws SQLException {

  Connection conn = DriverManager.getConnection("jdbc:default:connection");
  PreparedStatement ps1 = conn.prepareStatement("select * from t1 where i = ?");
  ps1.setInt(1, p1);
  data1[0] = ps1.executeQuery();

  PreparedStatement ps2 = conn.prepareStatement("select * from t1 where i >= ?");
  ps2.setInt(1, p2);
  data2[0] = ps2.executeQuery();

  conn.close();
}

Client side application code to call procedure.

  CallableStatement cs = conn.prepareCall("{ call DRS2(?, ?)}");
  cs.setInt(1, p1);
  cs.setInt(2, p2);

  boolean hasResults = cs.execute();

  while (hasResults) {
      ResultSet rs = cs.getResultSet();
      while (rs.next()) {
          // ....
      }
      rs.close();
      hasResults = cs.getMoreResults();
  }

Items to note:

The power of Java in SQL

The ability to write functions and procedures in Java brings the complete set of Java apis into your SQL environment as server side logic. A function or procedure may call any of the standard Java libraries, any of the standard Java extensions, or other third party libraries. Examples are:

Are Derby Procedures *Stored* Procedures?

Databases, pioneered by Sybase, initially provided stored procedures that were written in a enhanced SQL programming language. The enhanced SQL contained flow control, variables etc. in addition to the standard DML constructs. The procedures were declared in by a CREATE PROCEDURE statement containing the logic in the enhanced SQL. The database then compiled the procedure and stored its definition and compiled form. Thus the procedures were completely stored by the database, hence the term stored procedure.

Derby currently supports procedures written in the Java programming language, following the SQL Standard, Part 13. With these Java procedures, the implementation of the procedure, a public static Java method in a Java class, is compiled outside the database, typically archived into a jar file and presented to the database with the CREATE PROCEDURE statement. Thus the CREATE PROCEDURE statement is no an atomic "define and store" operation. The compiled Java for a procedure (or function) may be stored in the database using the standard SQL procedure SQLJ.INSTALL_JAR or may be stored outside the database in the class path of the application.

The advantage of Java procedures is that the same procedure will run on any database that supports the standard, such as Derby, IBM's DB2 and Oracle.

Permissions on Routines

This section deals with issues which arise when you run Derby with authentication and authorization enabled.

By default, a function/procedure can be run only by its creator. However, the creator can grant other users EXECUTE permission on the routine. See the Reference Guide for information on the GRANT command.

In addition, by default functions/procedures run with the privileges of the current user. So for instance, a procedure which writes new rows to a table will work properly only for users who have been granted INSERT privilege on that table. The routine creator can override this behavior by declaring that the routine executes with "definer's rights". This will make the routine execute with the privileges of its creator rather than with the privileges of the current user. For information on how to declare a routine with "definer's rights", see the Reference Guide sections on CREATE FUNCTION and CREATE PROCEDURE.

Common Problems

*Unrecognized* procedures

Up until at least 10.1.3, attempting to call a procedure with the wrong number of parameters causes an SQL exception: "ERROR 42Y03: 'SYSCS_UTIL.SYSCS_IMPORT_DATA' is not recognized as a function or procedure.".

SYSCS_IMPORT_DATA

The last line of data must be terminated with an end-of-line (possibly system dependant) or you will get an exception: "ERROR 38000: The exception 'SQL Exception: Read endOfFile at unexpected place on line 3.' was thrown while evaluating an expression."

The table and field names must be given in ALL UPPER CASE. Otherwise you will get "ERROR XIE0M: Table 'property' does not exist." or "ERROR XIE08: There is no column named: set_id."

If the number of columns in the data is greater than the number of columns in the insertColumns list then the extra columns are ignored. However, if the reverse is true then you get this ugly and nearly meaningless SQL exception: "ERROR 38000: The exception 'SQL Exception: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression."

DerbySQLroutines (last edited 2012-04-03 06:07:43 by Sreedish)