17.10 - SQL Definition - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

The default parameter mapping convention for mapping SQL data types to Java data types is simple mapping, where SQL data types map to Java primitives. In this example, the EXTERNAL NAME string does not specify the parameter types, so simple mapping is assumed. The corresponding Java function should also use simple mapping as shown in the subsequent Java Implementation section.

DATABASE JUDF;
CREATE FUNCTION extract_field(Text VARCHAR(32000),
                              From_Store INTEGER)
   RETURNS TABLE (Customer_ID INTEGER,
                  Store_ID INTEGER,
                  Item_ID INTEGER)
   LANGUAGE JAVA
   NO SQL
   PARAMETER STYLE JAVA
   EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.extract_field';

This example shows the SQL definition for a table function with object mapping:

DATABASE JUDF;
CREATE FUNCTION extract_field(Text VARCHAR(32000),
                              From_Store INTEGER)
   RETURNS TABLE (Customer_ID INTEGER,
                  Store_ID INTEGER,
                  Item_ID INTEGER)
   LANGUAGE JAVA
   NO SQL
   PARAMETER STYLE JAVA
   EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.extract_field(java.lang.String, 
java.lang.Integer, java.lang.Integer[], java.lang.Integer[], java.lang.Integer[]';

The corresponding Java UDF definition is:

public static void extract_field(java.lang.String textstr,
                                 java.lang.Integer from_store,
                                 java.lang.Integer[] cust_id,
                                 java.lang.Integer[] store_id,
                                 java.lang.Integer[] item_id)
{
   ...
}

For details about simple mapping and object mapping, see Java Data Types.