SqlPsmSupport

Proposal to support functions and procedures writen in SQL by compiling them to server-side JDBC Java byte code and then executing them using the existing Java routine support. Functionality could then be extended to SQL/PSM with control statements.

Initially discussed on derby-dev mailing list under this thread. Please continue any discussion on the mailing list.

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:

1. validate the SQL routine body
2. 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
3. define the SQL routine to use that class and method just like a Java routine
4. 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();
    }
}

Possible storage of the generated class in SYSSTATEMENTS. This table already supports the storage of generated classes for stored prepared statements.

• Issue with existing index on STMTNAME,SCHEMAID. Would conflict name space with statements (which only currently exist for JDBC metadata statements). Could upgrade index to include TYPE.