How To: Adding an external database AJAX lookup to a UCM checkin form

by | May 28, 2010 | Integrations

A topic that comes up quite frequently on the OTN and intradoc_users forums as well as in discussions with some of our customers is “How do I add custom functionality to the checkin page?”  This usually revolves around an integration with an external database to lookup values for metadata in order to keep the systems in sync or relate the items in some way.  Since I’ve done similar customizations on several occasions I figured why not pull some of that UCM code together for a little How To article.

Some of the UCM development concepts that will be touched on in this post:

  • Custom Components
  • Custom Services
  • Database Providers
  • Custom Query Resources
  • Custom Java Service Handlers
  • MergeInclude
  • Resource Includes

By the end of this post you should have a working lookup to an external database on your checkin page.

A few assumptions before we start:

  • The reader is familiar with basic UCM component architecture
  • The reader is familiar with creating components using the Component Wizard
  • The reader is familiar with creating a database provider in the UCM interface
  • The reader knows how to create a custom class and include it in a component.
  • This post will focus on the pieces of code needed to implement the solution

For info on components and providers check out the documentation here and here.

The basic steps we will run through in this post are as follows:

  1. Create a custom component
  2. Create a service
  3. Create a query resource
  4. Create a database provider
  5. Update the provider config
  6. Create a custom java service handler
  7. Create the ServiceHandler definition
  8. Create the resource includes

1. Create a custom component

On your UCM development machine open up the Component Wizard from either the Start Menu or $IntradocDir/bin.  Once there Click Add and create a new Component called “MyExternalLookupComponent” or whatever you prefer.  Code examples further on will assume that component name, make changes as appropriate.

2. Create a service

Once your component is created click Add on the Resource Definition tab and pick Service to create a new Content Server Service.  Creating a custom service gives us a container to put our logic for retrieving info from the external database and formatting the response that we will read in on the checkin page.   Fill out the Add Service dialog with the following:

  • Name: MY_EXTERNAL_LOOKUP
  • Service Class: Service
  • Template: <empty>
  • Service Type: <empty>
  • Access Level:  Read
  • Subjects Notified: <empty>
  • Error Message: Unable to retrieve external lookup info

Add a Service action with the following values:

  • Type: Java method
  • Action: executeExternalLookup
  • Parameters:
    MyExternalProvider,QexternalInfo,MyExternalLookupRS
  • Error Message: Error retrieving external lookup info

Once you are done your service definition file under $IntradocDir/custom/MyExternalLookupComponent/resources/myexternallookupcomponent_services.htm should contain the following:

[sourcecode language=”html”]
<@table MyExternalLookupComponent_Services@>
<table border=1><caption><strong>Scripts For Custom Services</strong></caption>
<tr>
<td>Name</td><td>Attributes</td><td>Actions</td>
</tr>
<tr>
<td>MY_EXTERNAL_LOOKUP</td>
<td>Service
1
null
null
null<br>
Unable to retrieve external lookup info</td>
<td>3:executeExternalLookup:MyExternalProvider,QexternalInfo,MyExternalLookupRS::Error retrieving external lookup info</td>
</tr>
</table>
<@end@>
[/sourcecode]

The 3 parameter values on the service action map to:

  • The name of our database provider (to be added in step 4)
  • The name of our query resource (to be added in step 3)
  • The name of the result set to return and be added to the binder (used in steps 6 and 8 )

3. Create a query resource

Once we have our service defined we need to create the query that will be used to retrieve data from the external database.  Using a query resource allows us to keep the SQL from being hard-coded in our Java source, thereby making it easier to change.  A query resource also means that the query is executed using a Java PreparedStatement, giving a performance boost over directly including SQL in our code.

In Component Wizard click Add on the Resource Definition tab again and select “Query”.  Enter your SQL and parameters to that SQL.  Your query definition file: $IntradocDir/custom/MyExternalLookupComponent/resources/myexternallookupcomponent_query.htm should contain code similar to below:

[sourcecode language=”html”]
<@table MyExternalLookupComponent_Queries@>
<table border=1><caption><strong>Query Definition Table</strong></caption>
<tr>
<td>name</td><td>queryStr</td><td>parameters</td>
</tr>
<tr>
<td>QexternalInfo</td>
<td>SELECT * FROM SomeTable WHERE lookupField = ?</td>
<td>lookupField varchar</td>
</tr>
</table>
<@end@>
[/sourcecode]

Query parameters are specified by question marks (?) in the SQL statement.  Their names in the parameters box are references to variables in the DataBinder.  In a code sample further on we will show how the “lookupField” variable is added to the binder.

4.  Create a database provider

In order for the content server to connect to to a different database a provider needs to be added.  This can be achieved through the Provider page under the UCM Administration menu.  The provider name used for this example is “MyExternalProvider”.

5. Update the provider config

Once you have added the new provider and restarted the content server there are a couple “undocumented” additions that need to be made manually to the provider’s configuration.  In a text editor open up the provider.hda file for the provider you just created.  This file is under $IntradocDir/data/providers/myexternalprovider/.

First we need to add the lines that tell our provider where to find our query resource we defined in step 3.  Add the below 3 lines at the top of the LocalData section (adjust directory location and table name accordingly).

[sourcecode language=”text”]
ProviderConfig=intradoc.server.DbProviderConfig
QueryResourceFile=c:/stellent/10gr3/custom/MyExternalLookupComponent/resources/myexternallookupcomponent_query.htm
QueryResourceTables=MyExternalLookupComponent_Queries
[/sourcecode]

IMPORTANT: The QueryResourceFile variable is an absolute directory location to the file containing our query resource.  When you deploy this component to your production box this must be changed to reflect the location of the file on that server.  When you create the provider on production (or any other server) make sure this is the correct location.  Make sure to restart the content server after changing provider.hda

The other lines that need to be added to the provider.hda are to workaround a bug with the 10gr3 version of UCM that Oracle has never bothered to fix (because this easy workaround exists).  At the bottom of provider.hda add the following lines:

[sourcecode language=”text”]
@ResultSet ColumnMap
2
column
alias
@end
[/sourcecode]

6. Create a custom java service handler

A ServiceHandler class is where we write custom java methods that can be used as actions on a UCM service.  It provides a mechanism to reuse methods between multiple services without having to write a custom service class.  It also allows you to reuse those service methods between components as long as the component that contains the service handler is also enabled on the content server.

Open up the IDE of your choice (I recommend Eclipse) paste in the following code, compile and make sure that the class file is output in the /classes directory under your component directory.  Note:  UCM 10gr3 runs on version 1.5 of the JVM.  Make sure that your classes are compiled with compatibility for that version turned on.

[sourcecode language=”java”]
package com.mycompany.externallookup;

import intradoc.data.DataException;
import intradoc.data.DataResultSet;
import intradoc.data.Workspace;
import intradoc.provider.Provider;
import intradoc.provider.Providers;
import intradoc.server.ServiceHandler;
import intradoc.shared.SharedObjects;

public class MyExternalProviderHandler extends ServiceHandler
{
public void executeExternalLookup() throws DataException
{
String providerName = m_currentAction.getParamAt(0);
String queryName = m_currentAction.getParamAt(1);
String returnRsName = m_currentAction.getParamAt(2);
Workspace ws = getProviderWorkspace(providerName);
DataResultSet drs = new DataResultSet();
drs.copy(ws.createResultSet(queryName, m_binder));
m_binder.addResultSet(returnRsName, drs);
}

private Workspace getProviderWorkspace(String provider)
{
Workspace workspace = null;
Provider wsProvider = Providers.getProvider(provider);
if(wsProvider != null)
{
workspace = (Workspace)wsProvider.getProvider();
}
return workspace;
}

}
[/sourcecode]

You will notice that the name of our method maps to the value we entered for “Action” when we created the service action.  The above code reads in the parameters from our service, gets a reference to the external provider, executes the query, and adds the results back to the binder to be used later.

7. Create the ServiceHandler definition

In order for our service to be able to execute the method in our ServiceHandler class we need to add a resource definition for the ServiceHandler that is merged to the internal ServiceHandlers table in the content server.  When a service action is called the content server looks for the method first in the class defined in the “Service Class” for that particular service.  If it isn’t found there is checks the ServiceHandlers table to see if the method is defined in any custom handlers for the service.

To create this definition Click “Add” on the Resource Definition tab in Component Wizard and select “Resource – Static Table (HTML Format)”.  For Table Name add “ServiceHandlers” after the prepopulated value.  Click the “Merge To” checkbox and select ServiceHandlers from the dropdown.  This will create the $IntradocDir/custom/MyExternalLookupComponent/resources/myexternallookupcomponent_resource.htm file containing the table we just defined with default values.  Open up the file in a text editor and replace what was created with the following:

[sourcecode language=”html”]
<@table MyExternalLookupComponent_ServiceHandlers@>
<table border=1><caption><strong>
<tr>
<td>serviceName</td><td>handler</td><td>searchOrder</td>
</tr>
<tr>
<td>Service</td><td>com.mycompany.externallookup.MyExternalProviderHandler</td><td>10</td>
</tr>
</table>
<@end@>
[/sourcecode]

8. Create the resource includes

Now that we have created the backend for querying our external database via UCM we can put in the UI pieces.  To do so we have 3 resource includes to add to the $IntradocDir/custom/MyExternalLookupComponent/resources/myexternallookupcomponent_resource.htm file created in the previous step.  Resource includes are the pieces of code that are used to render the UCM interface.  The contain a mix of IdocScript (server side evaluated script), HTML, JavaScript, and CSS.  All of the standard UCM pages are built using a set of resource includes.  Using our custom component we can extend or override these components to customize the UI for our needs.

The first include we will look at is to add the Lookup button to the Checkin form itself.  This is done by extending the “std_nameentry_row” resource include.  This include defines the HTML that contains a row on a checkin, update, info, or search screen.  Paste the following into our resource.htm file.

[sourcecode language=”html”]
<@dynamichtml std_nameentry_row@>
<$if strEquals(fieldName, “xLookupField”) and not (isInfo or isQuery)$>
<tr <$strTrimWs(inc(“std_nameentry_row_attributes”))$>>
<td <$if captionFieldWidth$>width=”<$captionFieldWidth$>”<$endif$> <$if isInfo$>align=right<$endif$>><$strTrimWs(inc(fieldCaptionInclude))$></td>
<td <$if isFieldInfoOnly$>colspan=”100″<$endif$> <$if captionEntryWidth$>;width=”<$captionEntryWidth$>”<$endif$>><$inc(fieldEntryInclude)$>
<!– addition –>
<input type=”button” value=”Lookup” onclick=”externalLookup(this.form.xLookupField);”>
<!– end addition –>
</td>
</tr>
<$else$>
<$include super.std_nameentry_row$>
<$endif$>
<@end@>
[/sourcecode]

To add our button we simply put it inside the same table cell to the right of the input field.  We define an onclick handler to call a javascript function called “externalLookup” which looks like this:

[sourcecode language=”javascript”]
function externalLookup(lookupField)
{
var url=”<$HttpCgiPath$>?IdcService=MY_EXTERNAL_LOOKUP&lookupField=” + lookupField.value + “&MergeInclude=my_external_lookup_response&IsJava=1&timestamp=”+new Date().getTime();
var callback =
{
success: function(originalRequest)
{
//get reference to checkin form
var checkinForm = originalRequest.argument.lookupField.form;

//get response as a javascript object
var obj = eval(originalRequest.responseText); //assuming response coming back as JSON

//populate checkin form fields
checkinForm.xFirstFieldToPopulate.value = obj.firstNewValue;
checkinForm.xSecondFieldToPopulate.value = obj.secondNewValue;
checkinForm.xThirdFieldToPopulate.value = obj.thirdNewValue;

},
failure:function(o){},
argument: {lookupField:lookupField}
};
var request = YAHOO.util.Connect.asyncRequest(‘GET’, url, callback);

}
[/sourcecode]

Add this javascript by extending the std_javascript_header_functions like so:

[sourcecode language=”text”]
<@dynamichtml std_javascript_header_functions@>
<$include super.std_javascript_header_functions$>
//function here
<@end@>
[/sourcecode]

The javascript function is where the proverbial “ajax magic” happens.  I used YUI (Yahoo User Interface) library for this example since it is already installed with UCM.  To ensure that the YAHOO.Util.Connect object is included on the checkin page add the following resource include to our resources.htm file:

[sourcecode language=”html”]
<@dynamichtml std_checkin_html_head_declarations@>
<$include super.std_checkin_html_head_declarations$>
<pre><script type=”text/javascript” src=”/idc/resources/yui/yahoo/yahoo-min.js”></script>
<script type=”text/javascript” src=”/idc/resources/yui/yahoo-dom-event/yahoo-dom-event.js”></script>
<script type=”text/javascript” src=”/idc/resources/yui/event/event-min.js”></script></pre>
<script type=”text/javascript” src=”/idc/resources/yui/connection/connection-min.js”></script>
<@end@>
[/sourcecode]

With the YUI connection object added to the page you need to make sure to  modify the externalRequest function to populate the fields specific to your form.  The function itself does an asynchronous request to a URL to pull back data to populate on the form.  The URL contains the following query parameters:

  • IdcService=MY_EXTERNAL_LOOKUP (our service we defined)
  • lookupField=<value from our field> (this is passed in as “lookupField” in our binder where our query picks it up)
  • MergeInclude=my_external_lookup_response (defined below, a specifically defined resource include to render as the response)
  • IsJava=1 (used in conjunction with MergeInclude tells UCM to render the response exactly as the include defines)
  • timestamp=<current time in milliseconds> (helps us avoid browser caching of the ajax response by making the request string “different” every time)

The final piece of the puzzle is our resource include that renders the response as JSON (JavaScript Object Notation).  Sending back the response as JSON allows us to get a native object in our externalLookup function to use to populate our checkin form fields.  The include looks something like:

[sourcecode language=”javascript”]
<@dynamichtml my_external_lookup_response@>
<$if strEquals(IdcService, “MY_EXTERNAL_LOOKUP”)$>
<$exec rsFirst(“MyExternalLookupRS”)$>
({
firstNewValue: ‘<$getValue(“MyExternalLookupRS”, “firstNewValue”)$>’,
secondNewValue: ‘<$getValue(“MyExternalLookupRS”, “secondNewValue”)$>’,
thirdNewValue: ‘<$getValue(“MyExternalLookupRS”, “thirdNewValue”)$>’
})
<$endif$>
<@end@>
[/sourcecode]

Our response include simply grabs values from the first row in the result set returned by our service handler and creates a JSON response.

To run the code make sure that your component is Enabled, restart your content server, then click the Lookup button on your checkin form.  Hopefully everything works!

An example screenshot from one of the implementations we have done.  This is not what the code in this article will look like except for the “Change” button on the page.  Hopefully though this will give you a good example of what can be done.