Returning result sets

A SQL result set is a sequence of SQL rows that is delivered to the calling environment.

When a Transact-SQL stored procedure returns one or more results sets, those result sets are implicit output from the procedure call. That is, they are not declared as explicit parameters or return values.

Java methods can return Java result set objects, but they do so as explicitly declared method values.

To return a SQL-style result set from a Java method, you must first wrap the Java method in a SQLJ stored procedure. When you call the method as a SQLJ stored procedure, the result sets, which are returned by the Java method as Java result set objects, are transformed by the server to SQL result sets.

When writing the Java method to be invoked as a SQLJ procedure that returns a SQL-style result set, you must specify an additional parameter to the method for each result set that the method can return. Each such parameter is a single-element array of the Java ResultSet class.

This section describes the basic process of writing a method, creating the SQLJ stored procedure, and calling the method. See “Specifying Java method signatures explicitly or implicitly” for more information about returning result sets.

Writing the Java method

The following method, SQLJExamples.orderedEmps, invokes SQL, includes a ResultSet parameter, and uses JDBC calls for securing a connection and opening a statement.

	public static void orderedEmps
			(int regionParm, ResultSet[] rs) throws
			SQLException {


		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			Class.forName
				(“sybase.asejdbc.ASEDriver”);
			Connection conn =
				DriverManager.getConnection
				(“jdbc:default:connection”);
		} 
		catch (Exception e) {
			System.err.println(e.getMessage() 
				+ “:error in connection”);
		}

		try {
			java.sql.PreparedStatement 
				stmt = conn.prepareStatement 
				(“SELECT name, region_of(state)”
				“as region, sales FROM sales_emps”
				“WHERE region_of(state) > ? AND” 
				“sales IS NOT NULL” 
				“ORDER BY sales DESC”);
			stmt.setInt(1, regionParm);
			rs[0] = stmt.executeQuery();
			return;
		}
		catch (SQLException e) {
			System.err.println(“SQLException:” 
			+ e.getErrorCode() + e.getMessage());
		}
		return;
	}

orderedEmps returns a single result set. You can also write methods that return multiple result sets. For each result set returned, you must:

Include a separate ResultSet array parameter in the method signature.
Create a Statement object for each result set.
Assign each result set to the first element of its ResultSet array.

Adaptive Server always returns the current open ResultSet object for each Statement object. When creating Java methods that return result sets:

Create a Statement object for each result set that is to be returned to the client.
Do not explicitly close ResultSet and Statement objects. Adaptive Server closes them automatically.

Adaptive Server ensures that ResultSet and Statement objects are not closed by garbage collection unless and until the affected result sets have been processed and returned to the client.
If some rows of the result set are fetched by calls of the Java next() method, only the remaining rows of the result set are returned to the client.

Creating the SQLJ stored procedure

When you create a SQLJ stored procedure that returns result sets, you must specify the maximum number of result sets that can be returned. In this example, the ranked_emps procedure returns a single result set.

create procedure ranked_emps(region integer)
	dynamic result sets 1
	language java parameter style java
	external name 'SQLJExamples.orderedEmps(int,
		ResultSet[]'

If ranked_emps generates more result sets than are specified by create procedure, a warning displays and the procedure returns only the number of result sets specified. As written, the ranked_emps SQLJ stored procedures matches only one Java method.

Some restrictions apply to method overloading when you infer a method signature involving result sets. See “Mapping Java and SQL datatypes” for more information.

Calling the procedure

After you have installed the method’s class in the database and created the SQLJ stored procedure that references the method, you can call the procedure. You can write the call using any mechanism that processes SQL result sets.

For example, to call the ranked_emps procedure using JDBC, enter the following:

java.sql.CallableStatement stmt =
	conn.prepareCall(“{call ranked_emps(?)}”);
stmt.setInt(1,3);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
	String name = rs.getString(1);
	int.region = rs.getInt(2);
	BigDecimal sales = rs.get.BigDecimal(3);
	System.out.print(“Name = “ + name);
	System.out.print(“Region = “ + region);
	System.out.print(“Sales = “ + sales);
	System.out.printIn():
}

The ranked_emps procedure supplies only the parameter declared in the create procedure statement. The SQL system supplies an empty array of ResultSet parameters and calls the Java method, which assigns the output result set to the array parameter. When the Java method completes, the SQL system returns the result set in the output array element as a SQL result set.

You can return result sets from a temporary table only when using an external JDBC driver such as jConnect. You cannot use the Adaptive Server native JDBC driver for this task.