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.

As a VTI replacement

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