Query template file

Understanding > WebSphere Commerce and service-oriented architecture (SOA) > Data service layer > Data service layer query processing
Commerce Feature Pack

Query template file

The query template file is a mechanism by which you can easily map a query on your logical model to one or more SQL statements. The SQL assets are kept in separate files, isolated from the runtime Java code. This helps database administrators and programmers to locate and analyze SQL statements. Additionally, changes to SQL used for queries does not require recompiling Java code. Also, the addition of new columns into existing tables may not require changing SQL statements that use those tables. Column information is separated out in the SYMBOL_DEFINITIONS section.

Query template file location and naming

A service module can have one or more query template files. The query template files must be prefixed with 'wc-query' and have the extension '.tpl'.
Default query template files are located at:

WCDE_installdir\xml\config\com.ibm.commerce.servicemodule

WC_eardir\xml\config\com.ibm.commerce.servicemodule
You can add new queries or extend provided queries, by placing your query template files under the extended configuration directory for your service module.
Custom query template files must be located at:

WCDE_installdirxml\config\com.ibm.commerce.servicemodule-ext

WC_eardir\xml\config\com.ibm.commerce.servicemodule-ext
For example, for catalog, this would be xml\config\com.ibm.commerce.catalog-ext.

Adding new columns to your custom tables requires changing the physical SDOs that represent those tables, using the Data Service Layer wizard.

Query template file syntax and loading

Query template files are loaded in ascending alphanumeric order. Queries defined in files loaded later override queries loaded earlier.
The blocks in the query template must be in this order:

BEGIN_SYMBOL_DEFINITIONS
END_SYMBOL_DEFINITIONS
BEGIN_XPATH_TO_SQL_STATEMENT
END_XPATH_TO_SQL_STATEMENT
BEGIN_ ASSOCIATION_SQL_STATEMENT
END_ ASSOCIATION_SQL_STATEMENT
BEGIN_SQL_STATEMENT
END_SQL_STATEMENT
BEGIN_PROFILE
END_PROFILE.

Every comment line or comment block must start with . A comment line or block can be at any place in the file.
During development, you may want to modify and reload a query template file, without having to restart the server. This is supported through the use of a .reloadconfig file. See Reloading the configuration of a BOD service module for more information.

Query template file organization

A query template file has five main sections, of which the first two are mandatory:
SYMBOL_DEFINITIONS
The column symbol definition section defines column symbols that are used and referenced in the SELECT list of your SQL template statements. If your physical schema changes, you can adjust the symbols without rewriting SQL. Symbol definition is similar to defining constants in programming languages. You can define information aboutce and use it in multiple places. This helps localize where to update if future changes are required. When you define a subset of the columns to select from a table, include the primary key columns and the foreign key columns referencing other tables in the query.

There must be one and only one BEGIN_SYMBOL_DEFINITIONS, END_SYMBOL_DEFINITIONS block per query template file.
All the symbols have to be defined in the BEGIN_SYMBOL_DEFINITIONS block.
There can be comment line or symbol definition line. Symbol definition has to be completed in one line.
A symbol definition line defines one and only one symbol definition.
The symbol name, between "COLS:" and '=', is the unique identifier of the symbols. The legal characters of the symbol names are a-z, A-Z and 0-9.
If the wildcard (*) is used in the column definition, the column names are retrieved from the Object-relational metadata.

Column definitions have a file scope.
Example
BEGIN_SYMBOL_DEFINITIONS

  COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID
  COLS:CATENTRY=CATENTRY:*  
  COLS:CATENTDESC=CATENTDESC:CATENTRY_ID,SHORTDESCRIPTION, OPTCOUNTER

END_SYMBOL_DEFINITIONS
When you define a subset of the columns to select from a table, then you should include the OPTCOUNTER column. See Optimistic locking for additional details. You should always add an OPTCOUNTER column to your custom tables. For example:
COLS:DMACTIVITY_NAME=DMACTIVITY:DMACTIVITY_ID, NAME, OPTCOUNTER 
XPATH_TO_SQL_STATEMENT
The XPATH_TO_SQL_STATEMENT links the logical and physical layers by mapping an XPath key directly to an SQL statement. The name of the XPATH_TO_SQL_STATEMENT is the key of the XPath expression, or in the case of single-step queries, a combination of the XPath key and the access profile. For example, if the XPath key is /CatalogEntry[CatalogEntryIdentifier[ExternalIdentifier[(PartNumber=)]]] and the access profile is IBM_Admin_Details, the name of a single-step template is /CatalogEntry[CatalogEntryIdentifier[ExternalIdentifier[(PartNumber=)]]]+IBM_Admin_Details. Use the wcs_xpathkey utility to get the XPath key for an XPath expression.

IBM_Admin_ prefixes all services intended to be used by admin/CMC based services calls in Feature Pack 4. Access profiles which do not follow the new naming conventions continue to function correctly, as compatibility is maintained with earlier versions. IBM recommends, however, that they are followed for existing access profiles, and when making changes to future access profiles.�See Access profile naming conventions for more information.

The mapping from XPath key to SQL query can be overridden. If an XPath key is defined in more than one query template file, the one defined in the file last loaded will override the others.

There can be more than one BEGIN_XPATH_TO_SQL_STATEMENT, END_XPATH_TO_SQL_STATEMENT block.
Each block defines one and only one SQL statement.
Each block has to have name and base_table defined.
Spaces are not allowed in the name.
The SQL statement may expand to multiple lines, and it has to be defined last in the block.
The optional dbtype tag is specified when an SQL statement is specific to the database platform. This tag is useful when developing SQL statements for multiple database platforms. These statements can be included in a single template file. The valid values for the dbtype tag include 'db2', 'oracle', 'derby', and 'any'. Queries that do not have this tag specified will apply for all database platforms. If you include a query for a specific database platform, IBM recommends to also include a default one (dbtype value 'any') to be used on other platforms.
The elements in the block have to be in the same order as in the example below.
Example
BEGIN_XPATH_TO_SQL_STATEMENT
name=/CatalogEntry[CatalogEntryIdentifier[ExternalIdentifier[(PartNumber=)]]]
base_table=CATENTRY
sql=
SELECT 
  CATENTRY.$COLS:CATENTRY_ID$
FROM
  CATENTRY
JOIN
  STORECENT ON (CATENTRY.CATENTRY_ID = STORECENT.CATENTRY_ID AND
  STORECENT.STOREENT_ID = $CTX:STORE_ID$)
WHERE
  CATENTRY.PARTNUMBER IN (?PartNumber?) 
  AND CATENTRY.MARKFORDELETE = 0

END_XPATH_TO_SQL_STATEMENT 
ASSOCIATION_SQL_STATEMENT
Associated SQL statements define a specific SQL query. These queries can then be reused to build different access profiles defined in the PROFILE section.

There can be more than one BEGIN_ ASSOCIATION_SQL_STATEMENT, END_ ASSOCIATION_SQL_STATEMENT block.
Each block may define at most one SQL statement.
The rules in the XPATH_TO_SQL_STATEMENT block are applied in this block.
The name is the unique identifier of the ASSOCIATION_SQL_STATEMENT.
Example
BEGIN_ASSOCIATION_SQL_STATEMENT
  name=IBM_CatalogEntryWithDescription
  base_table=CATENTRY
  sql=
       SELECT 
          CATENTRY.$COLS:CATENTRY$,
                CATENTDESC.$COLS:CATENTDESC$
       FROM
          CATENTRY
             LEFT OUTER JOIN CATENTDESC ON 
                     (CATENTDESC.CATENTRY_ID = CATENTRY.CATENTRY_ID  AND 
                      CATENTDESC.LANGUAGE_ID IN ($CONTROL:LANGUAGES$))
       WHERE
                CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$)
END_ASSOCIATION_SQL_STATEMENT
SQL_STATEMENT
This section contains named SQL statements. The SQL statements are executed directly via the JDBC interface using the JDBCQueryService class. This class is similar to the session bean JDBC helper used by SOI service modules. This section may contain select statements that use aggregate functions, like sum() or avg(), as the result of these queries does not map to physical Java objects by object-relational metadata.
Under certain circumstances, SQL statements may need to be run to update data, delete data, or retrieve data independent of the data model. For example, a business operation may insert or delete records in data tables that are not defined in the logical model, or may update multiple data objects that are more efficient to issue a direct SQL rather than using the data service layer to retrieve and update each object.
Example
BEGIN_SQL_STATEMENT
  name=IBM_Update_DeleteCatalogEntry
  base_table=CATENTRY
  sql= UPDATE CATENTRY     
        SET CATENTRY.PARTNUMBER=
               CASE WHEN 
                 LENGTH(CATENTRY.PARTNUMBER||'-'||$DB:CURRENT_TIMESTAMP$)<=64 
               THEN 
                 CATENTRY.PARTNUMBER||'-'||$DB:CURRENT_TIMESTAMP$  
               ELSE 
                 SUBSTR(CATENTRY.PARTNUMBER,1,64-LENGTH(''||$DB:CURRENT_TIMESTAMP$)-1)||'-'||$DB:CURRENT_TIMESTAMP$ END
               ,CATENTRY.MARKFORDELETE=1
         WHERE
      CATENTRY.CATENTRY_ID=?catalogEntryId? 
      OR CATENTRY.CATENTRY_ID IN (SELECT CATENTREL.CATENTRY_ID_CHILD FROM CATENTREL WHERE CATENTREL.CATENTRY_ID_PARENT=?catalogEntryId?)
END_SQL_STATEMENT  
The SQL_STATEMENT section should only appear in the wc-query-utilities.tpl file. This is a special convention for a query template file that only contains these special named SQL statements.

You should never read or update the same data using the JDBCQueryService and the PhysicalDataContainer within the same transaction. If you do, there is a chance you will read stale data or end up with corrupted data in the database.
PROFILE
This section defines access profiles that make use of associated SQL statements. If needed, more than one associated SQL statement can be used by a profile. In that case, each associated SQL statement is performed in turn and the results of the different associated SQL statements are merged together using a GraphComposer class.
Queries that are associated with an access profile must always be defined in the same file where the access profile is defined, except for the case when you extend an access profile. The extension mechanism allows you to reuse the default associated SQL statements without having to redefine them in your custom query template file.

There can be more than one BEGIN_PROFILE, END_PROFILE block.
All the profile blocks have to be at the end of the file.
Each block has one profile name defined, and the profile name is the unique identifier of the profile.
Each profile block may have only one BEGIN_ENTITY, END-ENTITY block, and in each entity block

base_table must be defined.
There can be one or more associated_sql_statement defined. This associated_sql_statement has to match the one of the ASSOCIATION_SQL_STATEMENT names defined. In addition, the base_table name defined in the entity block has to match the base_table name in the corresponding queries in the ASSOCIATION_SQL_STATEMENT block and XPATH_TO_SQL_STATEMENT block used in the access profile.
An optional Graph composer can be specified in the entity block. If the className is specified, it has to be the full path of the class, and the class must subclass com.ibm.is.component.dsl.GraphComposer.

Example
BEGIN_PROFILE 
     name=IBM_Admin_Summary
     BEGIN_ENTITY 
        base_table=CATENTRY 
          associated_sql_statement=IBM_CatalogEntryWithDescription
     END_ENTITY
END_PROFILE
Graph composer
In the query template file, an optional graph composer Java class can be specified in the ENTITY section within the PROFILE block. This Java class allows you to augment the final graph of physical SDOs. It can perform data aggregation from other sources and can merge multiple graphs of physical SDOs into a single graph if there is more than one associated SQL statement defined in the profile for the base table object.
Related concepts

Data service layer
Access profiles
Related tasks

Create a query
Create an access profile for an existing XPath expression
Extending a default WebSphere Commerce query
Generating your XPath key using the wcs_xpathkey utility
Related reference

SQL parameters
Query template file tags
Data service layer naming conventions
WebSphere Commerce extended XPath notation
Last updated: 25 November 2009