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] Update of "SqlPsmSupport" by DanDebrunner
Date Thu, 06 Oct 2005 16:11:30 GMT
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/SqlPsmSupport

------------------------------------------------------------------------------
  http://mail-archives.apache.org/mod_mbox/db-derby-dev/200510.mbox/%3c43448C37.4040909@debrunners.com%3e
  
  This page will be updated to summarize the discussion.
+ ----
+ == Outline of Approach ==
+ The approach proposed would be to basically re-write the SQL code in the procedure or function
as server side JDBC/Java byte code and then use the existing Java routine support to execute
it. That is the create SQL function or procedure would:
  
- more to come ...
+   1. validate the SQL routine body
+   1. convert the SQL routine body to server-side JDBC byte code in a new routine class with
a single public static method for the entry point
+   1. define the SQL routine to use that class and method just like a Java routine
+   1. store the generated routine class in the database
  
+ Then at execute routine time, Derby would ensure the routine class is
+ loaded before executing the routine
+ 
+ From a high-level it seems most of the pieces are already in Derby:
+ 
+   * server-side JDBC supported
+   * I think savepoints can be used in server-side JDBC to make multiple statements atomic
+   * SQL/PSM control statements all have corresponding control actions in Java byte code
+   * Derby already generates & loads classes for SQL statements
+   * Derby already stores generated classes for stored prepared statements.
+ 
+ ----
+ == Raw Prototype ==
+ So I spent a few hours hacking up some code, and managed to get a simple
+ example working, a SQL function with no parameters.
+ {{{
+ ij> create function ct() returns TIME language SQL
+ parameter style java external name 'xxx.yyy'
+ return current_time;
+ 
+ ij> values ct();
+ 1
+ -------
+ 15:20:00
+ }}}
+ (the parameter style and external name are there but ignored, that's
+ just more parser work).
+ 
+ The generated method has this signature:
+ {{{
+ public static java.sql.Time
+ org.apache.derby.psm.APP.CT.SQL051005031910950.psm_sql() throws
+ java.sql.SQLException
+ }}}
+ Here's the decompiled version of the generated routine byte code:
+ 
+ 
+ {{{
+ 
+ /* SQL051005034613770 - Decompiled by JODE
+  * Visit http://jode.sourceforge.net/
+  */
+ package org.apache.derby.psm.APP.CT;
+ import java.sql.Connection;
+ import java.sql.DriverManager;
+ import java.sql.SQLException;
+ import java.sql.Time;
+ 
+ public final class SQL051005034613770
+ {
+     private Connection conn;
+ 
+     private SQL051005034613770() {
+ 	/* empty */
+     }
+ 
+     private Time _psm_sql() throws SQLException {
+ 	Connection connection
+ 	    = DriverManager.getConnection("jdbc:default:connection");
+ 	SQL051005034613770 sql051005034613770_0_ = this;
+ 	sql051005034613770_0_.conn = connection;
+ 	java.sql.ResultSet resultset
+ 	    = conn.createStatement().executeQuery("VALUES current_time");
+ 	resultset.next();
+ 	Time time = resultset.getTime(1);
+ 	resultset.close();
+ 	conn.close();
+ 	return time;
+     }
+ 
+     public static Time psm_sql() throws SQLException {
+ 	return new SQL051005034613770()._psm_sql();
+     }
+ }
+ }}}
+ 

Mime
View raw message