[Db-derby Wiki] Update of "JavaTableFunctions" by DanDebrunner

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by DanDebrunner:
http://wiki.apache.org/db-derby/JavaTableFunctions

New page:
= Java Table Functions =
Just some notes on how the SQL standard expects table functions written in Java to work,
at least my understanding, please correct any mistakes.
== SQL Standard 2003 incl. Part 13 ==
=== DDL ===
SQL/Foundation 11.50 SQL Invoked Routine
{{{
<returns table type> ::= TABLE <table function column list>
<table function column list> ::=
<left paren> <table function column list element>
[ { <comma> <table function column list element> }... ] <right paren>
<table function column list element> ::= <column name> <data type>
}}}

Example

{{{
CREATE FUNCTION MYTABLEFUNC(ID INTEGER, NAME VARCHAR(25))
RETURNS TABLE ( COST DOUBLE, COLOUR VARCHAR(20), AGE SMALLINT)
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'MyFunctions.styleTable';
}}}

=== Query syntax ===

{{{
SELECT * FROM TABLE(MYTABLEFUNC(?, ?)) AS T
}}}

=== Java Method ===
SQL/Part 13 - Sections 8.5/8.6
{{{
public static void styleTable throws SQLException (
    int id, String name,   // IN parameters, standard function support
    double[] cost, String[] colour, short[] age, // OUT parameters for a returned row, SQL standard
    StringBuffer saveDataArea, // the save area data item.
    int callType) // the call type data item
{
 // method body
}
    
}}}
''Some options exist for the Java types of the saveDataArea and callType.''

Calling sequence:

 1. Java method is called once with the callType set to -1 (negative one) indicating the open call.
 1. Java method is called multiple times to fetch rows with the callType set to 0 (zero) indicating a fetch call.
 1. Java method throws SQLException with SQL state 02000 (SQL condition no data) to indicate no more data
 1. Java method is called once with the callType set to 1 (one) indicating the close call.

=== Issues ===

General.

 * Maintaining the table's state from a Java '''static''' method seems hard. The StringBuffer (or the alternative of a String object) is where the state is meant to be stored. Saving object state to and from a StringBuffer on every call seems very expensive. Maybe some scheme of a unique tag saved using the StringBuffer, and then a static HashMap in the class of the method to map the tag into the state. How to generate uniqueness and how to ensure cleanup?

As a VTI replacement

 * The internal VTI code uses interfaces to pass information to and from the object implementing the VTI. These are used for costing (number of expected rows) and pushing select lists & qualifiers into the VTI. With a static method based aproach there is no mechanism (I can see) to perform such logic.
 * No support for INSERT/UPDATE/DELETE