External Body Reference Clause - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The mandatory external body reference clause both declares that the external procedure is external to the 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.

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.
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 and Teradata Vantage™ - SQL External Routine Programming, B035-1147).

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.

You cannot specify the F option in string without also specifying an include, library, object, package, or source file name. The 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 and Teradata Vantage™ - SQL External Routine Programming, B035-1147.

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. For more information, see Parameter Style Clause .