Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - Programming » SQL Anywhere JDBC API » Using JDBC to access data

Using prepared statements for more efficient access Next Page

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 code. 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" );
Source code available

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

To run the JDBC result set example
  1. Using Interactive SQL, connect to the sample database as the DBA.

  2. Ensure the JDBCExample class has been installed.

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

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

      EXTERNAL NAME 'JDBCExample.Results([Ljava/sql/ResultSet;)V'
  4. Set the following Interactive SQL options so you can see all the results of the query:

    1. From the Tools menu, choose Options.

      The Options dialog appears.

    2. Click Results.

    3. Set the value for Maximum Number of Rows to Display to 5000.

    4. Select Show Multiple Result Sets.

    5. Click OK.

  5. Call the JDBCExample.Results method as follows:

    CALL JDBCResults();
  6. Check each of the three results tabs, Result Set 1, Result Set 2, and Result Set 3.