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