QueryProdInfoADO

The QueryProdInfoADO component, which usually appears in the Product Info stage of an order processing pipeline, runs a database query to retrieve data about the products on the order form. If data for the specified SKU is found, then for each column retrieved by the query, the component then writes the data into the item._product_columnvalue fields on the item Dictionary of the order form, so that the product information is available to components that run subsequently.

υConnection string. Specifies the connection string used for connecting to the database. The connection string must include either the full connection string (the data source name (DSN), database login ID, and database password) or the name of a named connection stored in the connection map. If this field is blank, the default connection string is used.
υQuery. Specifies either the full SQL text of the query that is executed by this component or the name of a query whose full SQL text is defined in the site’s global.asa file.
υParameter List. Specifies the parameters that the component passes to the query. If multiple parameters are specified, each parameter is separated from the next by a single space.

Remarks

If you add new columns to the product database table, and if this information is needed by any component in the pipeline, then you must add the column to the query used by QueryProdInfoADO.

If data for the specified SKU is found, then for each column retrieved by the query, QueryProdInfoADO writes a name/value pair to the item Dictionary. The name part of the name/value pair consists of the prefix _product_, followed by the column name. Thus, for example, the data in a table column named col would be placed in the value of item[n]._product_col. Finally, QueryProdInfoADO initializes the name/value pair to contain the data stored in the appropriate column.

If no rows are returned by the query, QueryProdInfoADO writes a name/value pair named delete to the item Dictionary, initializes the delete item to 1 (marking it for deletion), adds a pur_badsku message to the _Basket_Errors list, then moves on to the next item. Items marked for deletion are removed from the OrderForm.

The primary difference between QueryProdInfoADO and the Commerce Server 2.0 QueryProdInfo component lies in the types of queries you can use.

υThe QueryProdInfo component attempts to retrieve the query that you specify from the Content object. QueryProdInfoADO attempts to retrieve the query from the QueryMap Dictionary.
υThe QueryProdInfo component uses the default datasource for the site to execute the query. QueryProdInfoADO uses any connection string that you specify.
υThe QueryProdInfo component always uses the SKU for a given item as a parameter to the query that retrieves information about that item. QueryProdInfoADO can use any parameters that you specify to retrieve information about an item. Consequently, QueryProdInfoADO can function with a wider variety of database schema than can QueryProdInfo.

Example

The QueryProdInfoADO component is included in the Plan pipeline (Plan.pcf) used by the Clocktower sample site. When you open the Clocktower’s Plan.pcf file in the Win32®-based Pipeline Editor, and double-click the QueryProdInfoADO component, the property page appears.

The Query field is specified as productpl, which identifies an entry in the Clocktower’s QueryMap Dictionary. This entry is created in the Clocktower’s Global.asa file as follows:

REM -- Create Query Map Dictionary
REM -- Each Query is a Dictionary with SQLCommand and parameters
Set MSCSQueryMap = Server.CreateObject("Commerce.Dictionary")

REM Later in global.asa

REM -- Query used by the QPI component
Set MSCSQueryMap.productpl = Server.CreateObject("Commerce.Dictionary")
    MSCSQueryMap.productpl.SQLCommand = "select sku, name, list_price     from clocktower_product where sku = ?"

When the order processing pipeline (OPP) is invoked, the QueryMap is passed in as part of the pipe context Dictionary to the MtsPipeline object’s Execute method.

The QueryProdInfoADO component retrieves the text of the productpl query from the QueryMap. Because the component has no configured connection string, the QueryProdInfoADO component uses the default connection string.

If the productpl query returns data for an item, QueryProdInfoADO adds the following name/value pairs to the Dictionary for that item.

_product_sku
_product_name
_product_list_price