9 Retrieving Query Results

Example 9-1 Creating a Cursor

CursorManager cursorMngr = dp.createCursorManager(querySource);
Cursor queryCursor = cursorMngr.createCursor();

// Use the Cursor in some way, such as to display the values of it.

cursorMngr.close();

Example 9-6 Navigating for a Table View

Source unitPriceByMonth = unitPrice.join(productSel)
                                   .join(timeSel);
// Commit the current Transaction (code not shown).

// Create a Cursor for unitPriceByMonth.
CursorManager cursorMngr = dp.createCursorManager(unitPriceByMonth);
CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor();

// Determine a starting position and the number of rows to display.
int start = 7;
int numRows = 12;

println("Month     Product     Unit Price");
println("-------   --------   ----------");

// Iterate through the specified positions of the root CompoundCursor.
// Assume that the Cursor contains at least (start + numRows) positions.
for(int pos = start; pos < start + numRows; pos++) 
{
  // Set the position of the root CompoundCursor.
  rootCursor.setPosition(pos);
  // Print the local values of the output and base ValueCursors.
  // The getLocalValue method gets the local value from the unique
  // value of a dimension element.
  String timeValue = ((ValueCursor)rootCursor.getOutputs().get(0))
                     .getCurrentString();
  String timeLocVal = getLocalValue(timeValue);
  String prodValue = ((ValueCursor)rootCursor.getOutputs().get(1))
                     .getCurrentString();
  String prodLocVal = getLocalValue(prodValue);
  Object price = rootCursor.getValueCursor().getCurrentValue();
  println(timeLocVal + "   " + prodLocVal + "   " +  price);
}
cursorMngr.close();

If the time selection for the query has eight values, such as the first month of each calendar quarter for the years 2001 and 2002, and the product selection has three values, then the result set of the unitPriceByMonth query has twenty-four positions. Example 9-6 displays the following table, which has the values specified by positions 7 through 18 of the CompoundCursor.

Month     Product    Unit Price
-------   --------   ----------
2001.07   ENVY ABM   2892.18
2001.07   ENVY EXE   3155.91
2001.07   ENVY STD   2505.57
2001.10   ENVY ABM   2856.86
2001.10   ENVY EXE   3105.53
2001.10   ENVY STD   2337.3
2002.01   ENVY ABM   2896.77
2002.01   ENVY EXE   3008.95
2002.01   ENVY STD   2140.71
2002.04   ENVY ABM   2880.39
2002.04   ENVY EXE   2953.96
2002.04   ENVY STD   2130.88

Example 9-7 uses the same query as Example 9-6. In a crosstab view, the first row is column headings, which are the values from prodSel in this example. The output for prodSel is the faster varying output because the prodSel dimension selection is the last output in the list of outputs that results from the operations that join the measure to the dimension selections. The remaining rows begin with a row heading. The row headings are values from the slower varying output, which is timeSel. The remaining positions of the rows, under the column headings, contain the unitPrice values specified by the set of the dimension values. To display the results of a query in a crosstab view, you iterate through the positions of the children of the top-level CompoundCursor.

The DataProvider is dp.

Example 9-7 Navigating for a Crosstab View Without Pages

Source unitPriceByMonth = unitPrice.join(productSel)
                                   .join(timeSel);
// Commit the current Transaction (code not shown).

// Create a Cursor for unitPriceByMonth.
CursorManager cursorMngr = dp.createCursorManager(unitPriceByMonth);
CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor();

// Get the outputs and the ValueCursor objects.
List outputs = rootCursor.getOutputs();
// The first output has the values of timeSel, the slower varying output.
ValueCursor rowCursor = (ValueCursor) outputs.get(0);
// The second output has the faster varying values of productSel.
ValueCursor columnCursor = (ValueCursor) outputs.get(1);
// The base ValueCursor has the values from unitPrice.
ValueCursor unitPriceValues = rootCursor.getValueCursor();

// Display the values as a crosstab.
println("                     PRODUCT");
println("          ---------------------------------");
print("Month     ");
do 
{
  String value = ((ValueCursor) columnCursor).getCurrentString();
  print(getContext().getLocalValue(value) + "    ");
} while (columnCursor.next());
println("\n-------   --------    --------    --------");

// Reset the column Cursor to its first element.
columnCursor.setPosition(1);

do 
{
  // Print the row dimension values.
  String value = ((ValueCursor) rowCursor).getCurrentString();
  print(getContext().getLocalValue(value) + "    ");
  // Loop over columns.
  do 
  {
    // Print data value.
    print(unitPriceValues.getCurrentValue() + "     ");
  } while (columnCursor.next());
 
  println();
 
  // Reset the column Cursor to its first element.
  columnCursor.setPosition(1);
  } while (rowCursor.next());

cursorMngr.close();

Example 9-8 Navigating for a Crosstab View With Pages

// In someMethod.
Source unitsForSelections = units.join(prodSel)
                                 .join(custSel)
                                 .join(timeSel)
                                 .join(chanSel);
// Commit the current Transaction (code not shown).

// Create a Cursor for unitsForSelections.
CursorManager cursorMngr = dp.createCursorManager(unitsForSelections);
CompoundCursor unitsForSelCursor = (CompoundCursor) cursorMngr.createCursor();

// Send the Cursor to the printAsCrosstab method.
printAsCrosstab(unitsForSelCursor);

cursorMngr.close();
// The remainder of the code of someMethod is not shown.

private void printAsCrosstab(CompoundCursor rootCursor)
{
  List outputs = rootCursor.getOutputs();
  int nOutputs = outputs.size();

  // Set the initial positions of all outputs.
  Iterator outputIter = outputs.iterator();
  while (outputIter.hasNext())
    ((Cursor) outputIter.next()).setPosition(1);
  
  // The last output is fastest-varying; it represents columns.
  // The next to last output represents rows.
  // All other outputs are on the page.
  Cursor colCursor = (Cursor) outputs.get(nOutputs - 1);
  Cursor rowCursor = (Cursor) outputs.get(nOutputs - 2);
  ArrayList pageCursors = new ArrayList();
  for (int i = 0 ; i < nOutputs - 2 ; i++) 
  {
    pageCursors.add(outputs.get(i));
  }

  // Get the base ValueCursor, which has the data values.
  ValueCursor dataCursor = rootCursor.getValueCursor();

  // Print the pages of the crosstab.
  printPages(pageCursors, 0, rowCursor, colCursor, dataCursor);
}

// Prints the pages of a crosstab.
private void printPages(List pageCursors, int pageIndex, Cursor rowCursor,
                        Cursor colCursor, ValueCursor dataCursor) 
{
  // Get a Cursor for this page.
  Cursor pageCursor = (Cursor) pageCursors.get(pageIndex);

  // Loop over the values of this page dimension.
  do 
  {
    // If this is the fastest-varying page dimension, print a page.
    if (pageIndex == pageCursors.size() - 1) 
    {
      // Print the values of the page dimensions.
      printPageHeadings(pageCursors);

      // Print the column headings.
      printColumnHeadings(colCursor);

      // Print the rows.
      printRows(rowCursor, colCursor, dataCursor);

      // Print a couple of blank lines to delimit pages.
      println();
      println();
    }

    // If this is not the fastest-varying page, recurse to the
    // next fastest-varying dimension.
    else 
    {
      printPages(pageCursors, pageIndex + 1, rowCursor, colCursor, 
                 dataCursor);
    }
  } while (pageCursor.next());

  // Reset this page dimension Cursor to its first element.
  pageCursor.setPosition(1);
}

// Prints the values of the page dimensions on each page.
private void printPageHeadings(List pageCursors) 
{
  // Print the values of the page dimensions.
  Iterator pageIter = pageCursors.iterator();
  while (pageIter.hasNext())
  {
    String value = ((ValueCursor) pageIter.next()).getCurrentString();    
    println(getLocalValue(value));
  }
  println();
}

// Prints the column headings on each page.
private void printColumnHeadings(Cursor colCursor) 
{
  do 
  {
     print("\t");
     String value = ((ValueCursor) colCursor).getCurrentString();
     print(getLocalValue(value));
  } while (colCursor.next());
  println();
  colCursor.setPosition(1);
}

// Prints the rows of each page.
private void printRows(Cursor rowCursor, Cursor colCursor,
                       ValueCursor dataCursor) 
{
  // Loop over rows.
  do 
  {
    // Print row dimension value.
    String value = ((ValueCursor) rowCursor).getCurrentString();
    print(getLocalValue(value));
    print("\t");
    // Loop over columns.
    do 
    {
      // Print data value.
      print(dataCursor.getCurrentValue());
      print("\t");
    } while (colCursor.next());
    println();

    // Reset the column Cursor to its first element.
    colCursor.setPosition(1);
  } while (rowCursor.next());

  // Reset the row Cursor to its first element.
  rowCursor.setPosition(1);
}

Example 9-8 displays the following values, formatted as a crosstab. The display has added page, column, and row headings to identify the local values of the dimensions.

Channel DIR
Month 2001.01 
                           Product
                ------------------------------ 
Customer        ENVY ABM   ENVY EXE   ENVY STD
-------------   --------   --------   --------
COMP WHSE SD       0          0          1
COMP SERV TOK         2          4          2
COMP WHSE LON         1          1          2
 

Channel DIR
Month 2000.02
                           Product
                ------------------------------ 
Customer        ENVY ABM   ENVY EXE   ENVY STD
-------------   --------   --------   --------
COMP WHSE SD       1          1          1
COMP SERV TOK         5          6          6
COMP WHSE LON         1          2          2
 

Channel DIR
Month 2000.03
                           Product
                ------------------------------ 
Customer        ENVY ABM   ENVY EXE   ENVY STD
-------------   --------   --------   --------
COMP WHSE SD       0          2          2
COMP SERV TOK         2          0          2
COMP WHSE LON         0          2          3

Example 9-9 Getting CursorSpecification Objects for a Source

Source unitsForSelections = units.join(prodSel)
                                 .join(custSel)
                                 .join(timeSel)
                                 .join(chanSel);
// Commit the current Transaction (code not shown).

// Create a CompoundCursorSpecification for unitsForSelections.
CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
                      dp.createCursorInfoSpecification(unitsForSelections);

// Get the ValueCursorSpecification for the base values.
ValueCursorSpecification baseValueSpec =
                        rootCursorSpec.getValueCursorSpecification();

// Get the ValueCursorSpecification objects for the outputs.
List outputSpecs = rootCursorSpec.getOutputs();
ValueCursorSpecification chanSelValCSpec = 
                       (ValueCursorSpecification) outputSpecs.get(0);
ValueCursorSpecification timeSelValCSpec = 
                       (ValueCursorSpecification) outputSpecs.get(1);
ValueCursorSpecification prodSelValCSpec = 
                       (ValueCursorSpecification) outputSpecs.get(2);
ValueCursorSpecification custSelValCSpec = 
                       (ValueCursorSpecification) outputSpecs.get(3);

Once you have the CursorSpecification objects, you can use their methods to specify the behavior of the Cursor objects that correspond to them.

Example 9-10 Specifying the Calculation of the Extent of a Cursor

rootCursorSpec.setExtentCalculationSpecified(true);

You can use methods of a CursorSpecification to determine whether the CursorSpecification specifies the calculation of the extent of a Cursor as in the following example.

boolean isSet = rootCursorSpec.isExtentCalculationSpecified();

Example 9-11 specifies calculating the starting and ending positions of the current value of a child Cursor in the parent Cursor. The example uses the CompoundCursorSpecification from Example 9-9.

Example 9-11 Specifying the Calculation of Starting and Ending Positions in a Parent

// Get the List of CursorSpecification objects for the outputs. 
// Iterate through the list, specifying the calculation of the extent
// for each output CursorSpecification. 
Iterator iterOutputSpecs = rootCursorSpec.getOutputs().iterator();
while(iterOutputSpecs.hasNext()) 
{
  ValueCursorSpecification valCursorSpec = 
    (ValueCursorSpecification)iterOutputSpecs.next();
  valCursorSpec.setParentStartCalculationSpecified(true);
  valCursorSpec.setParentEndCalculationSpecified(true);
}

You can use methods of a CursorSpecification to determine whether the CursorSpecification specifies the calculation of the starting or ending positions of the current value of a child Cursor in a parent Cursor, as in the following example.

Iterator iterOutputSpecs = rootCursorSpec.getOutputs().iterator();
ValueCursorSpecification valCursorSpec =
  (ValueCursorSpecification)iterOutputSpecs.next();
while(iterOutputSpecs.hasNext()) 
{
  if (valCursorSpec.isParentStartCalculationSpecified())
    // Do something.
  if (valCursorSpec.isParentEndCalculationSpecified())
    // Do something.
  valCursorSpec = (ValueCursorSpecification) iterOutputSpecs.next();
}

Example 9-12 determines the span of the positions in a parent CompoundCursor of the current value of a child Cursor for two of the outputs of the CompoundCursor. The example uses the unitForSelections Source from Example 9-8.

The example gets the starting and ending positions of the current values of the time and product selections and then calculates the span of those values in the parent Cursor. The parent is the root CompoundCursor. The DataProvider is dp.

Example 9-12 Calculating the Span of the Positions in the Parent of a Value

Source unitsForSelections = units.join(prodSel)
                                 .join(custSel)
                                 .join(timeSel)
                                 .join(chanSel);
// Commit the current Transaction (code not shown).

// Create a CompoundCursorSpecification for unitsForSelections.
CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
  dp.createCursorInfoSpecification(unitsForSelections);
// Get the CursorSpecification objects for the outputs.
List outputSpecs = rootCursorSpec.getOutputs();
ValueCursorSpecification timeSelValCSpec = 
  (ValueCursorSpecification)outputSpecs.get(1); // Output for time.
ValueCursorSpecification prodSelValCSpec = 
 (ValueCursorSpecification)outputSpecs.get(3);  // Output for product.

// Specify the calculation of the starting and ending positions.
timeSelValCSpec.setParentStartCalculationSpecified(true);
timeSelValCSpec.setParentEndCalculationSpecified(true);
prodSelValCSpec.setParentStartCalculationSpecified(true);
prodSelValCSpec.setParentEndCalculationSpecified(true);

// Create the CursorManager and the Cursor.
CursorManager cursorMngr =
  dp.createCursorManager(unitsForSelections, 100, rootCursorSpec);
CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor();

// Get the child Cursor objects.
ValueCursor baseValCursor = cursor.getValueCursor();
List outputs = rootCursor.getOutputs();
ValueCursor chanSelVals = (ValueCursor) outputs.get(0);
ValueCursor timeSelVals = (ValueCursor) outputs.get(1);
ValueCursor custSelVals = (ValueCursor) outputs.get(2);
ValueCursor prodSelVals = (ValueCursor) outputs.get(3);

// Set the position of the root CompoundCursor.
rootCursor.setPosition(15);

// Get the values at the current position and determine the span
// of the values of the time and product outputs.
print(chanSelVals.getCurrentValue() + ", ");
print(timeSelVals.getCurrentValue() + ",\n  ");
print(custSelVals.getCurrentValue() + ", ");
print(prodSelVals.getCurrentValue() + ", ");
print(baseValCursor.getCurrentValue());
println();

// Determine the span of the values of the two fastest-varying outputs.
long span;
span = (prodSelVals.getParentEnd() - prodSelVals.getParentStart()) +1);
println("\nThe span of " + prodSelVals.getCurrentValue() +
        " at the current position is " + span + ".")
span = (timeSelVals.getParentEnd() - timeSelVals.getParentStart()) +1);
println("The span of " + timeSelVals.getCurrentValue() +
        " at the current position is " + span + ".")
cursorMngr.close();

This example displays the following text.

CHANNEL_PRIMARY::CHANNEL::DIR, CALENDAR_YEAR::MONTH::2000.02, 
  SHIPMENTS::SHIP_TO::COMP SERV TOK, PRODUCT_PRIMARY::ITEM::ENVY STD, 6.0

The span of PRODUCT_PRIMARY::ITEM::ENVY STD at the current position is 1.
The span of CALENDAR_YEAR::MONTH::2000.02 at the current position is 9.

Example 9-13 Specifying a Fetch Size

println("The default fetch size is "
         + rootCursorSpec.getDefaultFetchSize() + ".");
Source source = rootCursorSpec.getSource();
CursorManager cursorMngr = dp.createCursorManager(source);
Cursor rootCursor = cursorMngr.createCursor();
rootCursor.setFetchSize(10);
println("The fetch size is now " + rootCursor.getFetchSize()) + ".";