apache > db
Apache Derby Fortune Server Tutorial
 
Font size:      

SQL Functions

The Fortune Server adds three SQL functions that perform operations that aren't standard SQL. You'll see the source code for them in the Java section. This section shows how they get defined in SQL and introduces their functionality. The source code is discussed in the Java/JDBC section.

tutRand

The tutRand SQL function returns a random integer between lower and upper integer bounds. For example, the query below returns a random integer between 1 and 5000:

ij> values tutRand(1,5000);
1
-----------
1530

ij> values tutRand(1,5000);
1
-----------
2589

The Fortune Server uses this function to return a random fortune from the fortunes table. The Jakarta Mathematics Library provides the code for this SQL function. You'll see the source code for it in the section on Java. Here's the SQL statement that creates it:

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

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.

ij> values tutMatch ('Shelby is a foxhound', 'hound');
1
-----------
1

ij> values tutMatch('Shelby is a foxhound', 'dog');
1
-----------
0

ij> values tutMatch ('Shelby is a foxhound', 'hound|dog');
1
-----------
1

This function gets used to return fortunes in the fortunes table that match a regular expression. Jakarta Regexp provides the code for this SQL function. You'll see the source code for it in the section on Java. Here's the SQL statement that creates it:

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';

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.

ij> values tutReplace (
 'My dog Shelby is a foxhound',
 'dog|hound',
 '  <b>$0</b>');
 1
 -------------------------------------------------
 My <b>dog</b> Shelby is a fox<b>hound</b>

This function gets used by the Fortune Server to format the matches in results displayed in a web browser. Jakarta Regexp provides the code for this SQL function. You'll see the source code for it in the section on Java. Here's the SQL statement that creates it:

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';

(Note: Regexp 1.3 clips the first two characters in the replacement text, so the example above prepends two spaces.)

Storing Java Classes in the Database

The FORTUNE_SERVER/sql subdirectory contains a jar file named fortuneServerSql.jar, which contains the classes that implement the Fortune Server SQL functions. This jar file is stored in the Derby database itself. You'll see all the source code in the Java section. This section briefly describes loading classes from a Derby database. Complete details are in the Developer's Guide.

The jar file gets imported into the Derby database using the SQLJ.install_jar stored procedure:

ij> CALL SQLJ.install_jar
('fortuneServerSql.jar', 'APP.TutorialJar', 0);

Next the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY stored procedure sets the database class path:

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

The Derby class loader looks first in the user's class path for any needed classes, then it looks in the database class path.