apache > db

- Home
- Apache Derby

Font size:

# SQL Functions

- Overview
- Stored Procedures and SQL Functions
  - Similarities
  - Differences
- Source Code
  - tutRand
  - tutMatch
  - tutReplace
- Building

The Fortune Server implements SQL functions. This page provided an overview of Derby functionality for stored procedures and SQL functions, then focuses on the latter.

## Overview

Derby supports implementing stored procedures and SQL functions in Java.

Stored procedures are invoked with the CALL statement or the CallableStatement method in a Java client application. Stored procedures support IN, OUT, and INOUT parameters. If the stored procedure has just IN parameters, you can invoke it anywhere with the CALL statement, including in ij. If the stored 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. That's about all this tutorial says about stored procedures. Much of the same Derby infrastructure is supported for both procedures and functions and this tutorial points out features available for both as well as the differences.

SQL functions execute as part of a SQL statement and can be used pretty much wherever a SQL expression is allowed, including in triggers and in VALUES statements, but not with CALL. However, functions are read-only (cannot modify data in the database). This tutorial focuses on SQL functions, featuring the Fortune Server SQL functions implemented using Jakarta components.

In a Derby database, the SQLJ.install_jar built-in stored procedure lets you store a jar file in the database. The SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY stored procedure sets the database class path, derby.database.classpath, to include that jar file. Derby will look for a Java class in the database class path if it doesn't first find it in the user's CLASSPATH.

The Fortune Server uses SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY and SQLJ.install_jar to manage the jar files for the SQL functions, so you'll see more about this topic. Incidentally, this jar file management corresponds to SQLJ Part 1, which defines SQL facilities for managing jar files, including:

- Built-in procedures that install, replace and remove jar files.
- A SQLJ schema that stores meta-data for built-in procedures.

## Stored Procedures and SQL Functions

### Similarities

Stored procedures and functions share many similarities, including:

- Written in Java, optionally using JDBC
- Static method, therefore stateless. It needs to be thread-safe, so avoid use of static variables.
- Can execute any valid Java code.
- Access to db in the context of the calling connection via JDBC with new connection object:
  - Obtained using standard JDBC URL: jdbc:default:connection
  - The connection is only valid during the method call.
  - JDBC objects are only valid during the method call.
- Actions within Java procedures and functions are not restricted
  - Exception is SQL control on connection of caller
  - You can execute non-database actions, such as send e-mail, send messages, and access web-services.
  - Method may declare or throw any exception type. Derby wraps it in a SQLException for the caller.
  - Conversion from Java to SQL types handled by database engine.

### Differences

The table below summarizes differences between stored procedures and SQL functions. Depending on what you want to do, one or the other might be better suited.

Feature | Stored Procedure | SQL Function
--- | --- | ---
Execute in a trigger. | N | Y
Return > 1 result set. | Y | N
Process OUT / INOUT Parameters. | Y | N
Execute SQL select. | Y | Y
Execute SQL insert/update/delete/create/drop. | Y | N
Execute in a SQL expression. | N | Y

## Source Code

### tutRand

The tutRand SQL function returns a random integer between lower and upper integer bounds. It gets defined in SQL like this:

    create function tutRand(minInt int, maxInt int)
    returns int
    language java
    parameter style java
    no sql
    external name 'examples.tutorial.derby.DerbyRand.randIntJakarta';

The source code for tutRand is in FORTUNE_HOME/examples/tutorial/derby/DerbyRand.java. The code is simple and shown below (minus comments):

    package examples.tutorial.derby;
    import java.util.*;
    import org.apache.commons.math.random.*;

    public class DerbyRand
    {
       public static int randIntJakarta(int minInt, int maxInt)
       {
           int return_int=0;
           RandomDataImpl randomData = new RandomDataImpl();
           return_int = randomData.nextInt(minInt,maxInt);
           return return_int;
       }
    }

The SQL Exercises show sample queries that use tutRand, but they don't show what happens when an error is generated. If we swap the minimum and maximum integers, we should see an error that demonstrates how Derby wraps the error in a SQL exception for the caller:

    ij> values tutRand(500,1);
    1
    -----------
    ERROR 38000: The exception 'java.lang.IllegalArgumentException: upper
    bound must be > lower bound' was thrown while evaluating an expression.
    ERROR XJ001: Java exception: 'upper bound must be > lower bound:
    java.lang.IllegalArgumentException'.

The FORTUNE_HOME/examples/tutorial/derby/DerbyRand.java file also includes an entry point for the Sun random integer method. If you want to use the Sun code, you could create a SQL function as shown below:

    create function tutRandSun(maxInt int)
    returns int
    language java
    parameter style java
    external name
    no sql
    'examples.tutorial.derby.DerbyRand.randIntSun';

### tutMatch

The tutMatch SQL function returns 1 if the value in the first argument matches the regular expression in the second argument; otherwise, it returns 0. It gets defined in SQL like this:

    create function tutMatch(source varchar(3000), re varchar(128))
    returns int
    language java
    parameter style java
    no sql
    external name 'examples.tutorial.derby.DerbyRegex.tutMatchRegexp';

The source code for tutMatch is in FORTUNE_HOME/examples/tutorial/derby/DerbyRegex.java. The code is shown below (minus comments):

    package examples.tutorial.derby;
    import java.util.*;
    import org.apache.regexp.*;

    public class DerbyRegex
    {
       public static int tutMatchRegexp(String src, String pattern)
       {
          int retval=0;
          RE r = new RE(pattern);
          if (r.match(src))
             retval=1;
          return retval;
       }
       ...
    }

The SQL Exercises show sample queries that use tutMatch. Below is an example that generates an error, demonstrating again how Derby wraps the error in a SQL exception:

    ij> values tutMatch ('abc', '[b-a]');
    1
    -----------
    ERROR 38000: The exception 'org.apache.regexp.RESyntaxException:
       Syntax error: Bad character class' was thrown while evaluating an
       expression.
    ERROR XJ001: Java exception: 'Syntax error: Bad character class:
       org.apache.regexp.RESyntaxException'.

### tutReplace

If the value in the first argument matches the regular expression in the second argument, the tutReplace SQL function replaces the matched text with the value in the third argument. It gets defined in SQL like this:

    create function tutReplace(source varchar(3000), re varchar(128),
    change varchar(128))
    returns varchar(3000)
    language java
    parameter style java
    no sql
    external name 'examples.tutorial.derby.DerbyRegex.tutReplaceRegexp';

The source code for tutReplace is in FORTUNE_HOME/examples/tutorial/derby/DerbyRegex.java. The code is shown below:

    package examples.tutorial.derby;
    import java.util.*;
    import org.apache.regexp.*;

    public class DerbyRegex
    {
       ...
       public static String tutReplaceRegexp(String src,String pattern,String change)
       {
          RE r = new RE(pattern);
          String s1 = r.subst(src, change, RE.REPLACE_BACKREFERENCES);
          return s1;
       }
    }

Regarding that RE.REPLACE_BACKREFERENCES argument passed to the subst method, the default for Jakarta Regexp is to not replace back references. Without this flag, the third argument gets treated as a literal instead of as a substitution rule. So, instead of this:

    ij> values tutReplace('abc', 'b', '  ***$0***');
    1
    ---------------------------------------------------
    a***b***c

You would get this:

    ij> values tutReplace('abc', 'b', '  ***$0***');
    1
    ---------------------------------------------------
    a***$0***c

The SQL Exercises show sample queries that use tutReplace. Below is an example that generates an error:

    ij> values tutReplace ('abc', '*a', 'oops!');
    1
    -----------------------------------------------------------------------------
    ERROR 38000: The exception 'org.apache.regexp.RESyntaxException: Syntax error:
    Missing operand to closure' was thrown while evaluating an expression.
    ERROR XJ001: Java exception: 'Syntax error: Missing operand to closure:
    org.apache.regexp.RESyntaxException'.

## Building

The SQL functions require derby.jar, commons-math-1.0-RC1.jar (for tutRand), and jakarta-regexp-1.3.jar (for tutMatch and tutReplace) in the class path to build. Change directory to FORTUNE_HOME/java, then build the functions and create the jar file as shown below:

    javac examples/tutorial/derby/DerbyRand.java
    javac examples/tutorial/derby/DerbyRegex.java
    jar -cvf fortuneServerSql.jar examples/tutorial/derby/DerbyRand.class \
      examples/tutorial/derby/DerbyRegex.class
    mv fortuneServerSql.jar ../sql