HCL Commerce Developer

Creating a query template file to hold SQL queries

A query template file is used by your extended SEOURLMapper class to hold SQL queries that are required to get information from the database.

Procedure

  1. Browse to the WC/xml/config directory.
  2. If it does not exist, create a directory named com.ibm.commerce.infrastructure-ext:
    1. Right-click config.
    2. Click New > Folder.
    3. In the New Folder dialog, enter com.ibm.commerce.infrastructure-ext.
    4. Click Finish.
  3. Right-click the directory that you created, then click New > File. The New File dialog opens.
  4. In the Name field, enter wc-query-utilties.tpl and click Finish.
  5. Enter the code snippet that is shown below into the file, then click File > Save. 
    <!-- =======================================================-->
<!-- This SQL will return the active SEO URL keyword record -->
<!-- of a catalog entry or catalog group in a specified     -->
<!-- language and store.                                    -->
<!-- @param objectId The ID of the catalog entry or catalog -->
<!-- group.                                                 -->
<!-- @param langId       The language ID of the keyword.    -->
<!-- @param storeId	    The store ID of the keyword.       -->
<!-- ====================================================== -->
BEGIN_SQL_STATEMENT
  base_table=SEOURLKEYWORD
  name=MyCompany_Select_ActiveKeyword
  sql=
      SELECT *
      FROM SEOURLKEYWORD, SEOURL
      WHERE SEOURL.TOKENVALUE = ?objectId?
      AND LANGUAGE_ID = ?langId?
      AND (
        STOREENT_ID= ?storeId?
        OR
        STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
      WHERE STORE_ID = ?storeId? AND STRELTYP_ID = -4)
           )	
      AND SEOURLKEYWORD.SEOURL_ID = SEOURL.SEOURL_ID
      AND SEOURL.TOKENNAME = ?tokenName?
      AND STATUS = 1
END_SQL_STATEMENT

Results

You created a query file to hold all the SQL statements that the extended SEOURLMapper requires to get information from the database.