The Basics – Running a Query

Next to logging on/off, executing DQL queries is one of the most frequent tasks developed in a Documentum application.  There are three primary classes used to query the repository and process the results. These classes are:

  • IDfQuery – The query class encapsulates all of the data and functionality necessary to run a DQL query against a repository.
  • IDfCollection – The collection class encapsulates the results of a query in a read-once, forward-only object.  A better alternative is to use the dmRecordSet class.
  • IDfTypedObject – A typed object is a non-persistent object used to model row data in the IDfCollection object.

There are potentially more classes involved depending upon circumstances, for example whether you choose to use a query builder class (IDfQueryBuilder) to construct your query, or the dmRecordSet to hold query results.

The basic structure of the query code looks like this:


public IDfCollection runQuery(String query, IDfSession session) {
  IDfCollection col = null;

  try {
    // create query object
    IDfQuery q = new DfQuery();

    // set query string
    q.setDQL(query);

    // execute query
    col = q.execute(session, DfQuery.DF_READ_QUERY);

  } catch (DfException e) {
    e.printStackTrace();
  }
  return col;
}

Note the use of the DfQuery.DF_READ_QUERY constant. The use of the proper query type constant can affect your query performance. See here for more on query type constants.

The basic structure of the code used to process the results returned in the IDfCollection object is:


// do query
IDfCollection col = runQuery("select r_object_id, object_name from dm_document where folder('/Templates')", session);

// process results
while (col.next()) {

 // get each row
 IDfTypedObject tObj = col.getTypedObject();

 // get value in each column and do something with results
 String id = tObj.getString("r_object_id");
 String name =  tObj.getString("object_name");
 System.out.println(id + "\t" + name);
}

// it is very important to close each collection after you process it
if ( (col != null) && (col.getState() != IDfCollection.DF_CLOSED_STATE) )
 col.close();

In this example, the IDfTypedObject represents a row in the IDfCollection and its getter methods are used to retrieve each column’s data.  Note that an IDfCollection object can only be read once, and only in the forward direction.  For a more robust and capable query result object, use the dmRecordSet class.

UPDATE:  I have created a DCTMBasics JAR file that contains most of the code discussed in this series.

Advertisements

dmRecordSet Update v1.2

I just published an update to the dmRecordSet class, a more useable and flexible alternative to the DFC’s IDfCollection class.  Here is a quick list of improvements/changes in this release:

  • added getRecordSetAsList() method – returns the record set as a Java List
  • added getRecordSetAsSet() method – returns the record set as a Java Set (contains only unique objects)
  • added getRecordSetInfo() method – returns a String of stats about record set
  • added getRowCount() – returns number of rows in record set
  • added getColumnCount() – returns number of columns in record set
  • added static getVersion() – returns version information for class
  • changed getRow() to issue DfLogger statement if invalid row requested, instead of throwing exception
  • Deprecated first(), replaced with getFirstRow()
  • Deprecated last(), replaced with getLastRow()
  • Deprecated next(), replaced with getNextRow()
  • Deprecated getRow(), replaced with getCurrentRow()
  • Deprecated previous(), replaced with getPreviousRow()
  • Deprecated resetBeginning(), replaced with resetToBeginning()
  • Deprecated resetEnd(), replaced with resetToEnd()
  • getNextRow() and getPreviousRow() use getRow()
  • updated CollectionExamples test class

I also included a short tutorial that walks through some basic uses of the class.

The latest dmRecordSet class can be downloaded here.

dmRecordSet Update

I updated the code for the dmRecordSet class here.  The only change is that I removed some debug statements that made it into the released code.

IDfCollections, Part V

In this final post of the IDfCollection series, I offer an alternative to the IDfCollection object, the dmRecordSet. The dmRecordSet is an object I created to extend the capabilities of the IDfCollection and overcome many of the limitations I have been discussing here. For example, the dmRecordSet allows you to:

  • move forward, backward or randomly through the record set;
  • reset the record set to the beginning or the end to be re-processed;
  • add rows to the set;
  • determine the number of rows in the set;
  • determine if a record set is empty or not; and
  • retrieve column definitions.

The following code provides some examples of how easy it is to use the dmRecordSet object.

Instantiate a dmRecordSet:

    IDfCollection col = null;
    String dql = "select r_object_id, object_name, "
           + "r_creation_date, a_content_type, r_full_content_size, a_is_template "
           + "from dm_document where folder('/Temp',descend)";
    IDfQuery q = new DfQuery();
    IDfTypedObject tObj = null;

    q.setDQL(dql);
    col = q.execute(session, DfQuery.DF_READ_QUERY);

    // get record set
    dmRecordSet dmRS = new dmRecordSet(col);

Test for empty set and count rows:

    System.out.println("Record count = " + dmRS.getRowCount());
    if (dmRS.isEmpty()) {
       System.out.println("dmRecordSet is empty");
    } else {
       System.out.println("dmRecordSet is NOT empty");
    }

Process record set:

    while (dmRS.hasNext()) {
       tObj = dmRS.next();
       System.out.print(tObj.getString("r_object_id") + "\t");
       System.out.println(tObj.getString("object_name");
    }

Move to end and process set backwards:

    tObj = dmRS.last();
    while (dmRS.hasPrevious()) {
        tObj = dmRS.previous();
        System.out.print(tObj.getString("r_object_id") + "\t");
        System.out.println(tObj.getString("object_name");
    }

The class, source code and Javadoc for the dmRecordSet can be downloaded here.

I hope you have enjoyed this series on the IDfCollection object, one of the most commonly used but least functional objects in the DFC.  As always, I appreciate your comments and feedback.

%d bloggers like this: