17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

Java Method Signature

The signature for a Java method that implements the external stored procedure includes the IN, INOUT, and OUT parameters, followed by n ResultSet[] output parameters, where n is the number of result sets specified in the DYNAMIC RESULT SETS clause.

Implementation

To return a result set, a Java external stored procedure establishes a connection to Vantage using the default connection of the JDBC driver, executes one of the SQL statements that produces a result set, and uses Statement.getResultSet() to get the result set and return it using one of the ResultSet[] output parameters.

The Java external stored procedure must not close the Statement object that it uses to get the result set.

A Java external stored procedure can return as many result sets as specified by the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE or REPLACE PROCEDURE statement. It is not mandatory that the external stored procedure return any result sets, however. A null or uninitialized result set output parameter indicates none is returned.

A Java external stored procedure can position each result set to a specific row. For example, an external stored procedure that returns three result sets can position one at the third row, one at the n-1 row, and one at the beginning.

Restrictions

The following restrictions apply to an external stored procedure that returns result sets:
  • A statement for which an external stored procedure returns a result set cannot be part of a multistatement request.
  • Inline reading of BLOB or CLOB data is not supported from a result set.
  • A calling Java external stored procedure cannot return the result set created by a called stored procedure to the caller of the Java external stored procedure. It can only consume the data. (For details on consuming result sets, see Consuming Result Sets Created by Calling a Stored Procedure.)

Performance and Resource Considerations

Creating result sets uses additional time and resources that you must consider. Because the environment (including character set and host) of the caller may differ from the environment of the external stored procedure, the database must generate two spool files for each statement that an external stored procedure creates a result set for.