16.20 - Returning Dynamic Result Sets - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

If the CREATE PROCEDURE or REPLACE PROCEDURE statement for the external stored procedure specifies the DYNAMIC RESULT SETS clause, the external stored procedure can return result sets to the client application or to the caller of the external stored procedure (in addition to consuming the result sets itself) upon completion of the external stored procedure.

A result set is a set of rows that is the result of any of the following statements that the external stored procedure executes:

  • SELECT
  • HELP TABLE
  • HELP VIEW
  • HELP MACRO
  • SHOW TABLE
  • SHOW VIEW
  • SHOW MACRO
  • COMMENT

An external stored procedure can return up to 15 result sets, depending on the specification of the DYNAMIC RESULT SETS clause.

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 Teradata Database 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.

Example: Returning Two Result Sets

Here is an example of a method that returns two result sets:

/**************************************************************
   DDL for the Java external stored procedure:

   REPLACE PROCEDURE UsrCmd(  Command VARCHAR(120) )
      LANGUAGE JAVA 
      READS SQL DATA
      PARAMETER STYLE JAVA
      DYNAMIC RESULT SETS 2
      EXTERNAL NAME 'jUdfRs:jUdfRsExamples.UsrCmd';

 **************************************************************/

public class jUdfRsExamples {
   public static void UsrCmd(String Command,
                             ResultSet[] rs1,
                             ResultSet[] rs2) throws SQLException {
      Connection con = 
         DriverManager.getConnection( "jdbc:default:connection" );
      Statement stmt =          con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);

      /* Execute user's command. */
      if (stmt.execute( Command )) {
         /* Return the result set. */
         rs1[0] = stmt.getResultSet();
      }
      if ( stmt.getMoreResults(Statement.KEEP_CURRENT_RESULT)) {
         /* Get a second result set if needed. */
         rs2[0] = stmt.getResultSet();
      }
   }
   ...
}

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.