Usage Notes - Analytics Database - Teradata Vantage

Default Mapping Convention of Parameter Types

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 PROCEDURE or REPLACE PROCEDURE statement.

Consider the following CREATE PROCEDURE statement:

CREATE PROCEDURE NewRegionXSP
  (IN regionID INTEGER)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
EXTERNAL NAME 'JarXSP:region.newRegion';

The parameter list specifies that the SQL data type of regionID is INTEGER. The signature of the newRegion method that implements the external stored procedure looks like this:

public static void newRegion( int regionID ) { ... }

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 NewRegionXSP external stored procedure that takes a CHARACTER(30) value:

CREATE PROCEDURE NewRegionXSP
  (IN regionID CHAR(30))
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
EXTERNAL NAME 'JarXSP:region.newRegion';

Because the CHARACTER type does not map adequately to a Java primitive, the CHARACTER type maps to java.lang.String. The signature of the newRegion method that implements the external stored procedure looks like this:

public static void newRegion( String regionID ) { ... }

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

Overriding the Default Mapping of Parameters

For external stored procedures that allow parameters to pass in or return nulls, simple mapping to Java primitives is not appropriate because they cannot represent NULLs.

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

CLOB and BLOB Type Mapping

CLOB and BLOB SQL types in the parameter list of the CREATE PROCEDURE or REPLACE PROCEDURE statement map to java.sql.Clob and java.sql.Blob classes respectively.

The data access clause in the CREATE PROCEDURE or REPLACE PROCEDURE statement determines which implementing classes of java.sql.Clob and java.sql.Blob are used. (The data access clause indicates whether the external stored procedure executes SQL statements and, if so, whether the statements read or modify SQL data in the database.)

For details on the data access clause, see Executing SQL in Java External Stored Procedures.