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 calling SQL script. 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();
}

Source code available

This code fragment is part of the JDBCExample Java file included in the samples-dir\SQLAnywhere\JDBC directory.

Notes

This server-side JDBC example connects to the default running database using the current connection using getConnection.
The executeQuery methods return result sets.

Run the JDBC result set example

To 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()
  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. From the Tools menu, choose Options.
2. Click SQL Anywhere.
3. Click the Results tab.
4. Set the value for Maximum Number Of Rows To Display to 5000.
5. Select 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.