ASA Programming Guide
  Using Java in the Database
    Special features of Java classes in the database

Returning result sets from Java methods

This section describes how to make result sets available from Java methods. You must write a Java method that returns a result set to the calling environment, and wrap this method in a SQL stored procedure declared to be EXTERNAL NAME of LANGUAGE JAVA.

To return result sets from a Java method

1. Ensure that the Java method is declared as public and static in a public class.

2. For each result set you expect the method to return, ensure that the method has a parameter of type java.sql.ResultSet[]. These result set parameters must all occur at the end of the parameter list.

3. In the method, first create an instance of java.sql.ResultSet and then assign it to one of the ResultSet[] parameters.

4. Create a SQL stored procedure of type EXTERNAL NAME LANGUAGE JAVA. This type of procedure is a wrapper around a Java method. You can use a cursor on the SQL procedure result set in the same way as any other procedure that returns result sets.

   For more information about the syntax for stored procedures that are wrappers for Java methods, see CREATE PROCEDURE statement.

Example 

The following simple class has a single method which executes a query and passes the result set back to the calling environment.

import java.sql.*;

public class MyResultSet {
  public static void return_rset( ResultSet[] rset1 )
       throws SQLException {
    Connection conn = DriverManager.getConnection(
                       "jdbc:default:connection" );
    Statement stmt = conn.createStatement();
    ResultSet rset =
      stmt.executeQuery (
                       "SELECT CAST( JName.lastName " +
                       "AS CHAR( 50 ) )" +
                       "FROM jdba.contact " );
    rset1[0] = rset;
  }
}

You can expose the result set using a CREATE PROCEDURE statement that indicates the number of result sets returned from the procedure and the signature of the Java method.

A CREATE PROCEDURE statement indicating a result set could be defined as follows:

CREATE PROCEDURE result_set()
  DYNAMIC RESULT SETS 1
  EXTERNAL NAME
     'MyResultSet.return_rset ([Ljava/sql/ResultSet;)V'
  LANGUAGE JAVA

You can open a cursor on this procedure, just as you can with any ASA procedure returning result sets.

The string (Ljava/sql/ResultSet;)V is a Java method signature which is a compact character representation of the number and type of the parameters and return value.

For more information about Java method signatures, see CREATE PROCEDURE statement.