9 Retrieving Query Results

Example 9-1 Creating a Cursor

// Prepare and commit the current Transaction.
try
{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
  println("Cannot commit the current Transaction. " + e);
}
tp.commitCurrentTransaction();
CursorManagerSpecification cursorMngrSpec =
                 dp.createCursorManagerSpecification(querySource);
SpecifiedCursorManager cursorMngr = 
                           dp.createCursorManager(cursorMngrSpec);
Cursor queryCursor = cursorMngr.createCursor();

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

cursorMngr.close();

Example 9-6 Navigating for a Table View

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

// Create a Cursor for unitPriceByMonth.
CursorManagerSpecification cursorMngrSpec =
            dp.createCursorManagerSpecification(unitPriceByMonth);
SpecifiedCursorManager cursorMngr = 
                             dp.createCursorManager(cursorMngrSpec);
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();
// sp9 is nine spaces.
  println("  " + timeLocVal + sp9 + prodLocVal + sp9 +  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
------     -------     ----------
  61         13         2505.57
  61         14         3155.91
  61         15         2892.18
  64         13         2337.30
  64         14         3105.53
  64         15         2856.86
  69         13         2140.71
  69         14         3008.95
  69         15         2896.77
  72         13         2130.88
  72         14         2953.96
  72         15         2880.39

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 TransactionProvider is tp and the DataProvider is dp.

Example 9-7 Navigating for a Crosstab View without Pages

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

// Create a Cursor for unitPriceByMonth.
CursorManagerSpecification cursorMngrSpec =
            dp.createCursorManagerSpecification(unitPriceByMonth);
SpecifiedCursorManager cursorMngr = 
                         dp.createCursorManager(cursorMngrSpec);
CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor();

// Get the outputs and the ValueCursor.
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("\t        Product");
println("\t-----------------------");
print("Month");
do 
{
  String value = ((ValueCursor) columnCursor).getCurrentString();
  print("\t" + getLocalValue(value) + "  ");
} while (columnCursor.next());
println();
println("-----\t-------\t-------\t-------");

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

do 
{
  // Print the row dimension values.
  String value = ((ValueCursor) rowCursor).getCurrentString();
  print(getLocalValue(value) + "\t");
  // Loop over columns.
  do 
  {
    // Print data value.
    print(unitPriceValues.getCurrentValue() + "\t");
  } 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);
// Prepare and commit the current Transaction (code not shown).

// Create a Cursor for unitsForSelections.
CursorManagerSpecification cursorMngrSpec =
                   dp.createCursorManagerSpecification(unitsForSelections);
SpecifiedCursorManager cursorMngr = 
                   dp.createCursorManager(cursorMngrSpec);
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   2
Customer 43
 
             Product
        ---------------- 
Month   13     14     15
-----   --     --     --
65       1      0      0
58       2      4      2
61       2      1      1
 

Channel   2
Customer 44
 
             Product
        ---------------- 
Month   13     14     15
-----   --     --     --
65       1      1      1
58       6      6      5
61       2      2      1
 

Channel   2
Customer 45
 
             Product
        ---------------- 
Month   13     14     15
-----   --     --     --
65       2      2      0
58       2      0      2
61       3      2      0

Example 9-9 Getting CursorSpecification Objects from a CursorManagerSpecification

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

// Create a Cursor for unitsForSelections.
CursorManagerSpecification cursorMngrSpec =
     dp.createCursorManagerSpecification(unitsForSelections);

// Get the root CursorSpecification of the CursorManagerSpecification.
CompoundCursorSpecification rootCursorSpec =
(CompoundCursorSpecification) cursorMngrSpec.getRootCursorSpecification();

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

// Get the CursorSpecification 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

CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
                          cursorMngrSpec.getRootCursorSpecification();
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 its parent Cursor. The example uses the CursorManagerSpecification from Example 9-9.

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

CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
                            cursorMngrSpec.getRootCursorSpecification();

// 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 its 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 TransactionProvider is tp and 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);
// Prepare and commit the current Transaction (code not shown).

// Create a CursorManagerSpecification for unitsForSelections.
CursorManagerSpecification cursorMngrSpec =
     dp.createCursorManagerSpecification(unitsForSelections);

 // Get the root CursorSpecification from the CursorManagerSpecification.
CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
                            cursorMngrSpec.getRootCursorSpecification();
// 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.
SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec);
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(promoSelVals.getCurrentValue() + ", ");
print(chanSelVals.getCurrentValue() + ", ");
print(timeSelVals.getCurrentValue() + ", ");
print(custSelVals.getCurrentValue() + ", ");
print(prodSelVals.getCurrentValue() + ", ");
println(baseValCursor.getCurrentValue());

// Determine the span of the values of the two fastest varying outputs.
int span;
span = (prodSelVals.getParentEnd() - prodSelVals.getParentStart()) +1);
println("The 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_AW::CHANNEL_AW::2, CALENDAR_YEAR_AW::MONTH_AW::44, 
SHIPMENTS_AW::SHIP_TO_AW::58, PRODUCT_PRIMARY_AW::ITEM_AW::15, 5.0
The span of PRODUCT_PRIMARY_AW::ITEM_AW::15 at the current position is 1.
The span of CALENDAR_YEAR_AW::MONTH_AW::44 at the current position is 9.

Example 9-13 Specifying a Fetch Size

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