16.20 - Parameter List - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

The list of parameters in a Java method for an external stored procedure includes the IN, OUT, and INOUT parameters that are specified when the external procedure appears in a CALL statement.

An external procedure can have 0 to 255 input/output parameters.

The Java data types that you use as input/output parameters map to SQL data types in the procedure definition (CREATE PROCEDURE or REPLACE PROCEDURE) and invocation (CALL).

A Java external procedure cannot have an ARRAY type parameter where the base type is a nested structured UDT.

Synopsis

public class  class_name  {

   ...

   public static void  method_name ( type_1      *in_out_parameter_1,
                                    ...,
                                    type_n      *in_out_parameter_n  )
   {
        ...
   }
}

where:

Parameter … Specifies …
type_n * in_out_parameter_n the input/output parameters corresponding to the IN, OUT, or INOUT arguments, where n is the number of parameters in the CREATE PROCEDURE definition. If n = 0, no input/output parameters appear. The type is a Java primitive or class corresponding to the SQL data type of the IN, OUT, or INOUT argument.

The maximum number of input/output parameters is 255.

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.

Example: Using Default Mapping of Parameter Types

Here is a code excerpt that shows how to implement a Java method for an external stored procedure that maps an SQL INTEGER type parameter to the Java int primitive:

public class region {

   public static void newRegion( int[] regionID )
   {
       regionID[0] += 1;
   }

   ...

}

If the JAR file for the region class is called region.jar, the following statement registers region.jar and the region class with the database, and creates an SQL identifier called JarXSP for the JAR file:

CALL SQLJ.INSTALL_JAR('CJ!C:\xspsrc\region.jar','JarXSP',0);

The corresponding CREATE PROCEDURE statement to define the external stored procedure looks like this :

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

Example: Overriding the Default Mapping of Parameter Types

Here is a code excerpt that shows how to implement a Java method for an external stored procedure that maps an SQL INTEGER type parameter to the java.lang.Integer class:

public class region {

   public static void newRegion( Integer[] regionID )
   {
       if (regionID[0] != null)
          regionID[0] = regionID[0].intValue() + 1;
   }

   ...

}

If the JAR file for the region class is called region.jar, the following statement registers region.jar and the region class with the database, and creates an SQL identifier called JarXSP for the JAR file:

CALL SQLJ.INSTALL_JAR('CJ!C:\xspsrc\region.jar','JarXSP',0);

The corresponding CREATE PROCEDURE statement to define the external stored procedure looks like this :

CREATE PROCEDURE NewRegionXSP
  (INOUT regionID INTEGER)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
EXTERNAL NAME 'JarXSP:region.newRegion(java.lang.Integer[])';

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.)

IF the SQL type is … AND the data access clause is … THEN the Java implementing class is …
BLOB NO SQL com.teradata.fnc.Blob.

For details, see com.teradata.fnc.Blob.

CONTAINS SQL com.teradata.jdbc.jdbc_4.Blob.

For details, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html .

READS SQL DATA
MODIFIES SQL DATA
CLOB NO SQL com.teradata.fnc.Clob.

For details, see com.teradata.fnc.Clob.

CONTAINS SQL com.teradata.jdbc.jdbc_4.Clob.

For details, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html .

READS SQL DATA
MODIFIES SQL DATA

For details on the data access clause, see "Limiting the Types of SQL Statements that a Procedure Executes" in Executing SQL in Java External Stored Procedures.