17.10 - Default Mapping Convention of Parameter Types - 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 data types that you use in the parameter list of the Java method map to the SQL data types in the parameter list of the CREATE FUNCTION or REPLACE FUNCTION statement.

Consider the following CREATE FUNCTION statement for a scalar UDF:

CREATE FUNCTION factorial
  (x INTEGER)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'JarUDF:UDFExample.fact';

The parameter list specifies that the SQL data type of x is INTEGER. The signature of the fact method that implements the UDF looks like this:

public static int fact( int x ) { ... }

The default mapping convention is simple mapping, where SQL data types map to Java primitives. If no Java primitive can adequately map to an SQL type, then the default mapping convention is object mapping, where SQL data types map to Java classes.

Consider a factorial UDF that takes a DECIMAL value:

CREATE FUNCTION factorial
  (x DECIMAL(8,2))
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'JarUDF:UDFExample.fact';

Because the DECIMAL type does not map adequately to a Java primitive, the DECIMAL type maps to java.math.BigDecimal. The signature of the fact method that implements the UDF looks like this:

public static int fact( BigDecimal x ) { ... }

For details on how SQL data types map to Java data types, see SQL Data Type Mapping.