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

  1. 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 WebSphere 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.

  2. 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.

  3. 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.

  4. 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

Data service layer


+

Search Tips   |   Advanced Search