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]