Using Oracle ATG Web Commerce with an MSSQL Database

If your Oracle ATG Web Commerce product catalog is stored in a Microsoft SQL Server database, you must configure the database and catalog to properly handle full text searching. There are four steps involved in the configuration process:

  1. Set up the proper full text indexes on the appropriate columns in the database.

  2. Modify the template definition file.

  3. Set the simulateTextSearchQueries property of each product catalog repository component to false.

  4. Configure each search form handler component.

See the subsections that follow for information on completing each step.

Setting Up the MS SQL Full Text Indexes

To enable full text searching on columns, you must create full text indexes for the columns. See your Microsoft SQL Server documentation for specific information on performing this step.

If you are using the default Oracle ATG Web Commerce product catalog, index these tables:

DCS_PRODUCT

DCS_CATEGORY

If you have imported the Motorprise product catalog into MS SQL, index these tables:

DCS_PRODUCT

DCS_CATEGORY

DBC_CATEGORY_DE

DBC_PRODUCT_DE

For each of these tables, create an index for the LONG_DESCRIPTION, DESCRIPTION and DISPLAY_NAME columns.

Modifying the Template Definition File

If you include any full text search queries in the XML template definition file (using the <query-items> tag), verify that the queries use the appropriate format for MS SQL Full Text Query. For example:

<query-items item-descriptor="product">
   description MATCHES "Ethernet" USING "MSSQL_TSQL"
</query-items>

For more information on template definition files, see the ATG Repository Guide.

Configuring the Repository Components for Full Text Searching

To enable a SQL repository to work with a full text search engine, the simulateTextSearchQueries property of the SQL repository component must be set to false.

If you are using the default product catalog, the SQL repository component for the catalog has the Nucleus address /atg/commerce/catalog/ProductCatalog.

Configuring the Search Form Handlers

If your sites use components of class atg.commerce.catalog.custom.CatalogSearchFormHandler to build search forms for full-text searching, you must configure these components to generate full-text queries in the appropriate form. To do this, each component must be configured as follows:

  1. Set the searchStringFormat property to MSSQL_TSQL.

  2. Set the allowEmptySearch property to false.

By default, Oracle ATG Web Commerce includes five instances of this class in /atg/commerce/catalog/:

If you’ve created your own instances of this class, be sure to also set the properties of those components as described above.

Also note that the Motorprise Store Solution Set uses its own instance of atg.commerce.catalog.custom.CatalogSearchFormHandler, which is located in Nucleus at /atg/projects/b2bstore/catalog/SearchCompare. If you have imported the Motorprise product catalog into MS SQL, configure this component as described above as well.