UCM Externalized SQL statements

Why use UCM externalized SQL statements
I would recommend to always use externalized UCM queries unless absolutely impossible. Why?

- Security:: Because the externalized UCM queries mechanism guards against sql injection
- Maintainability:: Because the queries are not inside your compiled Java but stored seperately in a resource file
- Extensibility:: Because they are stored/defined in a resource file they can be overwritten by another component

Even though the externalized UCM statements look like PreparedStatements, they are in fact not (they are regular Statements). But if you would really like it to be a prepared statement this is also possible. Just add (PREPARED) in front of the query name in your definition file. Don't rush off and make all your externalized UCM statements prepared. See an example below:

<td>(PREPARED)QDatabaseObject</td>
<td>select 'x' from user_objects where object_name = ?</td>.
<td>theObjectName varchar</td>

Read some articles to see when it is useful to use a prepared statement for performance. That is outside the scope of this post.

UCM externalized SQL statement parameters
The component wizards helps you declare your externalized statements and their parameters. The parameter types that the component wizard allows you to chose are: varchar, boolean, int and date. Besides these very, very basic parameter types there is a whole world of options possible. I bet you can move 95% of all your dynamic SQL to externalized UCM statements.

The supported parameter types are:
- int (an integer)
- char (single character)
- varchar (multiple characters; String)
- boolean (boolean value)
- date/datetime (date/time value)
- decimal (decimal value/floating point)
- blob (binary large object)
- clob (character large object)
- column (column name)*
- table (table name)**
- resultset (resultset)***

* this'll allow you to dynamically retrieve a column
example:: (use case):: You want to create generic SQL to retrieve custom user metadata field value
example:: (statement):: select ? from users u where u.dname = ?;
example:: (values):: 'uSomeField' and even 'u.*' are valid
** this'll allow you to dynamically select from a table
example (use case):: You want to retrieve the total records in a table
example (statement):: select count(*) from ?;
example (values):: 'users' or 'revisions'
*** this is used predominantly for out parameters of callable procedures/functions

SQL IN statements
There is a special notation for passing a list of items. This is VERY useful. For instance, you want to retrieve all record with multiple particular states, then this works wonders (in many cases this is where I would have made a dynamic SQL query). The IN construct (<parametertype>.in) deserves some extra attention since you need to put multiple values into one variable. First there is a query definition:

<td>myQuery</td>
<td>select count(*) from revisions where dstatus in ( ? )</td>
<td>myStatuses char.in</td>

Now to fill variable myStatuses, you can use the following Java code:

// create a list of values
List<String> myStatusesList = new ArrayList<String>();
myStatusesList.add( "S" );
myStatusesList.add( "F" );
 
// use StringUtils.createStringSimple to create your list-string
String myStatusesString = intradoc.common.StringUtils.createStringSimple( myStatusesList );
m_binder.putLocal( "myStatuses", myStatusesString );

ResultSet results = m_workspace.createResultSet( "myQuery", m_binder );

Default Values
Last but not least, it is possible to define a define value for your parameters in your UCM externalized SQL statement definition. The following structure can be used for defining a default value.

<parameter_name>[:<default_value>] parameter_type

An example would be:

<td>queryWithDefault</td>
<td>select ? from docmeta</td>.
<td>myParam:xComments column</td>

Overview
To summarize this post, the structure of parameter types in an externalized SQL statement in UCM:

<parameter_name>[:<default_value>] [<(in)/out/inout>:]<parameter_type>[.in]

Gain access to UCM log messages

The Problem
If you have implemented the LogWriter interface in UCM 10g (or before) you may have noticed it does not quite work in UCM 11g as expected (most error log messages never seem to come through the LogWriter implementation, though some low level stuff does). An example of the LogWriter use can be found in the RssFeeds component that is freely available on OTN (for 7.5 and 10g). This post will discuss the UCM 11g (and probably also UCM 10g) approach for gaining access to the messages that are written to your logs.

UCM 10g - LogWriter interface
The LogWriter interface is a very simple interface with just one method. The code below shows a very typical LogWriter implementation. The RssFeeds component contains a more detailed implementation of how you could use this interface.

public class SampleLogWriter implements LogWriter {
 
 public SampleLogWriter () {
  // do your initialization here
 }

 @Override
 public void doMessageAppend (
  final int logLevel,
  final String message,
  final LogDirInfo context,
  final Throwable exception
 ) {
  try {
   // do something with your logging here 
   // note: don't log anything from here on! (trace is safe)
  } catch ( final Throwable someException ) {
   someException.printStackTrace();
  }
 }

}

And yes, you want to catch ALL possible RunTimeExceptions/Errors, otherwise you are very likely to end up in an infinite loop if your code for some reason throws something (like a NullPointerException or an AssertionError). Obviously, don't log an error in your log message handling code either.

To configure its use you had to create an entry in your content server configuration (config.cfg) as described below and it would work.

ExtraLogWriter=my.SampleLogWriter

UCM 11g - ReportHandler interface
As described in the introduction, the LogWriter no longer functions propertly in UCM 11g. Luckily there is a good alternative that gives you access to all messages written to the logs... and even more! Lets show you a very typical implementation of the ReportHandler interface.

public class SampleReportHandler implements ReportHandler {

 public SampleReportHandler() {
  // you may do some initiation of your report handler here
 }

 @Override
 public void init ( final Map settings ) {
  // you can use some settings here to initialize (further)
  // (called prior to the first call to the message() method) 
 }

 @Override
 public boolean isActiveSection ( final String traceSection ) {
  // whether or not the given tracing section is active in this
  // report handler
  return true;
 }

 @Override
 public void message (
  final String application,
  final String section,
  final int logLevel,
  final IdcMessage message,
  final byte[] bytes,
  final int offset,
  final int length,
  final Throwable throwable,
  final Date date
 ) {
  try {
   // do something with your logging here
   // note: don't log OR trace anything here!
   if ( logLevel >= Report.LEVEL_TRACE ) {
    return;
   }
   // after the above if statement tracing in your custom code is safe
  } catch ( final Throwable someException ) {
   someException.printStackTrace();
  }
 }
}

Again, catch all to avoid infinite loops. And again, don't log any error in your code. But this time... also don't trace (even verbose) because the ReportHandler handles all messages.

Configuring the content server to pick up your ReportHandler implementation and use that, there is - unfortunately - no simple config parameter. The good thing is that you can set this up in your component by editing your component hda and adding the following result set at the end of your <component_name>.hda file.

@ResultSet StateCfgExportedVars
2
name
value
JAVA_OPTIONS_sample_reporthandler
${DEFINE_PREFIX}idc.report.sample.implementor=my.SampleReportHandler
JAVA_CLASSPATH_sample
$COMPONENT_DIR/classes/
@end

The first entry in the result set adds a java option setting up your report handler class as a report implementation. The second entry makes sure the classes directory in your component is part of the java classpath. This last entry is needed because this will be setup before all components are loaded (thus try not to have any dependencies on libraries or code outside of the classes folder and the default loaded libraries such as IdcServer.jar).

Important Differences

• The log levels use different backing integers, don't mix them up; LogWriter uses the constants setup in intradoc.common.Log (ie. Log.m_warningType = 3) whereas ReportHandler uses the constants setup in intradoc.common.Report (ie. Report.LEVEL_WARNING = 4000).
• The ReportHandler method message contains more parameters than the doMessageAppend method in the LogWriter interface (luckily all parameters from the LogWriter have an (almost) equivalent parameter in the ReportHandler).
• As said before, the ReportHandler has access to much more than the logging messages, this includes but is not limited to trace messages.
• And the most important difference is ofcourse that LogWriter does not function properly in UCM 11g, whilst the ReportHandler does work in UCM 11g and also seems available in UCM 10g (although I have not tested if this works correctly).

Homework
Rewrite the RssFeeds component to work in 11g with the ReportHandler interface.

Calling stored procedures from within UCM

The Problem
When creating customizations for UCM (Oracle Universal Content Management), there have been a couple of times that I've wanted to call a procedure or function from within a service or filter (ie. to make use autonomous transactions for logging stuff). This post will show you how this can be done (without having to resort to using workarounds like making the procedure into a function and select it from dual).

The procedure
So lets create an interesting - yet compact - procedure that contains in, in-out and out parameters (as this is where the workaround can't be applied anymore). So here it is; my very own example_procedure:

create or replace procedure example_procedure (
   param1 in number,
   param2 in out varchar2,
   param3 out number
) as
begin
   param2 := param2 || param1;
   param3 := param1 + 2;
end example_procedure;

The query (definition)
Now we will define the query we want to call using the Component Wizard (note that you need to use Launch Editor and manually edit the entry because these options can't be selected from a dropdown menu). I am going to call my query exampleQuery and have defined it as:

<tr>
    <td>(ORACLE.CALLABLE)exampleQuery</td>
    <td>{call example_procedure( ?, ?, ? )}</td>
    <td>param1 int
        param2 inout:varchar
        param3 out:int</td>
</tr>

There are several interesting things about this definition that you need to remember:

• There is an (ORACLE.CALLABLE) tag in front of the query name; I believe this prepares the query parser for a procedure/function call.
• The statement itself is between brackets and is preceded by the word call; the syntax to call a procedure or function
• Two parameters have a prefix (inout: and out:); they are pretty self explanatory

The custom Java code 
Finally there is the Java code necessary to call our fancy procedure.

private void testProcedureCall (
   final Workspace workspace,
   final DataBinder binder
) throws DataException {

   // prepare the "in" parameters
   binder.putLocal( "param1", Integer.toString( 33 ) );
   binder.putLocal( "param2", "some test string" );

   // the magical moment; calling the procedure
   final CallableResults myResults = workspace.executeCallable( "exampleQuery", binder );

   // retrieve the "out" parameters
   final String myInOutParameter = myResults.getString( "param2" );
   final int myOutParameter = myResults.getInteger( "param3" );

   // now you can use the output parameters however you like!

}

The above is not that special, except for the executeCallable call. It's use (and arguments) are exactly equal to a createResultSet or execute call on the workspace, except that you get a CallableResults from which you can retrieve the parameters.

And... that should do the trick!