Parameter List | Java External Stored Procedures | Teradata Vantage - Parameter List - Advanced SQL Engine - Teradata Database

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

Synopsis

public class  class_name  {

   ...

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

where:

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

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.