[Db-derby Wiki] Update of "DerbySQLroutines" by DanDebrunner

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by DanDebrunner:
http://wiki.apache.org/db-derby/DerbySQLroutines

------------------------------------------------------------------------------
  
   * [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]
  
+ === 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:
+ 
+ SQL create statement
+ {{{
+ CREATE PROCEDURE DRS2(DP1 INTEGER, DP2 INTEGER)
+ PARAMETER STYLE JAVA
+ LANGUAGE JAVA
+ READS SQL DATA
+ 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.
+ {{{
+ public static void selectRows(int p1, int p2, ResultSet[] data1,
+ ResultSet[] data2) throws SQLException {
+ 
+   Curonnection conn = DriverManager.getConnection("jdbc:default:connection");
+   PreparedStatement ps1 = conn.prepareStatement("select * from t1 where i = ?");
+   ps1.setInt(1, p1);
+   data1[0] = ps1.executeQuery();
+ 
+   PreparedStatament ps2 = conn.prepareStatement("select * from t1 where i >= ?");
+   ps2.setInt(1, p2);
+   data2[0] = ps2.executeQuery();
+ 
+   conn.close();
+ }
+ }}}
+ 
+ Client side application code to call procedure.
+ 
+ {{{
+   CallableStatement = 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.
+  * The !ResultSet must be open and generated from the default connection (jdbc:default:connection) in order to be returned. Any other !ResultSets will be ignored.
+  * Closing the Statement that created the !ResultSet within the procedure's method will close the !ResultSet, so don't do that! However closing the connection is ok.
+  * The !PreparedStatement's or other Statement objects in the method must be created in the body of the method, do not cache them as static variables, that will not work. So unlike a JDBC client application a Java method for a procedure or a function cannot hold onto JDBC objects after it completes.
+  * The database engine (Derby) creates the one element !ResultSet arrays that hold the returned !ResultSets.
+  * Returning less !ResultSets than defined by the DYNAMIC RESULT SETS clause is ok, only fill in the number of arrays you need. So in this example to only return one !ResultSet either just set data1[0] to the !ResultSet or data2[0] to the !ResultSet. Remember the order returned through !CallableStatement is driven by the order of creation, not the order of the method's parameters.
+ 
  == The power of Java in SQL ==
  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