Adding query templates to include custom information

This file describes how to add query templates to include information you have added to the schema.

Before you begin

Custom information has already been added to the schema.

Procedure

Create a custom Get query template file.
1. Create a WC\config\com.ibm.commerce.servicemodule-ext folder, if one does not already exist.
2. Right-click the WC\config\com.ibm.commerce.servicemodule-ext folder. You must create the file in the -ext directory. Do not modify HCL Commerce query templates directly.
3. Click New > File.
4. Name the custom query template file, using some unique string (your company name) to differentiate it: wc-query-MyCompanyCatalogEntry-get.tpl.
5. Click Finish.

Decide which of the two types of queries you need to create:

Single-step query

A single-step query, or XPath query, uses the XPATH_TO_SQL_STATEMENT block. It defines the XPath key and Access Profile together. You should use single-step queries whenever possible. However, in some cases it is not possible to fetch all the data in a single query or such a query needs to join a very large number of tables and may not perform. In this case a two-step query should be used. Changes to the query template are required to support single-step query paging. See Paging support in the data service layer for more information.

For single step queries, consider the following example of a default HCL Commerce sample query template file:

BEGIN_SYMBOL_DEFINITIONS
	COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID
	COLS:CATENTRY=CATENTRY:*	
	COLS:CATENTDESC=CATENTDESC:CATENTRY_ID,SHORTDESCRIPTION
END_SYMBOL_DEFINITIONS
			
BEGIN_XPATH_TO_SQL_STATEMENT
	name=/CatalogEntry[(PartNumber=)]+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$)),
                STORECENT                   
	    WHERE
	       CATENTRY.CATENTRY_ID  = STORECENT.CATENTRY_ID AND
               STORECENT.STOREENT_ID = $CTX:STORE_ID$ AND
               CATENTRY.PARTNUMBER IN (?PartNumber?) 
END_XPATH_TO_SQL_STATEMENT

Two-step query

A two-step, or associated query, uses the ASSOCIATION_SQL_STATEMENT block. It defines the XPath key and Access Profile separately. Another reason to use a two-step query is when paging is requested by the client. Paging on the result of a single-step query is not possible if it returns multiple records for each base table record. A two-step query allows you to page on the result set returned by the first statement (the primary keys) rather than on the result set of the second statement. Changes to the TPL file are not required to support two-step query paging.

The order of the nouns returned by the service is consistent with the ordering of the primary keys returned by the XPath to SQL query.

For two step queries, consider the following example of a default HCL Commerce sample query template file:

BEGIN_XPATH_TO_SQL_STATEMENT
	name=/CatalogEntry[(PartNumber=)]
	base_table=CATENTRY
	sql=
	     SELECT  CATENTRY.$COLS:CATENTRY_ID$
	     FROM  CATENTRY, STORECENT                    
	    WHERE
	       CATENTRY.CATENTRY_ID  = STORECENT.CATENTRY_ID AND
                STORECENT.STOREENT_ID = $CTX:STORE_ID$ AND
                CATENTRY.PARTNUMBER IN (?PartNumber?) 
END_XPATH_TO_SQL_STATEMENT

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

BEGIN_PROFILE 
     name=IBM_Admin_Summary
     BEGIN_ENTITY 
				base_table=CATENTRY 
        associated_sql_statement=IBM_CatalogEntryWithDescription
     END_ENTITY
END_PROFILE

Note: If you define the same query, that is, the XPath and Access Profile, as both a single-step and two-step query, the Data Service Layer chooses the one step query by default.

Create an XPATH_TO_SQL_STATEMENT template query. For a single-step query, retrieve all the needed information. For a two-step query, return the primary key values. Use the information in Query template file as a reference.
Optional: Create a new access profile to return different data, as described in Query template file.

Note: Consider the following information when deciding on a new name for an access profile: Names beginning with IBM_ are reserved for IBM use. Use a name which conveys the scope of the data being returned. For example, MyCompany_Details.IBM_Admin_ prefixes all services intended to be used by admin/CMC based services calls. Access profiles which do not follow these naming conventions continue to function correctly, as compatibility is maintained with earlier versions. It is recommended, however, that they are followed for existing access profiles, and when making changes to future access profiles.

Example

Here's an example of a custom query template file for a single-step query. It includes customized schema information (the WARRANTY table)


BEGIN_SYMBOL_DEFINITIONS
 <!-- CATENTRY table -->
 COLS:CATENTRY=CATENTRY:*
 COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID

 COLS:WARRANTY=WARRANTY:*
 COLS:WARDESC=WARDESC:*

  END_SYMBOL_DEFINITIONS

  BEGIN_XPATH_TO_SQL_STATEMENT
 name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]+MyCompany_CatalogEntryWarrantyProfile
 entity_table=CATENTRY
 sql=
 SELECT 
  CATENTRY.$COLS:CATENTRY$,
  WARRANTY.$COLS:WARRANTY$,
  WARDESC.$COLS:WARDESC$
 FROM
  CATENTRY 
   JOIN WARRANTY ON (CATENTRY.CATENTRY_ID = WARRANTY.CATENTRY_ID) 
   JOIN WARDESC ON (CATENTRY.CATENTRY_ID = WARDESC.CATENTRY_ID AND 
  WARDESC.LANGUAGE_ID = $CTX:LANGUAGE_ID$)
 WHERE
  CATENTRY.CATENTRY_ID IN (?UniqueID?) AND
  CATENTRY.MARKFORDELETE = 0
END_XPATH_TO_SQL_STATEMENT