Chapter 27 Sending Result Sets

Sending result sets with Java

Java components send results sets with the interfaces in the com.sybase.jaguar.sql package:

Methods in the JServerResultSetMetaData interface define the format of rows in a result set.
Methods in the JServerResultSet interface define column values for rows in a result set and send the rows to the client.

The JContext class contains static factory methods to return objects that implement these interfaces.

Chapter 1, "Java Classes and Interfaces" of the Jaguar CTS API Reference contains reference pages for all classes and interfaces.

You cannot send a result set unless the IDL definition of the component method returns TabularResults::ResultSet or TabularResults::ResultSets.

Methods in Java components that use Java/IDL datatypes must be declared to return TabularResults.ResultSet or TabularResults.ResultSet if the method returns result sets. However, you can still use the JServerResultSetMetaData and JServerResultSet interfaces to implicitly return results. Just return null as the method's return value. Alternatively, you can construct the equivalent Java datatypes for the IDL TabularResults::ResultSet and TabularResults::ResultSets types. Call the getResultSet method in the class com.sybase.CORBA.jdbc11.IDL to convert a java.sql.ResultSet instance into a TabularResults.ResultSet instance that can be returned by the method.

Forwarding a ResultSet object

You can use the steps below to forward results from a JDBC query directly to the client:

Query the remote server. Use java.sql.Statement or one of its extensions; the appropriate method depends on the query being sent.
Handle the results of the query. For each ResultSet returned by the query, call JContext.forwardResultSet(ResultSet) to forward the rows to the client.
If your component uses IDL/Java datatypes, return null as the method's return value.

For an example that calls JContext.forwardResultSet(ResultSet), see "Java Connection Manager example". You can find more examples in the source for the Jaguar sample Java components, available in your Jaguar installation directory.

Instead of calling JContext.forwardResultSet(ResultSet), Java components that use IDL/Java datatypes can call the IDL.getResultSet(java.sql.ResultSet) method to convert ResultSet object to TabularResults.ResultSet object, then return the converted object as the method's return value.

Sending results row-by-row

Use the sequence of calls below to define and send a result set row-by-row. Use these calls when building a result set from a non-JDBC source, or when the java.sql.ResultSet returned by a database query cannot be sent as-is to the client.

JServerResultSet sequence of calls

Here are the calls to construct a result set and send it row-by-row:

Create a JServerResultSetMetaData object by calling JContext.createServerResultSetMetaData().
Call the JServerResultSetMetaData methods to define the format of the result rows, as follows:
1. For each column, call JServerResultSetMetaData.setColumnType(int, int) to specify the datatype.
2. For columns that have a variable length datatype, call JServerResultSetMetaData.setColumnDisplaySize(int, int) to specify the maximum length for column values.
3. Call other JServerResultSetMetaData methods to specify other column attributes as needed.
Create a JServerResultSet object by calling JContext.createServerResultSet().
Call JServerResultSet.next() to position the result set's cursor at the first row.
For each row to be sent:
- For each column, call the appropriate JServerResultSet.set<Object>(int, <Object>) method to set the column value.
- Call JServerResultSet.next() to send the row.
If sending a single result set or if using JDBC types, call JServerResultSet.done() to indicate that all rows have been sent in the current result set.
If your component uses IDL/Java datatypes, use the com.sybase.CORBA.IdlResultSet class to convert the result set to a TabularResults.ResultSet instance. See the Jaguar CTS API Reference for details.

You can repeat steps 4 to 6 to send or create another result set that has the same metadata using the same JServerResultSet object. Repeat steps 1 to 6 to send or create another result set that requires different metadata.

You cannot return multiple result sets unless the method's IDL definition returns TabularResults::ResultSets.

JServerResultSet example

The example method below sends three rows with three columns each. Note that exceptions are not caught in the example; the server logs any uncaught exceptions that are thrown in a method call:

public void send_rows (IntegerHolder ih) throws
      JException, SQLException 
{

  // Declare the constant 'pi'
  final double pi = 3.1414;  // Create the metadata object.
  JServerResultSetMetaData 
       jsrsmd = JContext.createServerResultSetMetaData();

  // There will be 3 columns in the result set.
  jsrsmd.setColumnCount(3);

  // The first column has datatype INTEGER and name 'one'.
  jsrsmd.setColumnType(1, Types.INTEGER);
  jsrsmd.setColumnName(1, "one");

  // The second column has datatype VARCHAR and name 'two'.
  jsrsmd.setColumnType(2, Types.VARCHAR);
  jsrsmd.setColumnName(2, "two");

  // The third column has datatype DOUBLE and name 'three'.
  jsrsmd.setColumnType(3, Types.DOUBLE);
  jsrsmd.setColumnName(3, "three");
  
  // Create the result set object.
  JServerResultSet jsrs = JContext.createServerResultSet(jsrsmd);

  // Position the cursor.
  jsrs.next();

  // First row values: 1, "first", pi 
  jsrs.setInt(1, 1);
  jsrs.setString(2, "first");
  jsrs.setDouble(3, pi);

  // Send the row.
  jsrs.next();

  // Second row values: 2, "second", pi * 2
  jsrs.setInt(1, 2);
  jsrs.setString(2, "second");
  jsrs.setDouble(3, pi * 2.0);

  // Send the row.
  jsrs.next();

  // Third row values: 3, "third", pi * 3
  jsrs.setInt(1, 3);
  jsrs.setString(2, "third");
  jsrs.setDouble(3, pi * 3.0);

  // Send the row.
  jsrs.next();

  // Demarcate the end of the result set by calling done().
  jsrs.done();
}

The fragment below shows client-side code to call the stub and print the rows to the console. For more information about coding the client to retrieve result sets from components, see "Return result sets".

try {
  ih = new IntegerHolder();
  comp.send_rows(ih);

  ResultSet rs = comp.getResultSet();
  ResultSetMetaData rsmd = rs.getMetaData();

  StringBuffer row = new StringBuffer("");
  for (int i = 1; i <= rsmd.getColumnCount(); i++)
  {
    row.append(rsmd.getColumnName(i));
    if (i < rsmd.getColumnCount())
      row.append("\t");
  }

  System.out.println(row);
 
  while(rs.next())
  {
    row = new StringBuffer("");
    for (int i = 1; i <= rsmd.getColumnCount(); i++)
    {
      row.append(rs.getString(i));
      if (i < rsmd.getColumnCount())
        row.append("\t");
    }
    System.out.println(row);
  }

  // Discard any remaining results.
  while(comp.getMoreResults())
  {
    rs = comp.getResultSet();
  }
 
}
catch (Exception e) {
  System.out.println("Exception: " + e.getMessage());
  e.printStackTrace();
}