Query ClearQuest with Java.

At deployment of a new release of a project it might be required to deliver a list of solved issues in a release notes document. At least that was a requirement that my colleagues on my current project had.
They copied and pasted the issues from the Rational ClearQuest tool, which they felt was error-prone and a tedious job. If I could create a tool for it?

The first thing I did was to look into ClearQuest on how the import and export possibilities were. But although there is an export tool, it is not batch. It'll start a wizard that you have to go through. Within ClearQuest there are query possibilities. But then again: you're in the tool and we wanted to have it batch-wise, integrated with Ant.
So it would be nice to be able to query ClearQuest in Java. And fortunately I found a cqjni.jar in the ClearQuest home. This jar is a Java Native Interface set of API's on CQ.

There are, however, very few java examples. I did find an example on perl here. I could use this with some other examples to come up with my own code. So I'll lay out my steps here.

0. Some helper methods

First I'll give you some helper methods that I use in the following examples. They're in fact shortcuts to "System.out.println".

/**
  * Shortcut for output
  * 
  * @param text
  */
 public static void pl(String text) {
  System.out.println(text);
 }

 public static void pl(String text, Exception e) {
  System.out.println(text);
  e.printStackTrace();
 }

 /**
  * Output a String array.
  * 
  * @param label
  * @param array
  */
 public static void printStringArray(String label, String[] array) {
  for (int idx = 0; idx < array.length; idx++) {
   pl(label + " " + idx + ": " + array[idx]);
  }
 }

1. Create session

The first  step to do is to create a CQSession:

pl("Begin");
CQSession cqSession = new CQSession();

2. List Installed Database Sets and Master Databases

To be able to query on CQ you'll have to find out what databases you can connect to. In CQ apparently databases are grouped in a DatabaseSet. And a DatabaseSet is correlated to a MasterDatabase. You can query them as follows.

CQSession session = getCqSession();
  String[] dbSets = session.GetInstalledDbSets();
  String[] masters = session.GetInstalledMasterDbs();

As I understand it, a DabaseSet is put in a database. In my setup I've two masterdatabases and a DatabaseSet in each. I assumed that the entries in the String Arrays correspond to each other: dbSets[0] correlates to masters[0], etc.

I combined the lot in a some combined methods. These are based on a helper Bean DatabaseSet.

/**
  * Get Installed ClearQuest Database Sets
  * 
  * @return
  * @throws CQException
  */
 public String[] getInstalledDbSetNames() throws CQException {
  CQSession session = getCqSession();
  String[] dbSets;
  dbSets = session.GetInstalledDbSets();
  return dbSets;
 }

 /**
  * Get Installed ClearQuest Master Databases
  * 
  * @return
  * @throws CQException
  */
 public String[] getInstalledMasterDbNames() throws CQException {
  CQSession session = getCqSession();
  String[] masters = session.GetInstalledMasterDbs();
  return masters;
 }

 /**
  * Get Installed ClearQuest Database Sets
  * 
  * @return
  */
 public Vector<DatabaseSet> getInstalledDatabaseSets() {
  final String methodName = "getInstalledDatabaseSets";
  Vector<DatabaseSet> dbSets = new Vector<DatabaseSet>();
  try {
   String[] dbSetNames = this.getInstalledDbSetNames();
   String[] masters = this.getInstalledMasterDbNames();
   for (int i = 0; i < dbSetNames.length; i++) {
    DatabaseSet dbSet = new DatabaseSet(masters[i], dbSetNames[i]);
    dbSets.add(dbSet);
   }
  } catch (CQException e) {
   pl("Exception at " + methodName, e);
  }

  return dbSets;
 }
 /**
  * List Installed ClearQuest Database Sets
  * 
  * @return
  */
 public void listInstalledDbSets() {
  final String methodName = "listInstalledDbSets";
  pl(methodName);
  Vector<DatabaseSet> dbSets;
  dbSets = getInstalledDatabaseSets();
  ListIterator<DatabaseSet> it = dbSets.listIterator();
  while (it.hasNext()) {
   DatabaseSet dbSet = it.next();
   pl(dbSet.toString());
  }
 }

3. Accessible Databases

To list the Accessible Databases I used the code that I found here. In my own code I moved some of the code to the methods above.

/**
  * List Accessible ClearQuest Databases
  * 
  * @return
  */
 public void listAccessibleDatases() {
  final String methodName = "listInstalledDatabaseSets";
  pl(methodName);
  Vector<DatabaseSet> dbSets;
  dbSets = getInstalledDatabaseSets();
  ListIterator<DatabaseSet> it = dbSets.listIterator();
  while (it.hasNext()) {
   try {
    DatabaseSet dbSet = it.next();
    pl("Handling dbSet: " + dbSet.getName());
    CQSession session = getCqSession();
    CQDatabaseDescs dbDescriptors = session.GetAccessibleDatabases(
      dbSet.getMasterDb(), "", dbSet.getName());
    int n = (int) dbDescriptors.Count();
    System.out.println(" Accessible database count: " + n);
    for (int j = 0; j < n; j++) {
     CQDatabaseDesc desc = dbDescriptors.Item(j);

     pl(" Database " + j + ": " + desc.GetDatabaseName());
    }
   } catch (CQException e) {
    pl("Exception at " + methodName, e);
   }
  }
 }

4. Connect to a database

If you know what database you want to connect to, you can logon:

pl("Logon to ClearQuest");
   cqSession.UserLogon("User", "Password", "CQ-database", "CQ-DatabaseSet");
   setCqSession(cqSession)

5. List Record Types

Record types are sort of the logical-entities in ClearQuest. You have to know the entity in which your issues are stored. To list the possible Record Types you'll have to connect to the database (see above). Then you can list the record types as follows:

/**
  * List the possible RecordTypes
  * 
  * @return
  * @throws CQException
  */
 public void listRecordTypes() {
  final String methodName = "listRecordTypes";
  pl(methodName);
  try {
   CQSession session = getCqSession();
   String[] cqRecordTypes = session.GetEntityDefNames();
   printStringArray("Record Type", cqRecordTypes);
  } catch (CQException e) {
   pl("Exception at " + methodName, e);
  }
 }

The same way you can find so-called Family Types. I did not use them further.

/**
  * List the possible Family Types
  */
 public void listFamilyTypes() {
  final String methodName = "listFamilyTypes";
  pl(methodName);
  try {
   CQSession session = getCqSession();
   String[] cqFamilyTypes = session.GetEntityDefFamilyNames();
   printStringArray("Family Type", cqFamilyTypes);
  } catch (CQException e) {
   pl("Exception at " + methodName, e);
  }

 }

6. Build a query

Now it's time to build the query. If you've build a query before in the CQ tool you'll probably be familiar with the concepts. It starts with creating a query and add columns to it:

CQQueryDef cqQueryDef = cqSession.BuildQuery("BPM");
   cqQueryDef.BuildField("id");
   cqQueryDef.BuildField("State");
   cqQueryDef.BuildField("headline");
   cqQueryDef.BuildField("Owner");
   cqQueryDef.BuildField("Fix_By");

The parameter in the BuildQuery method is the RecordType that you can query with the code in previous paragraph. In this example the record type is "BPM". The field-names are those defined in the tool on the record type. So it might be handy to build your query first in the CQ-Tool, to know which attributes/columns are to your disposal.

7. Create a filter

Of course you don't want the whole lot of issues. In a longer running project there can be thousands of them. So we'll need to add a filter. In the ClearQuest Tool you can have a hierarchy of so called filter nodes. A filter node has several expressions that are concatenated using And or Or operators. Such an expression in itself can be a other filter node or what I call a FilterField: a filter on a field.

pl("Query Issues");
   CQQueryFilterNode cqQueryFilterNode = cqQueryDef
     .BuildFilterOperator(BOOL_OP_AND);
   String[] stateValues = { "Closed", "Resolved" };
   cqQueryFilterNode.BuildFilter("State", COMP_OP_EQ, stateValues);
   String[] branchValues = { "R1.%" };
   cqQueryFilterNode.BuildFilter("Fix_by", COMP_OP_LIKE, branchValues);

Here you see that I use some constants. For the FilterOperators the possible values are:

// And operator
 public static final long BOOL_OP_AND = 1;
 // Or operator
 public static final long BOOL_OP_OR = 2;

For the Filter "Comparators" the following values are possible:

// Equality operator (=)
 public static final long COMP_OP_EQ = 1;
 // Inequality operator (<>)
 public static final long COMP_OP_NEQ = 2;
 // Less-than operator (<)
 public static final long COMP_OP_LT = 3;
 // Less-than or Equal operator (<=)
 public static final long COMP_OP_LTE = 4;
 // Greater-than operator (>)
 public static final long COMP_OP_GT = 5;
 // Greater-than or Equal operator (>=)
 public static final long COMP_OP_GTE = 6;
 // Like operator (value is a substring of the string in the given field)
 public static final long COMP_OP_LIKE = 7;
 // Not-like operator (value is not a substring of the string in the given
 // field)
 public static final long COMP_OP_NOT_LIKE = 8;
 // Between operator (value is between the specified delimiter values)
 public static final long COMP_OP_BETWEEN = 9;
 // Not-between operator (value is not between specified delimiter values)
 public static final long COMP_OP_NOT_BETWEEN = 10;
 // Is-NULL operator (field does not contain a value)
 public static final long COMP_OP_IS_NULL = 11;
 // Is-not-NULL operator (field contains a value)
 public static final long COMP_OP_IS_NOT_NULL = 12;
 // In operator (value is in the specified set)
 public static final long COMP_OP_IN = 13;
 // Not-in operator (value is not in the specified set)
 public static final long COMP_OP_NOT_IN = 14;

8. Execute the query and process the rows

Having built the query, it can be executed. First you'll have to create a CQResultSet from the session based on the Query Definition.
The query can then be executed and the result set can be processed using the MoveNext() method. The MoveNext() gives a status. As long as the status equals 1 there is a next record. With GetNumberOfColumns you can count the number of columns in the resultset. And with GetColumnLabel and GetColumnValue you can get the Column name and value at a particular index. Of course you know what columns you asked for and in what order. But traversing over the available columns is handy, because adding a column to the query does not affect this part of the code. Also it enables you to search for the value of a particular column.

CQResultSet cqResultSet = cqSession.BuildResultSet(cqQueryDef);
   long count = cqResultSet.ExecuteAndCountRecords();
   pl("count: " + count);
   long status = cqResultSet.MoveNext();
   while (status == 1) {
    pl("Status: " + status);
    for (int i = 1; i <= cqResultSet.GetNumberOfColumns(); i++) {
     pl(cqResultSet.GetColumnLabel(i) + ": "
       + cqResultSet.GetColumnValue(i));
    }
    status = cqResultSet.MoveNext();
   }
   pl("Status: " + status);
   pl("Done");

Conclusion

That's it. The example-snippets above come from a test class I put together and that can be  downloaded form here. It's a test class that I created from other examples and refactored somewhat. For this blog I anonymised it and rearranged some of the methods. In my application I refactored the class into seperate classes for the dbconnection, a query class and an export class. Of course that's what you do in a project, to make it neat. But in the basics it runs down to this particular example. So I kept it in my project to first pilot a new feature.

Then I created some ant tasks and targets to do the export and run Word with a document and a macro to import the file. But that's another story...