Returning result sets

This section describes how to make one or more result sets available from Java methods.

You must write a Java method that returns one or more result sets to the calling environment, and wrap this method in a SQL stored procedure. The following code fragment illustrates how multiple result sets can be returned to the caller of this Java procedure. It uses three executeQuery statements to obtain three different result sets.

public static void Results( ResultSet[] rset )
       throws SQLException
{
    // Demonstrate returning multiple result sets

    Connection con = DriverManager.getConnection(
                    "jdbc:default:connection" );
    rset[0] = con.createStatement().executeQuery(
        "SELECT * FROM Employees" +
        "   ORDER BY EmployeeID" );
    rset[1] = con.createStatement().executeQuery(
        "SELECT * FROM Departments" +
        "   ORDER BY DepartmentID" );
    rset[2] = con.createStatement().executeQuery(
        "SELECT i.ID,i.LineID,i.ProductID,i.Quantity," +
        "       s.OrderDate,i.ShipDate," +
        "       s.Region,e.GivenName||' '||e.Surname" +
        "   FROM SalesOrderItems AS i" +
        "   JOIN SalesOrders AS s" +
        "   JOIN Employees AS e" +
        "   WHERE s.ID=i.ID" +
        "        AND s.SalesRepresentative=e.EmployeeID" );
    con.close();
}

Notes

This server-side JDBC example is part of the JDBCExample.java file included in the %SQLANYSAMP12%\SQLAnywhere\JDBC directory.
It obtains a connection to the default running database by using getConnection.
The executeQuery methods return result sets.

Run the JDBC result set example

Using Interactive SQL, connect to the sample database as the DBA.
Ensure the JDBCExample class has been installed.

For more information about installing the Java examples classes, see Preparing for the examples.

Define a stored procedure named JDBCResults that acts as a wrapper for the JDBCExample.Results method in the class:

CREATE PROCEDURE JDBCResults(OUT args LONG VARCHAR)
  DYNAMIC RESULT SETS 3
  EXTERNAL NAME 'JDBCExample.Results([Ljava/sql/ResultSet;)V'
  LANGUAGE JAVA;

Set the following Interactive SQL options so you can see all the results of the query:
1. Click Tools » Options.
2. Click SQL Anywhere.
3. Click the Results tab.
4. Set the value for Maximum Number Of Rows To Display to 5000.
5. Click Show All Result Sets.
6. Click OK.
Call the JDBCExample.Results method as follows:
CALL JDBCResults();
Check each of the three results tabs, Result Set 1, Result Set 2, and Result Set 3.

Discuss this page in DocCommentXchange.