How to Return Result Sets from Java Methods

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.

Perform the following tasks to return result sets from a Java method:

Ensure that the Java method is declared as public and static in a public class.
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.
In the method, first create an instance of java.sql.ResultSet and then assign it to one of the ResultSet[] parameters.
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.

Example

The following simple class has a single method that 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 Surname " +
                       "FROM Customers" );
    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()
  RESULT (SurName person_name_t)
  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 SAP Sybase IQ procedure returning result sets.

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