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, andToStr
(
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;
}
}