Thursday, 17 March 2011

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...

17 comments :

Anjali Nair said...

Hi,
I am new to Clearquest, so forgive me if I sound silly. I have to create an application that has to get defect records from Clearquest. This application will not reside on the same server as that of Clearquest. Is this possible using the above code? Because if the application has to connect to a remote Clearquest server, the url has to be specified somewhere. It would be great if you could help.
Thanks,
Anjali.

Martien van den Akker said...

HI Anjali,

I've been on vacation, so sorry for the late response. The solution here uses a Java Native Interface on the Clear Quest client. The Jar file you need to incorporate in your classpath is delivered in the ClearQuest client. The possible connections to remote ClearQuest servers are registered in the ClearQuest client (see the administration tools in the menu). So in deed the servers does not need to reside on the same server as the ClearQuest server. Provided that the server that runs your application has a clearquest-client installed.

Regards,
Martien

Anonymous said...

How do you close the session? e.g. in CQPerl, you Unbuild (e.g.
CQSession::Unbuild($session)) to close the session. Just wondering is there such API in Java?

Anonymous said...

Great post! Any plans to make it work with CQ webservice, means no CQ client required on the machine my application runs on ?!

bye4now, Gilbert

Yargo said...

Hi,

A lot of thanks for sharing this info!

Have you ever encountered a situation in which the CQ finds 10 records but running query using code like yours finds only 5 of them, not showing the rest?

Why this is happening?

BTW, other entity prints 4 of 7..

TIA.

Martien van den Akker|Darwin-IT said...

Hi Yargo,

No in my case I get all the records I have matching my filter.

Regards,
Martien

Sam said...

Hi,
Very helpful blog. I have a requirement where in from my web application i should be able to query clearquest by passing the user credentials. From the reply to comments, i could see that 'server that runs your application has a clearquest-client installed.' I have a constraint where in the server on which my web application will be deployed will not have clear quest client installed. Is there any alternate way to handle this?
Thanks in Advance,
Sam

Martien van den Akker|Darwin-IT said...

Hi Sam,

This article was based on the java native interface, hence the requirement. I've seen that there is a web-interface so there might be webservices as well.

I would go that way. But I haven't looked into ClearQuest's webservice possibilities.

Regards,
Martien

Viswanath SK said...

Can we create a new incident? is there any interface present for the same?

Martien van den Akker|Darwin-IT said...

Hi Viswanat,
Interesting question.
But, I'm sorry, it's been a while and haven't done something with this ever since.

You could look into the javadocs, I would imagine that it should be possible.

If you find out if and how it's possible, I would welcome a new comment of you.

Regards,
Martien

Viswanath SK said...

Hi Martien,
Thanks for the quick response.Could you please provide the link for the javadocs?

Viswanath SK said...

Hi Martien,
Thanks for the quick response.Could you please provide the link for the javadocs?

Martien van den Akker|Darwin-IT said...

No, havent got it by hand. I've no installment here.

Regards,
Martien

Viswanath SK said...

How to close a session?

Martien van den Akker|Darwin-IT said...

SOrry, haven't time to look in to this. But I see that I don't actually close a session. Don't know why, apparently I did not get this from my examples or investigation.

Unfortunately I have no ClearQuest available anymore (I'm not with that customer at the moment).
So I can't look that up.

Good luck.

Regards,
Martien

Anonymous said...

I want to create a new incident using an macro from excel, Is it possible?

Martien van den Akker|Darwin-IT said...

Hi,

Well, I figure there would be an API to create incidents as well. But I can't look that up for you right now.
If you're able to call out external executables from a Excel macro I figure that you would be able to do what you have in mind.

However I do know that there are is a java lib that enables you to read an Excel workbook. Doing so you surely can create a java application that can create incidents in Excel conforming a flag. Probably update a cell in Excel for that incident as well to indicate that the incident has been created. But again, I can't sort that out now.

Good luck.