db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Trivial Update of "DerbySQLroutines" by ThomasHill
Date Mon, 18 Jul 2011 21:08:33 GMT
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/<41C3584A.3080302@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/<42A0E031.60003@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/<43418926.6010407@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/<41DAC70D.7000002@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/<43415F06.3040601@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."

Mime
View raw message