db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: SQL functions, procedures and PSM - a possible approach
Date Thu, 06 Oct 2005 20:58:33 GMT
Hi Dan,

This is pretty cool. I have to say, though, I would need some heavy 
motivation before embarking on implementing SQL/PSM. IMHO, Java is more 
expressive, more powerful, and better designed than SQL/PSM. Java is 
better defined and less ambiguous. Moreover, I suspect that Java code is 
more portable than vendor-specific dialects of SQL/PSM. In short, I 
think Java is a superior dbproc language. People are welcome to scratch 
their own itches, but implementing SQL/PSM looks to me like a big effort 
with not a lot of payoff.


Daniel John Debrunner wrote:

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