By: Dwayne Parkinson - Solution Architect
The past few Blog entries have covered some WebCenter Content building blocks that are necessary before we begin customizing the system. In this entry we will explore the query resource which is that last of our basic building blocks before we start gluing everything together and creating our own fully functional custom components.
The query resource allows a component store and execute any number of custom SQL queries. The SQL queries run directly against Content Server’s database. The queries defined in the query resource can be executed from a service, Idoc Script, or Java and are therefore incredibly useful.
The query resource requires some information about Content Server’s database. To develop query resources you will need a connection to the Content Server’s database and some sort of SQL tool like Oracle SQL Developer. Once you have established a connection to your server you should see tables similar to those shown below.
Content Server Tables in Oracle SQL Developer
Creating a query resource will require knowledge of the existing tables and SQL. A few of the most popular tables used for customizations are:
- DOCMETA: Metadata (including custom metadata fields) for all content items on the content server.
- DOCUMENTS: The document name and other document related information.
- REVISIONS: Information about each revision of a content item such as the document type, title, creation date, author, etc.
- USERS: Contains information about the user profiles in the system.
The query resource may also define new tables and access those tables, but that’s beyond the scope of this Blog entry.
Before creating a query resource for a component, we can look at Content Server’s core query resource for a good reference. Content Server’s core query resource contains hundreds of queries that may be used in a component. The core query resource can be accessed at <install_dir>/ucm/idc/info/core/query.htm.
Content Server’s Core Queries
The query resource above is the HTML representation of the query definition. Notice that the table is broken into three columns. The columns are name, queryStr, and parameters. Although with the Component Wizard these will be managed automatically, knowing the columns is useful.
Query Resource Columns
| Column | Description |
| name | The name column specifies the name for the query. The name needs to be unique. If the name is not unique, it could override an existing query. It is recommended that a convention is used to uniquely identify queries. See the naming standards below. |
| queryStr | The queryStr column specifies the SQL query. The SQL query can update, delete, insert, or select rows from the database. Parameters may be added to the query by using a ‘?’. |
| parameters | The parameters column specifies all of the parameters for the query. The parameters must have a name and data type associated with them. The order of the parameters is important. |
When you are creating a custom component with a query resource, the SQL queries defined in the query resource should have a unique name. Content Server generally follows a standard naming convention to help manage the types of queries. When adding a query resource to a component, it is recommended that a prefix convention is added to the name of the query as well. For example, a query inside of the component ‘exampleComponent’ may be QfullName. The table below shows the standard prefixes throughout Content Server.
Standard Query Naming Convention
| Prefix | Description |
| Q | SQL queries that select row(s) from a database table. |
| U | SQL queries that update row(s) in a database table. |
| D | SQL queries that delete row(s) from a database table. |
| I | SQL queries that insert a row into a database table. |
Once a component has been created, a query resource can be added from the Add Resource form. Notice that the query will be stored in an htm page within the /info folder of the component.
Add Resource Form
By selecting the query resource, the file name should change to follow the standard “_query.htm” convention. The query resource can be used to define new queries, override existing queries, and expand existing queries.
An existing query may be loaded and edited by selecting one by name from the list of queries for a particular query resource or a new query may be added. As expected the Add Query form requires a Query name to be specified. The Edit Query form does not have the opportunity to specify the query name, but otherwise it is identical. In both cases you must provide the SQL query text, and optionally specify parameters.
Adding a Query to the Resource
The Component Wizard will then add the new query to the query resource and behind the scenes a “table” will be created to store the information. It’s important to note that if you want to rename a query you must manually edit the htm files created by the Component Wizard. For example if you wanted to change the query above to be QgetFullName once it had been created you would edit the html tables manually.
Beyond the query resource file, the Component Wizard will automatically add the query resource to the glue file as well.
| @ResultSet ResourceDefinition4type filename tables loadOrder query resources/examplecomponent_query.htm exampleComponent_Queries 10 @end |
The example above shows an example of a query resource, the path to the resource, the query table name, and the load order.
The use of parameters is important in queries. Content Server supports the use of parameters in queries by using a “?” place holder for each parameter. Each of the parameters used in the SQL query must associate with a parameter that is defined in the parameter list for the query.
Adding a Parameter to the Query
The query above requires that a User Name is provided. The query will need to have a parameter added to the list of parameters before it will run successfully. Notice that in the example only one parameter is used, but with multiple parameters the order of the parameters in the list is important and the interface allows the parameters to be shifted up and down by using the Up and Down buttons.
Adding the Parameter to the Resource
The parameters will need to have a name and type specified. The types that are available are directly associated with the database data types. The type needs to match the data type that exists in the database table.
The name, queryStr, and parameters will then be added to the HTM file which stores the query information. The columns will match all of the information that was specified inside of the Component Wizard’s interfaces.
The HTML representation of the query resource
Now that we’ve got a query defined we need some way to execute the query. As previously mentioned, we could execute the query from Java, iDoc Script or from a Service. In the next Blog entry we will go into more details on creating a service and executing our query. For now it’s important to know that when you create a service that executes a query you have to tell the Content Server what to expect when the query is finished because the return results differ. A query that performs an SQL Update or Insert will return different results than one that performs a Select.
When creating a query service there are three action types that may be used.
Service Query Action Types
| Action Type | Description |
| Select Query | Executes a select query, but does not return the results. Instead, the query will return whether the execution succeeded or failed. |
| Execute Query | Executes data manipulation queries (insert, update, delete). The execution will not return any results, but will alter the database. |
| Select Cache Query | Executes a select query and puts the results into Content Server’s backend data. The output will be available as a ResultSet. |
As you might guess, selecting the right Action Type is a critical step for the query service to operate as expected.
In the next Blog entry we’ll dive into more detail on how to create a service to execute a query. Since the entire WebCenter Content platform is built on the concept of executing services, our ability to build services which return and update data from the content server files is incredibly powerful.
For more information on customizing WebCenter Content and customized training to suit the needs of your organization, please contact TEAM Informatics and let us help you increase the ROI on your WebCenter Content investment.
