db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Francois Orsini <francois.ors...@gmail.com>
Subject Re: SQL functions, procedures and PSM - a possible approach
Date Fri, 07 Oct 2005 01:11:40 GMT
SQL/PSM (Persistent Stored Module) was added to SQL-92 in 1996 (as an
addendum I believe), but in general peopleconsiders it part of SQL-99.

Because it is a standard and Oracle PL/SQL is not that much different, one
could argue that providing support for SQL/PSM in Derby would allow people
to port their application even better, from other RDBMS. Yes Java is more
expressive, etc but what matters at the end is the existing user base and
easing the pain of porting applications to favorize Derby's adoption at the
end.

--francois

On 10/6/05, Rick Hillegas <Richard.Hillegas@sun.com> wrote:
>
> 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.
>
> Regards,
> -Rick
>
> 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