Develop > Business logic layer > Work with the data service layer > Extend the WebSphere Commerce schema using the data service layer
Add 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.
- Create a WC\config\com.ibm.commerce.servicemodule-ext folder, if one does not already exist.
- Right-click the WC\config\com.ibm.commerce.servicemodule-ext folder. You must create the file in the -ext directory. Do not modify WebSphere Commerce query templates directly.
- Click New > File.
- Name the custom query template file, using some unique string (your company name) to differentiate it: wc-query-MyCompanyCatalogEntry-get.tpl.
- Click Finish.
- Decide which of the two types of queries 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 WebSphere 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 WebSphere 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
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.
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. See Access profile naming conventions for more information.
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.
Related concepts