Usage Notes - Advanced SQL Engine - Teradata Database

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

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.