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

Browse to the WC/xml/config directory.
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.
Right-click the directory that you created, then click New > File. The New File dialog opens.
In the Name field, enter wc-query-utilties.tpl and click Finish.

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.