User's Guide
   PART 3. Java in the Database
     CHAPTER 17. Using Java in the Database       

Returning result sets from Java methods

This section describes how to write a Java method so that it can return result sets to the calling environment, and how to create a SQL stored procedure that exports the result set in a way such that the client application can create a cursor on the result set.

  To return result sets from a Java method:

1. Ensure that the procedure is declared as public and static, in a public class.

2. For each result set that the procedure is to return, ensure that the procedure 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, the procedure must first create an instance of java.sql.ResultSet and then assign it to one of the ResultSet parameters.

4. Create a SQL stored procedure that makes the result set available in a standard fashion. You can then use a cursor on the SQL procedure result set in the standard fashion.

  An 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;
  }
}

The result set is exposed to SQL 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

A cursor can be opened on this procedure just as it can be with any ASA procedure returning result sets.

Java method signatures 

• A Java method signature is compact character representation of the types of the parameters and the type of the return value. For example,

double some_method(
  boolean a,
  int b,
  java.math.BigDecimal c,
  byte [][] d,
  java.sql.ResultSet[] d ) {
}

would have the following signature:

'(ZILjava/math/BigDecimal;[[B[Ljava/sql/ResultSet;)D'

     For information about Java method signatures as used in SQL stored procedures, see CREATE PROCEDURE statement.

  JDBC 1.1 limitations and Interactive SQL

In version 1.1 of the Java Development Kit, JDBC result sets are non-scrollable and non-updatable. Any result sets derived from them are therefore also non-scrollable and non-updatable. A side-effect of this is that these result sets are not accessible through Interactive SQL, because ISQL uses absolute fetches, which are not allowed by a non-scrollable cursor.