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.

Advertisements

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 v4.5 DQL Editor Widget – Part 2

In the last post I introduced you to my external D2 DQL Editor widget.  In this post I will discuss the JavaScript used to invoke the OpenAjaxHub and to use D2’s bi-directional communication channels.

The JSP file containing the JavaScript is named D2DQLEditor.jsp.  The HTML portion of the file is very simple and contains code to display the edit box for the DQL, the buttons to run the query and clear the editor box, and a place holder for the query results in the form of a <div> tag.  Note the onload='init();' event attribute in the <body> tag.  The init() JavaScript method initializes the OpenAjaxHub when the page is loaded.

<body onload='init()'>
  <h3>DQL Editor</h3>
  <textarea id="dqlEditor" rows="8" cols="60"></textarea>
  <button onclick="runDQL();">Run DQL</button>
  <button onclick="clearEditor();">Clear Editor</button>
  <hr/>
  <div id="dqlResults"></div>
</body>

I lifted the code that implements and invokes the OpenAjxHub from the GetTicket example in the D2 Widget Samples from Momentum 2014.  The D2 v4.5 Developers’ Guide was also helpful. Follow the logic:

init()

The init() method calls the parseQueryString() method to parse the user and docbase name out of the URL (nothing special there). It then instantiates an OpenAjaxHub and connects the hub to D2. The OpenAjaxHub.connectHub() method takes two callback functions as parameters: one to execute once the connection has been made (connectCompleted()), the other when the widget is activated (onActiveWidget()). The callback for the activated widget is not used.

function init() {
  parseQueryString();
  d2OpenAjaxHub = new D2OpenAjaxHub();
  d2OpenAjaxHub.connectHub(connectCompleted, onActiveWidget);
}

connectCompleted()
Once the hub is connected, the connectCompleted() function is called. If the connection was successful, it calls subscribeToNewTicket() to subscribe the widget to the D2_EVENT_DM_TICKET_GENERATED message.

function connectCompleted(hubClient, success, error) {
  if (success) {
    subscribeToNewTicket();
  } else {
  alert("Hub not connected. " & error);
}

function onActiveWidget(bActiveFlag) {
}

subscribeToNewTicket()

The subscribeToNewTicket() function simply subscribes the widget to the D2_EVENT_DM_TICKET_GENERATED message and passes onNewTicket() as the callback function for the subscription. This means that whenever a D2_EVENT_DM_TICKET_GENERATED message is received for this widget, the onNewTicket() function handles it.

The reciprocal of the subscribeToNewTicket() function, the publishNewTicketRequest() sends a message to D2 requesting a new login ticket be generated. These two functions (subscribeToNewTicket(), publishNewTicketRequest()) implement the bi-directional communication with D2.

function subscribeToNewTicket() {
  d2OpenAjaxHub.subscribeToChannel("D2_EVENT_DM_TICKET_GENERATED", onNewTicket, false);
}

function publishNewTicketRequest() {
  messageToSend = new OpenAjaxMessage();
  d2OpenAjaxHub.sendMessage("D2_ACTION_DM_TICKET_GENERATE", messageToSend);
}

onNewTicket()

onNewTicket() is the callback function for the subscribe event. When the message is recieved, the ticket is extracted from the message object and saved as a JavaScript variable. submitDQL() is then called to extract the query from the HTML form and pass it to the DQLQueryServlet for processing.

function onNewTicket(event, oMessage) {
  ticket = oMessage.get("ticket");
  submitDQL();
}

That is all of the JavaScript concerned with the OpenAjaxHub and communicating with D2. The remainder of the JavaScript code handles submitting the DQL query to the servlet and handling the results. This JavaScript to instantiate and use the OpenAjaxHub is pretty generic and can be re-purposed for building other external, bi-directional D2 widgets.

submitDQL()

The submitDQL() function extracts the DQL query syntax from the HTML textarea control, combines it with the user name and docbase name parsed from the URL, and the the ticket received by the onNewTicket() callback function, and submits it to the DQLQueryServlet using a little AJAX.

function submitDQL() {
  var dql = document.getElementById("dqlEditor").value;
  dql = encodeURI(dql);
  if (dql !== "") {
    var xmlhttp;
    if (window.XMLHttpRequest) {
      // code for IE7+, Firefox, Chrome, Opera, Safari
      xmlhttp = new XMLHttpRequest();
    } else {
      // code for IE6, IE5
      xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }

    // setup AJAX callback
    xmlhttp.onreadystatechange = function () {
      if (xmlhttp.readyState === 4 && xmlhttp.status === 200) {
        // set the query results on the div tag place holder
        document.getElementById("dqlResults").innerHTML = xmlhttp.responseText;
        document.body.className='default';
      }
    };

    // call servlet
    xmlhttp.open("POST", "DQLQueryServlet", true);
    xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    xmlhttp.send("user=" + user + "&docbase=" + docbase + "&dql=" + dql + "&ticket=" + ticket);
    document.getElementById("dqlResults").innerHTML = "Submitting query for execution...";
  } else {
    document.getElementById("dqlResults").innerHTML = "DQL statement cannot be blank";
    document.body.className='default';
  }
}

runDQL()

The runDQL() function is called when the Run DQL button is clicked on the widget. Because a new ticket is required for every query, this function simply requests a new ticket. Remember that the onNewTicket() callback function of the subscription to the D2_EVENT_DM_TICKET_GENERATED message calls the submitDQL() function, which runs the query.

function runDQL() {
  publishNewTicketRequest();
}

In the next post I will discuss the Java and DFC code used in the servlet to process the DQL query passed from the widget, and to return the results. You can download the D2 Configuration, the WAR file, and all of the source code for the D2 DQL Editor here.

D2 v4.5 DQL Editor Widget – Part 1

One function of Webtop that I really miss in D2 is the DQL Editor.  So, as an exercise to learn how to create external D2 widgets that use bi-directional communication, I decided to build one.  Recall that the last D2 widget I build generated a bar code for the selected object in the Doclist widget, but did not employ bi-directional communication with D2 or the Docbase.

The D2 DQL Editor looks and functions similarly to the DQL Editor in Webtop (see figure).  And, as it turned out, was not too difficult to build, once I understood the OpenAjaxHub and its callback structure.  The rest of the widget is implemented as a JSP page with a Java servlet behind it.

DQL Editor

D2 loads external widgets into iFrames.  As part of the configuration of this widget, D2 passes the name of the current user and Docbase in the URL.  JavaScript in the widget parses this information out of the URL when it loads and saves it for use later.  The JSP then instantiate the OpenAjaxHub and subscribes to the D2_EVENT_DM_TICKET_GENERATED message.  When a DQL query is run, the widget publishes a request for a login ticket on the OpenAjaxHub.  Once the ticket is received, the query, the ticket, the user, and the Docbase name are passed to the servlet to be run.

In the following three posts (Parts 2, 3, and 4) I will discuss in more detail the various components of this external D2 widget.

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

An Excel-based DQL Query Tool

One of the items on my personal to do list was to create a simple DQL query tool using Excel.  To me, it seemed like a natural fit:  Excel was adept at displaying tabular data, it had a robust scripting language to implement the logic (VBA), and VBA was dot-Net-compliant so it could load the DFC PIA directly.  The only problem is that I waited too long to create this tool and the DFC PIA is no longer distributed or supported by EMC.

Instead of using the DFC PIA, I discovered Obba — a framework for integrating Java with spreadsheets, both Excel and Open Office.  It is a little “heavier” solution than I had hoped to create but useful and interesting nonetheless. I had envisioned the DQL Query Tool as more of an Excel Add-in or something as portable.  Obba launches a small, local, Java-based server to handle the integration instead of handling the DFC calls internally as the PIA would have.

You can download the Excel DQL Query Tool here  if you are interested.  I found that VBA/Obba programming takes a little getting used to in the way it handles variables, objects, and methods, but after a few examples it makes sense.

For details regarding installing and running Obba, see the Obba website (www.obba.info). For details regarding configuring and running the Excel query tool, see the README file included in the download.

Query Screen

Query Screen

Results Screen

Results Screen

Next, I think I will pursue creating a tool using the DFS RESTful services.  For this interface I will likely use Excel-REST.  Hopefully that integration will prove to be a little “lighter weight” than this one.

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");

DAM DQL ESA

EMC has issued ESA-2014-024: EMC Documentum Digital Asset Manager DQL Injection Vulnerability. 

The DAM thumbnail proxy server allows unauthenticated users to query objects using a vulnerable URL query string parameter. A malicious attacker may potentially conduct Blind DQL injection attacks using the vulnerable parameter to infer or modify the database contents.

Affected products are:

  • Documentum Digital Asset Manager 6.5 SP3
  • Documentum Digital Asset Manager 6.5 SP4
  • Documentum Digital Asset Manager 6.5 SP5
  • Documentum Digital Asset Manager 6.5 SP6

A hot fix is available for DAM 6.5 SP3 – SP5. A patch is available for DAM 6.5 SP6.
The hotfix for DAM 6.5 SP3 – SP5 can be downloaded from:

The patch, DAM 6.5 SP6 P13, can be downloaded from:

New DQL Helper Library

I just found this very interesting DQL helper library.  I haven’t had the opportunity to use it yet, but I look forward to it.  Coupled with my dmRecordSet class (original post), these two helpers really have the potential to boost the programmability of DQL.  See what you think: https://community.emc.com/docs/DOC-23426

%d bloggers like this: