db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject SQL functions, procedures and PSM - a possible approach
Date Thu, 06 Oct 2005 02:30:15 GMT
After Army posted this on the user list (as a workaround for the escape
function not being supported on ODBC)

[create a Java function using this code]

> public static int tsDiffDays (Timestamp ts1, Timestamp ts2)
>     throws SQLException
> {
>     Connection conn = DriverManager.getConnection(
>         "jdbc:default:connection");
>     PreparedStatement pSt = conn.prepareStatement(
>         "values { fn timestampdiff (SQL_TSI_DAY, ?, ?) }");
>     pSt.setTimestamp(1, ts1);
>     pSt.setTimestamp(2, ts2);
>     ResultSet rs = pSt.executeQuery();
>     rs.next();
>     return rs.getInt(1);
> } 

I wondered how hard it would be to get Derby to support SQL functions
and procedures, and then move onto SQL/PSM. This would mean that the
above workaround would not involve the user writing Java code, just the
create function, something like:

RETURN { fn timestampdiff (SQL_TSI_DAY, TS1, TS2) }
(* see note 1)

Thinking about it I thought that an approach would be to basically
re-write the SQL code in the procedure or function as server side
JDBC/Java code (as Army did manually) 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
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.

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();

(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

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");
	Time time = resultset.getTime(1);
	return time;

    public static Time psm_sql() throws SQLException {
	return new SQL051005034613770()._psm_sql();


My current code does include hacks, but does this seem an acceptable
general approach to supporting SQL routines and PSM, re-write to Java
and use existing functionality? As with SQL statements we will pick up
the advantage that the JIT will optimize the code, this most likely has
most value with SQL/PSM and control statements within procedures.

Or course there's lot more to do, but if I generate some initial working
code, others could join in:

   - JDBC metadata
   - parameters
   - PSM control statements
   - atomic blocks
   - pre-compiling statements within routine bodies using stored
prepared statement mechanism (optional)
   - multiple statement procedures
   - multiple statement triggers
   - correct exception handling
   - ...???


note 1: though this example may be bad, since I don't think JDBC escaped
functions should be allowed in this SQL context.

View raw message