17.10 - Example: Returning Two Result Sets - 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)

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();
      }
   }
   ...
}