Differences between revisions 2 and 3
Revision 2 as of 2006-09-22 22:01:05
Size: 2681
Editor: DanDebrunner
Comment:
Revision 3 as of 2009-09-20 22:11:11
Size: 2681
Editor: localhost
Comment: converted to 1.6 markup
No differences found!

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.
  2. Java method is called multiple times to fetch rows with the callType set to 0 (zero) indicating a fetch call.
  3. Java method throws SQLException with SQL state 02000 (SQL condition no data) to indicate no more data
  4. 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

JavaTableFunctions (last edited 2009-09-20 22:11:11 by localhost)