Running a query and reporting on its result set

HCL Compass client provides powerful reporting capability in a graphical user interface (GUI) environment. The HCL Compass API also supports programmatic reporting.

Sometimes all you need is the raw results rather than a highly formatted report. The following subroutine is an external application:

• Uses an existing query object to run the query.
• Prints out the name of the EntityDef (record type) that the query runs against.
• Iterates through all the records in the result set to print the label and value of each field in each record. This subroutines makes use of two other routines: StdOut, which prints its arguments to a file, and ToStr(not included here), which converts its argument to a string.

Following the VBScript and Perl code examples are additional Perl code examples that illustrate:

• Listing all the defect records in a HCL Compass user database and modifying one of the records. See, Getting a list of defects and modifying a record
• Sorting the result set by using methods of the QueryFieldDef object. See, Sorting a result set

VBScript

Sub RunBasicQuery(session, querydef) 
' The parameters to this subroutine are a Session object and a 
' QueryDef object. It is assumed that the QueryDef is valid (for
' example, BuildField has been used to select one or more fields
' to retrieve). 

   Dim rsltset ' a ResultSet object 
   Dim status ' a Long 
   Dim column ' a Long 
   Dim num_columns ' a Long 
   Dim num_records ' a Long 

   Set rsltset = session.BuildResultSet(querydef) 
   rsltset.Execute 

   StdOut "primary entity def for query == " & _
            rsltset.LookupPrimaryEntityDefName 

   num_columns = rsltset.GetNumberOfColumns 
   num_records = 0 
   status = rsltset.MoveNext 
   Do While status = AD_SUCCESS 
      num_records = num_records + 1 
      StdOut "Record #" & num_records 

      ' Note: result set indices are based 1..N, not the usual
      '  0..N-1 
   column = 1 
   Do While column <= num_columns 
         ' ToStr converts the argument to a string
         StdOut " " & rsltset.GetColumnLabel(column) & "=" & _
            ToStr(rsltset.GetColumnValue(column)) 
         column = column + 1 
      Loop 

      StdOut "" 
      status = rsltset.MoveNext 
   Loop 
End Sub 

REM Start of Global Script StdOut
sub StdOut(Msg)
   msgbox Msg
end sub
REM End of Global Script StdOut 

Perl

sub RunBasicQuery {
my($session)=@_[0];
my($querydef)=@_[1];
#  The parameters to this subroutine are a Session object 
#  and a QueryDef object. It is assumed that the QueryDef 
#  is valid (for example, BuildField has been used to select 
#  one or more fields to retrieve). 

my ($rsltset);      #  This is a ResultSet object 
my ($status);
my ($column);
my ($num_columns);
my ($num_records);

$rsltset = $session->BuildResultSet(querydef);
$rsltset->Execute;

print "primary entity def for query == ", 
$rsltset->LookupPrimaryEntityDefName;

   $num_columns = $rsltset->GetNumberOfColumns;
   $num_records = 0;
   $status = $rsltset->MoveNext;
   while ($status == $CQPerlExt::CQ_SUCCESS) {
      $num_records = $num_records + 1;
      print "Record #", $num_records;
      #  Note: result set indices are based 1..N, not the usual
      # 0..N-1 
      $column = 1;
      while ($column <= $num_columns) {
         print " ", $rsltset->GetColumnLabel($column), "=", 
         $rsltset->GetColumnValue($column);
         $column = $column + 1;
      }
      print "";
      $status = $rsltset->MoveNext;
   }
}