You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by Apache Wiki <wi...@apache.org> on 2011/07/18 23:08:33 UTC
[Db-derby Wiki] Trivial Update of "DerbySQLroutines" by ThomasHill
Dear Wiki user,
You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.
The "DerbySQLroutines" page has been changed by ThomasHill:
http://wiki.apache.org/db-derby/DerbySQLroutines?action=diff&rev1=22&rev2=23
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''' <<TableOfContents(2)>>
- '''Table of Contents'''
- <<TableOfContents(2)>>
== Functions vs. Procedures ==
<<Anchor(FunctionsVsProcedures)>>
- 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.
+ 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.
@@ -21, +19 @@
-------------
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 [[http://issues.apache.org/jira/browse/DERBY-551|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:
@@ -33, +30 @@
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 ([[http://issues.apache.org/jira/browse/DERBY-551|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 ||
- 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 ([[http://issues.apache.org/jira/browse/DERBY-551|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 ==
<<Anchor(CreatingFunctions)>>
The [[http://db.apache.org/derby/docs/dev/ref/rrefcreatefunctionstatement.html|Reference Guide]] provides the syntax for creating functions.
- The [[http://db.apache.org/derby/papers/fortune_tut.html|Apache Derby Fortune Server tutorial]] from ApacheCon 2004 shows how to create
+ The [[http://db.apache.org/derby/papers/fortune_tut.html|Apache Derby Fortune Server tutorial]] from ApacheCon 2004 shows how to create three SQL functions:
- 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
@@ -61, +57 @@
Examples from the Derby mail archives include:
- * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200412.mbox/%3c41C3584A.3080302@Source-Zone.Org%3e|A function without any parameters that returns an integer]]
+ * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200412.mbox/<41...@Source-Zone.Org>|A function without any parameters that returns an integer]]
- * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3C42A0E031.60003@bristowhill.com%3E|A function that takes two values, adds them together and returns the result; also shows how to invoke a function in a trigger]]
+ * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/<42...@bristowhill.com>|A function that takes two values, adds them together and returns the result; also shows how to invoke a function in a trigger]]
- * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/%3C43418926.6010407@sun.com%3E|A function that given a Date, returns the day of week]]
+ * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/<43...@sun.com>|A function that given a Date, returns the day of week]]
- * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200501.mbox/%3c41DAC70D.7000002@debrunners.com%3e|A function that converts a Timestamp to its corresponding Long value using Java]]
+ * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200501.mbox/<41...@debrunners.com>|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
+ 1
---------------------------------------------------------------------------------------------
- NULL
+ NULL
1 row selected
ij> values getSystemProperty('user.dir');
- 1
+ 1
---------------------------------------------------------------------------------------------
+ /tmp
- /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 ==
@@ -97, +90 @@
Examples from the Derby mail archives include:
- * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/%3C43415F06.3040601@sun.com%3E|A procedure that drops a table if it exists]]
+ * [[http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/<43...@sun.com>|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:
- 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
@@ -118, +107 @@
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.
- 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 {
@@ -136, +125 @@
conn.close();
}
}}}
-
Client side application code to call procedure.
{{{
- CallableStatement = conn.prepareCall("{ call DRS2(?, ?)}");
+ CallableStatement cs = 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.
@@ -159, +146 @@
== The power of Java in SQL ==
<<Anchor(ThePowerOfJavaInSql)>>
+ 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:
- 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
+ * SendEmailRoutine Sending e-mail from a database trigger with !JavaMail API
- * please add others
+ * please add others
- * or even just ideas of libraries that would be useful in Derby
+ * 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.
- 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.
- 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
+ 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.
- 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."