DerbySQLroutines

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

1. Functions vs. Procedures
2. Creating Functions
3. Creating Procedures
4. The power of Java in SQL
5. Are Derby Procedures *Stored* Procedures?
6. Common Problems

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:

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:

• tutRand generates a random integer using the Jakarta Math Library.

• tutMatch and tutReplace perform regular expression search and replace using Jakarta Regexp

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

Examples from the Derby mail archives include:

• A function without any parameters that returns an integer

• A function that takes two values, adds them together and returns the result; also shows how to invoke a function in a trigger

• A function that given a Date, returns the day of week

• A function that converts a Timestamp to its corresponding Long value using Java

System.getProperty as a function

Here's a very simple example of how to define and use the JDK's System.getProperty() method from a Derby SQL function:

ij> create function getSystemProperty(name varchar(128)) returns varchar(128) language java external name 'java.lang.System.getProperty' parameter style java no sql;
0 rows inserted/updated/deleted
ij> values getSystemProperty('derby.system.home');
1                                                                                                                               
---------------------------------------------------------------------------------------------
NULL                                                                                                                            

1 row selected
ij> values getSystemProperty('user.dir');
1                                                                                                                               
---------------------------------------------------------------------------------------------
/tmp                                                                                                                            

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:

• A procedure that drops a table if it exists

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 = conn.prepareCall("{ call DRS2(?, ?)}");
  cs.setInt(1, p1);
  cs.setInt(2, p2);
  cs.execute();
  WORK IN PROGESS

Items to note:

• The ResultSets are returned to the application, through the CallableStatement, in the order they were created.

• The ResultSet must be open and generated from the default connection (jdbc:default:connection) in order to be returned. Any other ResultSets will be ignored.

• Closing the Statement that created the ResultSet within the procedure's method will close the ResultSet, so don't do that! However closing the connection is ok.

• The PreparedStatement's or other Statement objects in the method must be created in the body of the method, do not cache them as static variables, that will not work. So unlike a JDBC client application a Java method for a procedure or a function cannot hold onto JDBC objects after it completes.

• The database engine (Derby) creates the one element ResultSet arrays that hold the returned ResultSets.

• Returning less ResultSets than defined by the DYNAMIC RESULT SETS clause is ok, only fill in the number of arrays you need. So in this example to only return one ResultSet either just set data1[0] to the ResultSet or data2[0] to the ResultSet. Remember the order returned through CallableStatement is driven by the order of creation, not the order of the method's parameters.

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:

• SendEmailRoutine Sending e-mail from a database trigger with JavaMail API

• please add others

• or even just ideas of libraries that would be useful in Derby

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.

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."