Supported Java Data Types | External Routines | Teradata Vantage - Supported Types - Analytics Database - Teradata Vantage

This section identifies the SQL data types that are supported for parameters of Java UDFs and external stored procedures and how the SQL data types map to Java data types.

The default parameter mapping convention is simple mapping, where SQL data types map to Java primitives (identified by Simple Map). When an appropriate primitive does not exist, the SQL data types map to Java classes (identified by Object Map).

For UDFs or external stored procedures that allow parameters to pass in or return nulls, simple mapping to Java primitives is not appropriate.

For some types, more than one type of mapping is available. The default mapping is identified along with the additional mappings. The Java external routine writer can choose the Java data type to which the SQL type will be mapped depending on the requirement.

To override the default mapping, the EXTERNAL NAME clause in the CREATE/REPLACE FUNCTION or CREATE/REPLACE PROCEDURE statement must explicitly specify the mapping in the parameter list of the Java method.

Example: Distinct UDT with Default Mapping

In this example, the Java routine declaration in the EXTERNAL NAME clause does not include the parameter list. Therefore, simple mapping (the default) is used for the MyInt UDT.

CREATE TYPE MYINT AS INTEGER FINAL;

REPLACE FUNCTION MyScore(A1 MYINT)
RETURNS MYINT
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.myScore';

public static int myScore(int a) throws SQLException

Example: ST_Geometry with Nondefault Mapping

This example shows a Java UDF with an ST_Geometry parameter using a nondefault mapping to an ST_Geometry class.

REPLACE FUNCTION get_Geom(G1 ST_GEOMETRY)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.get_geom(com.teradata.fnc.ST_Geometry) returns int';

public static int get_geom(com.teradata.fnc.ST_Geometry g1)
   throws SQLException

ARRAY/VARRAY

The ARRAY/VARRAY SQL data type maps to the following Java data type.

The com.teradata.fnc.Array class implements the java.sql.Array interface.

Both 1-D and N-D arrays are supported as parameters and return types for Java external stored procedures and UDFs.

BIGINT

The BIGINT SQL data type maps to the following Java data types.

CREATE PROCEDURE F1 (IN A BIGINT, OUT B BIGINT)
LANGUAGE JAVA
 ...;

public static void f1(long a, long[] b)
{ ... }

CREATE PROCEDURE F1 (IN A BIGINT, OUT B BIGINT)
LANGUAGE JAVA
 ...
EXTERNAL NAME 'j1:c1.f1(java.lang.Long, java.lang.Long[])';

public static void f1(Long a, Long[] b)
{ ... }

BLOB (Binary Large Object)

The BLOB SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A BLOB, OUT B BLOB)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Blob a, java.sql.Blob[] b)
{ ... }

BYTE

The BYTE SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A BYTE[30], OUT B BYTE[30])
LANGUAGE JAVA
 ...;

public static void f1(byte[] a, byte[][] b)
{ ... }

BYTEINT

The BYTEINT SQL data type maps to the following Java data types.

CREATE PROCEDURE F1 (IN A BYTEINT, OUT B BYTEINT)
LANGUAGE JAVA
 ...;

public static void f1(byte a, byte[] b)
{ ... }

CREATE PROCEDURE F1 (IN A BYTEINT, OUT B BYTEINT)
LANGUAGE JAVA
 ...
EXTERNAL NAME 'j1:c1.f1(java.lang.Byte, java.lang.Byte[])';

public static void f1(Byte a, Byte[] b)
{ ... }

CHARACTER/CHAR

The CHARACTER SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A CHAR(30), OUT B CHAR(30))
LANGUAGE JAVA
 ...;

public static void f1(String a, String[] b)
{ ... }

CLOB (Character Large Object)

The CLOB SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A CLOB, OUT B CLOB)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Clob a, java.sql.Clob[] b)
{ ... }

DATASET

The DATASET SQL data type maps to the following Java data types.

For DATASET types in the Avro storage format, the default mapping is to java.sql.Blob. An additional mapping is provided to map the DATASET type to byte[]. You can use this mapping when the DATASET size will not exceed 64K. This can provide better performance than LOB-based parameter mappings.

The DATASET type in the Avro storage format is converted into its transform format, which is the schema defined for the instance, encoded in UTF-8 and null-terminated, followed by the binary-encoded Avro value.

For DATASET types in the CSV storage format, the default mapping is to java.sql.Clob. An additional mapping is provided to map the DATASET type to java.lang.String. You can use this mapping when the DATASET size will not exceed 64K. This can provide better performance than LOB-based parameter mappings.

Note that when DATASET types in the CSV storage format are mapped to java.sql.Clob or java.lang.String, only the CSV data is included, and not any optional schema.

CREATE PROCEDURE F1 (IN A DATASET(8000) Storage Format Avro, 
                     OUT B DATASET(8000) Storage Format Avro)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Blob a, java.sql.Blob[] b)
{ ... }

DATE

The DATE SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A DATE, OUT B DATE)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Date a, java.sql.Date[] b)
{ ... }

DECIMAL/NUMERIC

The DECIMAL/NUMERIC SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A DECIMAL(8,2), OUT B DECIMAL(8,2))
LANGUAGE JAVA
 ...;

public static void f1(BigDecimal a, BigDecimal[] b)
{ ... }

FLOAT / DOUBLE PRECISION / REAL

The FLOAT / DOUBLE PRECISION / REAL SQL data type maps to the following Java data types.

CREATE PROCEDURE F1 (IN A FLOAT, OUT B FLOAT)
LANGUAGE JAVA
 ...;

public static void f1(double a, double[] b)
{ ... }

CREATE PROCEDURE F1 (IN A FLOAT, OUT B FLOAT)
LANGUAGE JAVA
 ...
EXTERNAL NAME 'j1:c1.f1(java.lang.Double, java.lang.Double[])';

public static void f1(Double a, Double[] b)
{ ... }

Geospatial Data Type: MBB

The Geospatial MBB SQL data type maps to the following Java data types.

The MBB type maps to java.lang.String by default. The format is (Xmin,Ymin,Zmin,Xmax,Ymax,Zmax).

An additional mapping is provided to map MBB to a primitive double array, double[]. The array would contain 6 double values that correspond to the Xmin, Ymin, Zmin, Xmax, Ymax, and Zmax values.

When the MBB type is a structured UDT attribute, the default mapping (java.lang.String) will apply. The MBB type can be at any nested level.

Geospatial Data Type: MBR

The Geospatial MBR SQL data type maps to the following Java data types.

The MBR type is mapped to java.lang.String by default. The format is (Xmin,Ymin,Xmax,Ymax).

An additional mapping is provided to map MBR to a primitive double array, double[]. The array would contain 4 double values that correspond to the Xmin, Ymin, Xmax, and Ymax values.

When the MBR type is a structured UDT attribute, the default mapping (java.lang.String) will apply. The MBR type can be at any nested level.

Geospatial Data Type: ST_Geometry

The Geospatial ST_Geometry SQL data type maps to the following Java data types.

The ST_Geometry type is converted to its Well-Known Text (WKT) representation and passed as a Clob to the Java routine. The Clob value passed to the Java routine will not contain the spatial reference identifier (SRID) that is contained within a geometry object.

A mapping is provided to map the ST_Geometry type to java.sql.Blob. The Geospatial type is converted to its Well-Known Binary (WKB) representation and passed as a Blob to the Java routine. The Blob will not contain the SRID that is contained within the geometry object.

A mapping is provided to map the ST_Geometry type to the com.teradata.fnc.ST_Geometry class. The ST_Geometry object can be used to retrieve the WKT, WKB, and SRID of the geometry value or set the geometry value to a WKT or WKB value (with supplied SRID). Note that this is the only mapping that can read or set the SRID value.

Non-LOB-based mappings are provided to java.lang.String (WKT representation in LATIN character set) and byte[] (WKB representation). These mappings can be used when it is known that the ST_Geometry parameter size will not exceed 64K. This may provide better performance than LOB-based parameter mappings.

Structured UDTs with ST_Geometry attributes cannot be passed to Java UDFs or external stored procedures.

This example shows a Java UDF with an ST_Geometry parameter using a nondefault mapping to an ST_Geometry class.

REPLACE FUNCTION get_Geom(G1 ST_GEOMETRY)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.get_geom(com.teradata.fnc.ST_Geometry) returns int';

public static int get_geom(com.teradata.fnc.ST_Geometry g1)
   throws SQLException

INTEGER

The INTEGER SQL data type maps to the following Java data types.

CREATE PROCEDURE F1 (IN A INTEGER, OUT B INTEGER)
LANGUAGE JAVA
 ...;

public static void f1(int a, int[] b)
{ ... }

CREATE PROCEDURE F1 (IN A INTEGER, OUT B INTEGER)
LANGUAGE JAVA
 ...
EXTERNAL NAME 'j1:c1.f1(java.lang.Integer, java.lang.Integer[])';

public static void f1(Integer a, Integer[] b)
{ ... }

Interval SQL Data Types

CREATE PROCEDURE F1 (IN A INTERVAL MONTH, OUT B INTERVAL MONTH)
LANGUAGE JAVA
 ...;

public static void f1(String a, String[] b)
{ ... }

JSON

The JSON SQL data type maps to the following Java data types.

The default mapping is to java.sql.Clob.

A mapping to java.sql.Blob is provided that will send the data in binary JSON format.

Non-LOB-based mappings are provided to java.lang.String (text representation) and byte[] (BSON format). These mappings can be used when it is known that the JSON parameter size will not exceed 64K. This may provide better performance than LOB-based parameter mappings.

Structured UDTs with JSON attributes are allowed as parameters to Java UDFs at any nested level. The JSON value will be mapped as an inline value to a String or a byte[]. Lob mappings are not supported.

CREATE PROCEDURE F1 (IN A JSON(20) CHARACTER SET LATIN, OUT B JSON(40) CHARACTER SET LATIN)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Clob a, java.sql.Clob[] b)
{ ... }

NUMBER

The NUMBER SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (INOUT  A NUMBER(30,2))
LANGUAGE JAVA
 ...;

public static void f1(BigDecimal[] a)
{ ... }

Period

Period SQL data types map to the following Java data type.

A Period data type is mapped to java.sql.Struct. The com.teradata.fnc.Struct class implements the java.sql.Struct interface.

The Period Struct objects are constructed as follows:

java.sql.Struct{
   java.sql.Date,
   java.sql.Date
}

java.sql.Struct{
   java.sql.Time,
   java.sql.Time
}

java.sql.Struct{
   java.sql.Struct{
      java.sql.Time, 
      java.util.Calendar,
   }
   java.sql.Struct{
      java.sql.Time, 
      java.util.Calendar
   }
}

java.sql.Struct{
   java.sql.Timestamp,
   java.sql.Timestamp
}

java.sql.Struct{
   java.sql.Struct{
      java.sql.Timestamp, 
      java.util.Calendar,
   }
   java.sql.Struct{
      java.sql.Timestamp, 
      java.util.Calendar
   }	
}

For Period(Time With Time Zone) and Period(Timestamp With Time Zone), the Time or Timestamp object in the Struct stores the local time of the input Time/Timestamp value. The Calendar object stores only the time zone part of the input Time/Timestamp value. Other information such as hour, minute, second, and so forth is not set in the Calendar object based on the input Time/Timestamp value. Instead this information is set to the default local time when the Calendar object is created.

Period types can be attributes of a structured UDT at any level.

SMALLINT

The SMALLINT SQL data type maps to the following Java data types.

CREATE PROCEDURE F1 (IN A SMALLINT, OUT B SMALLINT)
LANGUAGE JAVA
 ...;

public static void f1(short a, short[] b)
{ ... }

CREATE PROCEDURE F1 (IN A SMALLINT, OUT B SMALLINT)
LANGUAGE JAVA
 ...
EXTERNAL NAME 'j1:c1.f1(java.lang.Short, java.lang.Short[])';

public static void f1(Short a, Short[] b)
{ ... }

TD_ANYTYPE

The TD_ANYTYPE SQL data type maps to the following Java data type.

User-defined types (UDTs) and complex data types (CDTs) can be passed to a Java UDF or external stored procedure with TD_ANYTYPE parameters. These will be mapped to java.lang.Object in the Java routine. The object type will always correspond to the default mapping type. For instance, if a JSON value is passed to a Java UDF with a TD_ANYTYPE parameter, the Java routine will receive a java.sql.Clob object.

CREATE PROCEDURE F1 (IN A TD_ANYTYPE, INOUT B TD_ANYTYPE)
LANGUAGE JAVA
 ...;

public static void f1(java.lang.Object a, java.lang.Object[] b)
{ ... }

TIME

The TIME SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A TIME, OUT B TIME)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Time a, java.sql.Time[] b)
{ ... }

TIME WITH TIME ZONE

The TIME WITH TIME ZONE SQL data type maps to the following Java data types.

A mapping is provided which maps TIME WITH TIME ZONE to java.sql.Struct. Mapping to java.sql.Struct allows the time zone value to be passed to the Java routine. The com.teradata.fnc.Struct class implements the java.sql.Struct interface.

Time with Time Zone
   java.sql.Struct{
      java.sql.Time,
      java.util.Calendar
   }

The Time object in the Struct stores the local time of the input Time value. The Calendar object stores only the time zone part of the input Time value. Other information such as hour, minute, second, and so forth is not set in the Calendar object based on the input Time value. Instead this information is set to the default local time when the Calendar object is created.

If the TIME WITH TIME ZONE type is a structured UDT attribute or an Array element type, then the default mapping (java.sql.Time) will apply.

CREATE PROCEDURE F1 (IN A TIME WITH TIME ZONE, OUT B TIME WITH TIME ZONE)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Struct a, java.sql.Struct[] b)
{ ... }

TIMESTAMP

The TIMESTAMP SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A TIMESTAMP, OUT B TIMESTAMP)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Timestamp a, java.sql.Timestamp[] b)
{ ... }

TIMESTAMP WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE SQL data type maps to the following Java data types.

The TIMESTAMP WITH TIME ZONE type maps to java.sql.Timestamp by default. This mapping does not preserve the time zone offset.

A mapping is provided which maps TIMESTAMP WITH TIME ZONE to java.sql.Struct. Mapping to java.sql.Struct allows the time zone value to be passed to the Java routine. The com.teradata.fnc.Struct class implements the java.sql.Struct interface.

Timestamp with Time Zone
   java.sql.Struct{
      java.sql.Timestamp,
      java.util.Calendar
   }

The Timestamp object in the Struct stores the local time of the input Timestamp value. The Calendar object stores only the time zone part of the input Timestamp value. Other information such as hour, minute, second, and so forth is not set in the Calendar object based on the input Timestamp value. Instead this information is set to the default local time when the Calendar object is created.

Struct{
   java.sql.Timestamp;
   java.util.Calendar;
}

The Timestamp object will be "2001-02-05 05:13:11.207000" of the local time based on the JVM default time zone. The Calendar object will be "GMT-00:30".

If the TIMESTAMP WITH TIME ZONE type is a structured UDT attribute or an Array element type, then the default mapping (java.sql.Timestamp) will apply.

CREATE PROCEDURE F1 (IN A TIMESTAMP WITH TIME ZONE, OUT B TIMESTAMP WITH TIME ZONE)
LANGUAGE JAVA
 ...;

public static void f1(java.sql.Struct a, java.sql.Struct[] b)
{ ... }

UDT (Distinct)

The distinct UDT SQL data type maps to the following Java data types.

A distinct UDT is mapped to the Java type (simple or object mapping) corresponding to its predefined type. The distinct UDT is converted to its predefined type before being passed to the Java routine.

The default mapping for a distinct UDT is the simple mapping to the primitive Java data type. That is, if the Java parameter declaration is not specified in the EXTERNAL NAME clause in the CREATE FUNCTION or CREATE PROCEDURE statement, then the UDT distinct types are mapped to the primitive Java data types. Otherwise the mapping is done based on the parameter type in the declaration.

When a distinct UDT is a structured UDT attribute or an Array element type, it will be object mapped to the corresponding Java data type.

In this example, the Java routine declaration in the EXTERNAL NAME clause does not include the parameter list. Therefore, simple mapping (the default) is used for the MyInt UDT.

CREATE TYPE MYINT AS INTEGER FINAL;

REPLACE FUNCTION MyScore(A1 MYINT)
RETURNS MYINT
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.myScore';

public static int myScore(int a) throws SQLException

UDT (Structured)

The structured UDT SQL data type maps to the following Java data type.

A structured UDT is mapped to java.sql.Struct. The com.teradata.fnc.Struct class implements the java.sql.Struct interface.

Structured UDTs with LOBs at a nested level are not supported. LOBs are only allowed at the first level of nesting.

Structured UDTs with XML or ST_Geometry attributes are not supported.

VARBYTE

The VARBYTE SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A VARBYTE(30), OUT B VARBYTE(30))
LANGUAGE JAVA
 ...;

public static void f1(byte[] a, byte[][] b)
{ ... }

VARCHAR / CHARACTER VARYING / CHAR VARYING / LONG VARCHAR

The VARCHAR SQL data type maps to the following Java data type.

CREATE PROCEDURE F1 (IN A VARCHAR(30), OUT B VARCHAR(30))
LANGUAGE JAVA
 ...;

public static void f1(String a, String[] b)
{ ... }

XML

The XML SQL data type maps to the following Java data types.

When mapped to java.sql.SQLXML, the XML type is handled as a CLOB-based type by the Java environment. The com.teradata.fnc.SQLXML class implements the java.sql.SQLXML interface.

A mapping to java.lang.String is provided that will send the XML string in UNICODE. The mapping can be used when the size of the XML value is less than 64K.

Mappings are provided to java.sql.Blob and byte[] which send the XML data in UTF-8 format.

Structured UDTs with XML attributes cannot be passed to Java UDFs or external stored procedures.