db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David W. Van Couvering" <David.Vancouver...@Sun.COM>
Subject Re: SQL functions, procedures and PSM - a possible approach
Date Thu, 06 Oct 2005 13:21:15 GMT
This sounds great, Dan!  Is this a good candidate for putting up on the 
Wiki site as a proposal?

David

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:
> 
> CREATE FUNCTION TSDIFF(TS1 TIMESTAMP, TS2 TIMESTAMP)
> RETURNS INT
> 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
> 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.
> 
> 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();
>     }
> }
> 
> ---------------------------------------------------
> 
> 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
>    - ...???
> 
> Dan.
> 
> note 1: though this example may be bad, since I don't think JDBC escaped
> functions should be allowed in this SQL context.
> 
> 
> 

Mime
View raw message