The Basics – Specialized Query Methods

Following the Running a DQL Query post, this post looks at some simple ways to specialize DQL queries based upon their type (e.g., select, set, create, delete, insert, drop, cached, and update) and expected return values, and the benefits such specialization provides.  The Basics library of Documentum methods containing the specialized query methods as well as all other methods discussed in this series, can be found here.

The Basics library contains 10 query methods.  The method signatures follow:

  1. public static IDfCollection runSelectQuery(String query, IDfSession session) throws DfException
  2. public static dmRecordSet runSelectQueryAsRecordSet(String query, IDfSession session) throws Exception
  3. public static int runUpdateObjectQuery(String query, IDfSession session) throws DfException
  4. public static IDfCollection runCachedQuery(String query, IDfSession session) throws DfException
  5. public static String runCreateObjectQuery(String query, IDfSession session) throws DfException
  6. public static int runDeleteObjectQuery(String query, IDfSession session) throws DfException
  7. public static IDfCollection runExecQuery(String query, IDfSession session) throws DfException
  8. public static int runInsertQuery(String query, IDfSession session) throws DfException
  9. public static int runDeleteQuery(String query, IDfSession session) throws DfException
  10. public static int runUpdateQuery(String query, IDfSession session) throws DfException

I will leave the exploration of the code to you, and instead discuss a few of the benefits of these specialized query methods.

  1. Using specialized query methods makes your primary code much simpler to create, read, and maintain.  You don’t have to clutter the code with IDfQuery and IDfCollection objects.
  2. These methods provide simpler handling of return values.  For example, if you are only interested in a single result value (e.g., number objects updated), you don’t have to clutter your code with loops, etc. to retrieve that value.
  3. Using specialized query methods, you don’t have to worry about using the correct or most efficient query constants.
  4. These methods provide rudimentary syntax checking, which could be improved/extended for your purposes.  This can help alleviate receiving cryptic error messages from the query engine.
  5. Because all of these methods eventually call a single “uber” query method, error checking/trapping is centralized.

Here are some quick examples:

				// SELECT
				query = "select r_object_id, object_name from dm_document where folder('/Temp')";
				System.out.println("running SELECT query: " + query);
				col = DCTMBasics.runSelectQuery(query,session);
				// do something with IDfCollection
				col.close();

				// CACHED
				query = "select r_object_id, object_name from dm_document where folder('/Temp',descend)";
				System.out.println("running CACHED query: " + query);
				long start = System.currentTimeMillis();
				col = DCTMBasics.runCachedQuery(query,session);
				long stop = System.currentTimeMillis();
				long dif = stop - start;
				// do something with IDfCollection
				col.close();
				System.out.println("duration=" + dif);

				System.out.println("[running second query]");
				start = System.currentTimeMillis();
				query = "select r_object_id, object_name from dm_document where folder('/Temp',descend)";
				col = DCTMBasics.runCachedQuery(query,session);
				stop = System.currentTimeMillis();
				dif = stop - start;
				// do something with IDfCollection
				col.close();
				System.out.println("duration=" + dif);

				// OBJ CREATE
				query = "create dm_document object set object_name = 'DCTMBasics Test Object' link '/Temp'";
				System.out.println("running OBJ CREATE query: " + query);
				String objId = DCTMBasics.runCreateObjectQuery(query,session);
				System.out.println("created " + objId);

				// EXEC
				query = "execute db_stats";
				System.out.println("running EXEC query: " + query);
				col = DCTMBasics.runExecQuery(query,session);
				// do something with IDfCollection
				col.close();

				// OBJ UPDATE
				System.out.println("running OBJ UPDATE query");
				query = "update dm_document object set object_name = 'DCTMBasics updated object name' where r_object_id = '" + objId + "'";
				int cnt = DCTMBasics.runUpdateObjectQuery(query,session);
				System.out.println("update " + cnt + " objs");

				// OBJ DELETE
				System.out.println("running OBJ DELETE query");
				query = "delete dm_document object where r_object_id = '" + objId + "'";
				cnt = DCTMBasics.runDeleteObjectQuery(query,session);
				System.out.println("deleted " + cnt + " objs");
Advertisements

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.

The Basics – Logging On/Off

Establishing a session with the Content Server is one of the most basic and first operations a Documentum programmer must master.  There are three primary classes involved in establishing a session with the Content Server:

  • IDfSessionManager – The session manager manages identities, pooled sessions and transactions.  It is also required to release a session (i.e., log off ) when you are through with it.
  • IDfLoginInfo – The login info object encapsulates the information needed to validate and login a user to a repository. It stores the user’s credential information in addition to options that affect the server login process.
  • IDfSession – The session object encapsulates a session with a Documentum repository.

Here are my logon and logoff methods:


public static IDfSession logon(String docbase,
                               String username,
                               String password) throws DfException {
    IDfSession session = null;

    // validate arguments
    if ((docbase == null) || (docbase.trim().isEmpty()) )
      throw new DfException ("Docbase name is null or blank.");

    if ((username == null) || (username.trim().isEmpty()) )
      throw new DfException ("Username name is null or blank.");

    if ((password == null) || (password.trim().isEmpty()) )
      throw new DfException ("Password is null or blank.");

    // create login info
    IDfLoginInfo li = new DfLoginInfo();
    li.setUser(username);
    li.setPassword(password);
    li.setDomain("");

    // get session manager
    IDfSessionManager sessionMgr = DfClient.getLocalClient().newSessionManager();

    // login
    if (sessionMgr != null) {
      sessionMgr.setIdentity(docbase, li);
      session = sessionMgr.getSession(docbase);
     } else {
       throw new DfException("Could not create Session Manager.");
     }
     return session;
}

public static void logoff(IDfSession session) {
  // release session when done
  if (session != null) {
    session.getSessionManager().release(session);
  }
}

Here is an example of using the logon() and logoff() methods in a program:


public static void main(String[] args) {
  private IDfSession session = null;

  try {
    System.out.println("Logging on...");
    session = login("repo1", "dmadmin", "dmadmin");
    if (session != null) {
      System.out.println("Success: session ID = " + session.getSessionId());

      // do stuff here

      // release session when done
      System.out.println("Logging off...");
      logoff(session);

    } else {
      System.out.println("Logon failed: Session is null");
    }

  } catch (DfException dfe) {
    System.out.print("Logon failed:  ");
    System.out.println(dfe.getMessage());
  }

}

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

“The Basics” Series

I have been kicking around an idea to run a recurring series of posts demonstrating basic Documentum DFC programming techniques.  The idea will be to demonstrate techniques to implement DFC operations programmers routinely implement, like logging on/off, running queries, creating jobs, checking out objects, etc.  in short, concise, and best practice code snippets.  These are operations that everyone has (re)written a hundred times and should be kept in a library JAR file instead of recreated every time they are needed.  Having said that, perhaps you have a library JAR of operations you would like to share?

If you are new to Documentum and looking for DFC best practices and code examples, this series will be for you.  To find all of the posts in this series, search the tag library for “The Basics”.  Look for the first post of this series soon, with others to follow at random intervals.

There have been several “The Basics” topics previously covered in this blog already, in addition to some that are not-so-basic.  The following topics may only be touched upon in this series since they have been covered in depth previously:

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

Updated Documentum Tools and Applications

I had a little free time this week and made some updates to tools and applications I use frequently.  These updates represent ideas sent to me by users or were necessary to address additional situations I encountered in the field.  I hope they are helpful for you too.

QuikDIE v1.5 – This application exports content and metadata from a Documentum repository based upon a query.  Content is exported in its native format, and metadata is exported as XML.  Updates include:

  • detects and eliminates content “parked” on BOCS server
  • “dmi_” object types are ignored
  • implemented DCTMBasics JAR

For additional information regarding QuikDIE (including important info in the comments section), search for “QuikDIE“.

DeepExport v1.1 – This application exports CONTENT ONLY from a folder in a Documentum repository.  Content is exported in its native format and the folder hierarchy in the repository is replicated on the export drive.  Updates include:

  • detects and eliminates objects parked on BOCS server
  • updated to use DCTMBasics v1.3
  • refactored to remove Utils class
  • added password encryption/decryption

For additional information regarding DeepExport, search for “DeepExport“.

DCTMBasics v1.3 – This JAR file contains a set of helpful methods for interfacing with Documentum via the DFC.  It contains implementations of methods discussed in “The Basics” series.  Updates include:

  • getDFCVersion() – returns DFC version of your client
  • checkDFCVersion() – compare the current DFC version with a minimum version
  • createDocbasePath() – create a folder hierarchy in the repository
  • encryptPassword() – encrypt a password using the RegistryPasswordUtils class (used to encrypt dm_bof_registry user)
  • decryptPassword() – decrypt a password encrypted with the encrypt() method
  • runDQLQueryReturnSingleValue() – run a query that returns a single value, for example:  select count(*) from …
  • isSysObject() – tests it an IDfPersistentObject is a sys_object or not
  • createLogFile() – create a log file and return it to the caller as a PrintWriter
  • other misc updates, changes, corrections, and refactoring

For additional information regarding “The Basics” series, search for “The Basics“.

 

 

 

IDfCollections, Part II

In the previous post, we looked at the basics of processing the contents of an IDfCollection object. In this post I will show how to process a generic IDfCollection, and how to determine if an IDfCollection is empty.

First, the code for processing a generic IDfCollection. The following method will take as input, an IDfCollection object of which it knows nothing about. It will “explore” the collection and retrieve the attribute values it contains according to the column types. You might embed code like this in your application as a generic way to extract IDfCollection content. In this example, I just print the content as it is extracted. This limits the method’s usefulness as a generic process, but it works well as an illustration.  You can change it to do something more meaningful.

private void processGenericCollection(IDfCollection col) {

    // #6 check for empty collections
    boolean isEmpty = true;

        try {
            // #1 print column names
            int colNum = col.getAttrCount();
            for (int i=0; i
               System.out.print(col.getAttr(i).getName() + "\t");
            }
            System.out.println();

            // #2 print contents of each row
            while (col.next()) {

                // #7 collection is not empty
                isEmpty = false;

                // #3 process each column individually according to its data type
                for (int j=0; j<colNum; j++) {

                // #4 get col data type
                int colType = col.getAttr(j).getDataType();

                // get col value based on type
                String colValue = "";
                if (colType == IDfType.DF_BOOLEAN)
                   colValue = Boolean.toString(col.getBoolean(col.getAttr(j).getName()));
                else if (colType == IDfType.DF_DOUBLE)
                   colValue = Double.toString(col.getDouble(col.getAttr(j).getName()));
                else if (colType == IDfType.DF_ID)
                   colValue = col.getId(col.getAttr(j).getName()).toString();
                else if (colType == IDfType.DF_INTEGER)
                   colValue = Integer.toString(col.getInt(col.getAttr(j).getName()));
                else if (colType == IDfType.DF_STRING)
                   colValue = col.getString(col.getAttr(j).getName());
                else if (colType == IDfType.DF_TIME)
                   colValue = col.getTime(col.getAttr(j).getName()).toString();

                // #5 print value  System.out.print(colValue + "\t");
                }
                System.out.println();
            }
        } catch (DfException e) {
            e.printStackTrace();
        }
        col.close();

        // #8 process empty collection
        if (isEmpty)
          System.out.println("The IDfCollection contains no results (zero rows)");
     }
  1. Before executing the first next() method, you can “explore” the IDfCollection and retrieve information about the columns.  Unfortunately, you can’t retrieve information about the rows, like, how many there are (We’ll look at that next week).  In this example, I just print the names of the columns in the IDfCollection (these will correspond to the values in the SELECT statement).
  2. Now execute the next() method to advance the row pointer in the IDfCollection.  The col object now represents the first row in the IDfCollection.
  3. Process each column of the current row according to its type.  To do this, iterate over the columns again.  The columns are represented by IDfAttr objects.
  4. Simply get the type of each IDfAttr represents and use an if statement to extract the value using the proper get method (Types are represented as integer constants.  Constants are discussed here.).
  5. Print the value.  Obviously, this is the step in the code where something more interesting needs to happen to make this method worthwhile.  I will expand this code in future posts to do something more useful here.

As I mentioned in #1, you can’t determine how many rows an IDfCollection has by interrogating it — or if it has any at all. Unfortunately, an empty collection is not returned as null from the IDfQuery.execute() method because it contains column definitions.  Therefore, testing for a null IDfCollection object will not indicate an empty collection.   The next few bullets present a simple flag that can be set to determine if a collection is empty (i.e., the query returned no results), as opposed to some other error that returned an empty IDfCollection object.

  1. Define a boolean variable to act as the flag for an empty IDfCollection object and set it to true (assume the collection is empty until content is actually retrieved).
  2. Once the next() method executes for the first time, set the flag to false.  If the collection is in fact empty, the code in the while loop will not execute.
  3. Simply test the value of the flag variable and act accordingly.

This code is a direct expansion of the basic processing loop presented in the previous post.  It has been enhanced  to accommodate a generic collection (i.e., not knowing what the column names or types are), and to handle empty collections.  Next week I’ll show you two methods for determining the size of an IDfCollection object.

Links to All of My Source Code

Here are links to all of the source code and projects I have discussed on my blog.

Don’t forget about all of my articles on the Publications page and the cool tools on the Tools page.

D2 v4.5 DQL Editor Widget – Part 4

In the last post of this series I will discuss how to install and configure the D2 DQL Editor external widget. To recap:

  • Part 1 – overview of widget design,
  • Part 2 – discussion of JavaScript and OpenAjaxHub implementation,
  • Part 3 – discussion of Java servlet to run query and format results.

Installation and configuration of the D2 DQL Editor widget occurs in three easy steps:

  1. First, install the D2DQL.war file on your application server (I only tested with Tomcat). The WAR should contain all of the necessary DFC, dmRecordSet, and DCTMBasics JARs (in /WEB-INF/lib), in addition to the DQLQueryServlet.class class file, D2DQLEditor.jsp JSP file, and DQL.css style sheet.  The directory structure should look like this:
    • ../webapps/D2DQL
      • D2DQLEditor.jsp
      • /META-INF
      • /resources
        • DQL.css
        • D2-OAH.js
        • OpenAjaxManagedHub-all.js
      • /WEB-INF
        • /lib
          • aspectjrt.jar
          • certFIPS.jar
          • commons-lang-2.4.jar
          • DCTMBasics.jar
          • dfc.jar
          • dmRecordSet.jar
          • jsafeFIPS.jar
          • log4j.jar
        • /classes/com/dm_misc/D2
          • DQLQueryServlet.class
  2. In D2-Config, configure a new widget using the settings below (you may need to adjust the URL for your environment).  See the EMC Documentum D2 v4.5 Administration Guide for details on configuring new widgets D2 configurations.
    • Name:  D2DQL
    • Label and Description:  D2DQL
    • Widget Type: ExternalWidget
    • Widget URL:  http://localhost:8080/D2DQL/D2DQLEditor.jsp?user=$USER&docbase=$DOCBASE
    • Bi-directional Communications:  checked
    • Communication Channels:  D2_ACTION_DM_TICKET_GENERATE

D2DQL-D2-Config

  1. Configure the D2DQL widget on a D2 workspace and configure it in the Configuration Matrix appropriately.

Login to D2, open your workspace, and run a query.

D2DQLEditor

You can download the WAR file and all of the source code for the D2 DQL Editor here.

I hope this series of blog posts on building the D2 DQL Editor have been valuable to you.  I find the widget itself useful and the experience of developing it incredibly valuable.  I hope to build additional D2 external widgets using this model in the future.

Leave me a comment, I’d be happy to hear your thoughts.

D2 v4.5 DQL Editor Widget – Part 3

In the last two posts I introduced you to my D2 DQL Editor external widget. In Part 1, I discussed the general workings of the widget. In Part 2, I discussed the JavaScript and OpenAjaxHub code required to request and receive login tickets via D2’s bi-directional communication channels. In this post I will briefly discuss the Java servlet that the widget calls to execute the DQL query and format the results.

There isn’t anything surprising in the Java servlet code. Instead of showing all of the servlet code, I will just highlight some areas that are noteworthy.

  • The servlet extends HttpServlet; nothing special.
  • Note the use of the DCTMBasics to login and run the query
  • Note the use of dmRecordSet classes to simplify the processing of the query results. The use of this class provides several nice capabilities:
    • The number of rows returned by the query can be determined from the collection without having to run a second query with the count(*) in the selection criteria.
      // get record set
      dmRecordSet rs = new dmRecordSet(col);
      
      // if results do this
      if (rs.getRowcount() > 0)
         output.append("<h3>Rows returned: " + rs.getRowCount() + "</h3>");
      
    • You can easily print the column names returned in the collection without knowing them ahead of time by iterating over the dmRecordSet.getColumnDefs() ArrayList.
      ArrayList&lt;IDfAttr&gt; cols = rs.getColumnDefs();
      
      // print col names as headers
      output.append("<tr>");
      output.append("<th>Row No.</th>");
      for (IDfAttr a : cols) {
        output.append("<th>" + a.getName() + "</th>");
      }
      output.append("</tr>");
      
    • Likewise, you can easily iterate over the entire collection by using the column name ArrayList to retrieve the value of each column from each row.
      while (rs.hasNext()) {
        IDfTypedObject tObj = rs.getNextRow();
        output.append("<td>" + (rs.getCurrentRowNumber() + 1) + ".</td>");
        for (IDfAttr a : cols) {
          output.append("<td>" + tObj.getString(a.getName()) + "</td>");
        }
        output.append("</tr>");
      }
      
      

You can download the WAR file and all of the source code for the D2 DQL Editor here.

D2 Developer White Papers

This past week EMC published three new D2 technical white papers on their support site.

These white papers are short and succinct, making them quick, easy reads to whet your appetite for digging into D2.  They also include some source code examples.

More source code examples can be found in the D2FS_API_White_Papers_and_Samples.zip file in the D2 product folder on subscribenet.  This ZIP file contains the three white papers listed above, and in addition, a white paper entitled: D2 4.X Plugins and Extensions.  This white paper provides a tutorial and template/framework for building D2 plugins.

Enjoy.

%d bloggers like this: