15.00 - External Body Reference Clause - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

External Body Reference Clause

The mandatory external body reference clause both declares that the external procedure is external to Teradata Database and identifies the location of all the file components it needs to be able to execute.

If an external procedure name alone is not sufficient to specify the location of all the procedure components, then you must also specify a string literal that explicitly specifies the path to each of those elements (see “External String Literal” on page 442).

In all cases, procedure_name is the identifier you specify when you invoke the procedure from SQL requests.

You can optionally specify either or both of the following options for this clause.

  • External procedure name
  • Parameter style
  • You can specify an external procedure name in several different ways. See the following for details.

  • “External String Literal” on page 276
  • “Include Name Clause” on page 279, “Library Name Clause” on page 280, “Object File Name Clause” on page 281, “Package Name Clause” on page 282, and “Source File Name Clause” on page 283
  • For Java procedures, the external string literal specifies the JAR file, the Java class within that JAR, and the Java method within the class to be invoked when the system executes the external routine.

  • The first part of the string literal specification is the JAR name or ID. This is the registered name of the associated JAR file. The name is created or specified with the external procedure SQLJ.Install_Jar.
  • The second part of the string literal specification, which you specify after the colon, is the name of the Java class contained within the JAR file that contains the Java method to execute.
  • The third part of the string literal specification is the name of the method that is executed.
  • For example, suppose you have a JAR file that has been registered in the database with the name salesreports, the class within the JAR is named formal, and the method within the class to be called is named monthend.

    This location specification for the JAR, Java class, and the Java class method is completely specified by the following external string literal.

         'salesreports:formal.monthend'

    The Java method name must be unique within the database in which you are creating this Java external procedure.

    You can also specify a Java parameter declaration list in the external body reference to specify the signature of a particular implementation of a Java method. This specification is mandatory when the system must choose among several possible implementations of the Java method. The Java parameters list specifies a comma‑separated list of Java data types (see “Data Type Mapping Between SQL and Java” on page 433 and SQL External Routine Programming).

    Note the following about Java data type names.

  • They are case sensitive
  • They can be fully qualified by a package name
  • The following table encapsulates the various specification possibilities of this clause for an external routine written in the C or C++ languages.

     

    IF CREATE PROCEDURE specifies this clause …

    THEN …

    EXTERNAL

    the C or C++ procedure name must match the name that follows the CREATE PROCEDURE keywords.

    Consider the following CREATE PROCEDURE statement.

        CREATE PROCEDURE GetRegionXSP
          (INOUT region VARCHAR(64))
        LANGUAGE C
        NO SQL
        EXTERNAL
        PARAMETER STYLE TD_GENERAL;

    The C procedure name must be GetRegionXSP.

    If the client is mainframe-attached, then the C or C++ function name must be the DDNAME for the source.

    EXTERNAL NAME external_procedure_name

    the C or C++ procedure name must match procedure_name.

    Consider the following CREATE PROCEDURE statement.

        CREATE PROCEDURE GetRegionXSP
          (INOUT region VARCHAR(64))
        LANGUAGE C
        NO SQL
        EXTERNAL NAME xsp_getregion
        PARAMETER STYLE TD_GENERAL;

    The C procedure name must be xsp_getregion.

    If the client is mainframe-attached, then procedure_name must be the DDNAME for the source.

    EXTERNAL NAME 'string'

    'string' can specify the C or C++ procedure name by stipulating the F option.

    For a Java external procedure, you must specify an external Java reference string rather than an EXTERNAL NAME ‘string’ value (see the NAME external_Java_reference_ string parameter in SQL Data Definition Language).

    You cannot specify the F option in ‘string’ without also specifying an include, library, object, package, or source file name. The Teradata Database needs 1 or more of these file names to link to.

    Consider the following CREATE PROCEDURE statement.

        CREATE PROCEDURE GetRegionXSP
          (INOUT region VARCHAR(64))
        LANGUAGE C
        NO SQL
        EXTERNAL NAME 'CS!getregion!xspsrc/      getregion.c!F!xsp_getregion'
        PARAMETER STYLE TD_GENERAL;

    The C function name must be xsp_getregion.

    If 'string' does not include the F option, then the C/C++/Java procedure name must match the procedure name specified with the CREATE PROCEDURE statement.

    To adapt these examples for Java external procedures, you must specify a JAR reference string in place of the specification forms used for C and C++ external procedures.

    For example, consider the following procedure definition for a Java external procedure.

         CREATE PROCEDURE myclobdiagret2 (IN  b CLOB)
         LANGUAGE JAVA
         MODIFIES SQL DATA
         PARAMETER STYLE JAVA
         EXTERNAL NAME 'user_jar:UnitTest.myclobdiagret2';

    The EXTERNAL NAME clause for this example specifies a JAR name, package name, and a Java class name, respectively, rather than an delimited function entry name, C or C++ library name, and so on. See “External Java Reference Strings” on page 444 and SQL External Routine Programming for details.

    You can specify the parameter style for the external procedure either in this clause or in the Optional Function Characteristics clause, but you can only specify the parameter style for an external procedure 1 time in its definition. See “Parameter Style Clause” on page 271 for more information.