Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id DB6BA7511 for ; Mon, 18 Jul 2011 21:08:59 +0000 (UTC) Received: (qmail 1662 invoked by uid 500); 18 Jul 2011 21:08:59 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 1622 invoked by uid 500); 18 Jul 2011 21:08:59 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 1615 invoked by uid 99); 18 Jul 2011 21:08:58 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 18 Jul 2011 21:08:58 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.131] (HELO eos.apache.org) (140.211.11.131) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 18 Jul 2011 21:08:53 +0000 Received: from eos.apache.org (localhost [127.0.0.1]) by eos.apache.org (Postfix) with ESMTP id 5529BF95 for ; Mon, 18 Jul 2011 21:08:33 +0000 (UTC) MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: Apache Wiki To: Apache Wiki Date: Mon, 18 Jul 2011 21:08:33 -0000 Message-ID: <20110718210833.10056.48722@eos.apache.org> Subject: =?utf-8?q?=5BDb-derby_Wiki=5D_Trivial_Update_of_=22DerbySQLroutines=22_by?= =?utf-8?q?_ThomasHill?= Auto-Submitted: auto-generated 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=3Ddiff&rev1=3D22&re= v2=3D23 This page describes how to implement SQL routines -- functions and proced= ures -- in java. Feel free to edit. Especially please add working examples. = + '''Table of Contents''' <> - '''Table of Contents''' - <> = =3D=3D Functions vs. Procedures =3D=3D <> = - 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 pr= etty much anywhere a SQL expression is allowed, such as in the {{{SELECT}}}= list or the {{{WHERE}}} clause. SQL functions can also be invoked in trigg= ers. 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 {{{Call= ableStatement}}} 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}}} statemen= t, including in ij. If the procedure has {{{OUT}}} or {{{INOUT}}} parameter= s, it can't be invoked from ij, it must be invoked from a client applicatio= n using the {{{CallableStatement}}} method. Starting in [[http://issues.apa= che.org/jira/browse/DERBY-551|Derby 10.2]], a java procedure can also be in= voked 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 da= tabase and the second, {{{SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY}}}, sets m= y 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|| = =3D=3D Creating Functions =3D=3D <> = 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 For= tune Server tutorial]] from ApacheCon 2004 shows how to create + The [[http://db.apache.org/derby/papers/fortune_tut.html|Apache Derby For= tune Server tutorial]] from ApacheCon 2004 shows how to create three SQL fu= nctions: - three SQL functions: = * {{{tutRand}}} generates a random integer using the Jakarta Math Librar= y. * {{{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 tha= t 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 re= turns 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 i= n 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 t= o 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 it= s corresponding Long value using Java]] = =3D=3D=3D System.getProperty as a function =3D=3D=3D - = 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 s= tyle 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.get= Property calls to be made to retrieve the properties in question. = =3D=3D Creating Procedures =3D=3D @@ -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]] = =3D=3D=3D Returning java.sql.ResultSets from Java procedures =3D=3D=3D + Derby follows the SQL standard part 13 (aka SQL-J part 1) for returning != ResultSets through Java procedures. Any procedures written this way will wo= rk on other database engines such as DB2 and Oracle. = + Each !ResultSet is returned through a separate argument to the java metho= d for the procedure that is a !ResultSet[] with one element. Here is a simp= le 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.s= electRows' }}} + Body of public static void Java method for procedure, using standard serv= er-side JDBC. Must be in a public class. = - Body of public static void Java method for procedure, using standard serv= er-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 =3D conn.prepareCall("{ call DRS2(?, ?)}"); + CallableStatement cs =3D 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 !Callable= Statement, in the order they were created. @@ -159, +146 @@ =3D=3D The power of Java in SQL =3D=3D <> = + 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 functio= n or procedure may call any of the standard Java libraries, any of the stan= dard 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 proced= ure 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 !JavaMai= l 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 = =3D=3D Are Derby Procedures *Stored* Procedures? =3D=3D + Databases, pioneered by Sybase, initially provided stored procedures that= were written in a enhanced SQL programming language. The enhanced SQL cont= ained flow control, variables etc. in addition to the standard DML construc= ts. The procedures were declared in by a CREATE PROCEDURE statement contain= ing the logic in the enhanced SQL. The database then compiled the procedure= and stored its definition and compiled form. Thus the procedures were comp= letely 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 cont= rol, 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 com= piled the procedure and stored - its definition and compiled form. Thus the procedures were completely sto= red by the database, hence - the term stored procedure. = + Derby currently supports procedures written in the Java programming langu= age, following the SQL Standard, Part 13. With these Java procedures, the i= mplementation 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 CRE= ATE PROCEDURE statement is no an atomic "define and store" operation. The c= ompiled Java for a procedure (or function) may be stored in the database us= ing the standard SQL procedure SQLJ.INSTALL_JAR or may be stored outside th= e database in the class path of the application. - Derby currently supports procedures written in the Java programming langu= age, 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" op= eration. The compiled Java for a procedure (or function) - may be stored in the database using the standard SQL procedure SQLJ.INSTA= LL_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 a= ny database that supports the standard, such + The advantage of Java procedures is that the same procedure will run on a= ny database that supports the standard, such as Derby, IBM's DB2 and Oracle. - as Derby, IBM's DB2 and Oracle. = =3D=3D Common Problems =3D=3D - = =3D=3D=3D *Unrecognized* procedures =3D=3D=3D - = Up until at least 10.1.3, attempting to call a procedure with the wrong n= umber of parameters causes an SQL exception: "ERROR 42Y03: 'SYSCS_UTIL.SYSC= S_IMPORT_DATA' is not recognized as a function or procedure.". = =3D=3D=3D SYSCS_IMPORT_DATA =3D=3D=3D - = The last line of data must be terminated with an end-of-line (possibly sy= stem dependant) or you will get an exception: "ERROR 38000: The exception '= SQL Exception: Read endOfFile at unexpected place on line 3.' was thrown wh= ile 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: T= here is no column named: set_id."