Introduced in Feature Pack 2

Adding query templates to retrieve new scheduling type data

In this lesson, you update the Subscription service module with the scheduler type and the day-of-month user data. You add query template files that retrieve your custom schedule type information. Query template files store the SQL and access profile definitions for a service module, isolating it from the business logic layer completely.

A query template relates an XPath key and an access profile of a logical object to a template SQL query to select the data. Custom query templates might reuse existing XPath keys but must always define a new access profile because a different view of the data is returned.

Read the following topic to learn more about query template files and how they are used:

The query template file consists of the following parts:
  1. A symbol definition section that defines the tables that are used by the query template (SUBSCRIPTION and XSUBSCRSCHTYPE).
  2. An XPath to the SQL statement that maps the XPath key and access profile to a specific template SQL query.
  3. A new access profile, MyCompany_IdResolve that is used along with the XPath key to identify the SQL template query.

The default queries to fetch the data before you update the Subscription noun and ScheduleInfo noun part is updated to include the XSUBSCRSCHTYPE table.

  • The default SQL queries for updating the Subscription noun and its part are located inside the following file: WC_eardir\xml\config\com.ibm.commerce.subscription\wc-query-Subscription-update.tpl
  • The default SQL query that is used to update the Subscription noun is identified by the IBM_IdResolve access profile.

To configure the Subscription service module to update the new user data, a new query is added in the extension update query template file. This new query is used to select the new table by using a new access profile.

Procedure

  1. Create a custom Get query file:
    1. Right-click the com.ibm.commerce.subscription-ext folder under the WC_eardir\xml\config path.
    2. Click New > File.
    3. Name the file: wc-query-MyCompanyCatalogEntry-get.tpl.
    4. Click Finish.
    5. Copy and paste the following query template into the file.
      BEGIN_SYMBOL_DEFINITIONS	
		COLS:XSUBSCRSCHTYPE = XSUBSCRSCHTYPE:*
		COLS:SUBSCRIPTION_ID = SUBSCRIPTION:SUBSCRIPTION_ID	
END_SYMBOL_DEFINITIONS

<!-- ===========================================================================
     Get XSubScrSchType query for MyCompany_Store_Details access profile.
     =========================================================================== -->
     
BEGIN_ASSOCIATION_SQL_STATEMENT
	name=MYCOMPANY_SUBSCR_DETAILS
	base_table=SUBSCRIPTION
	sql=
	SELECT SUBSCRIPTION.$COLS:SUBSCRIPTION_ID$, XSUBSCRSCHTYPE.$COLS:XSUBSCRSCHTYPE$
	FROM SUBSCRIPTION, XSUBSCRSCHTYPE
	WHERE SUBSCRIPTION.SUBSCRIPTION_ID=XSUBSCRSCHTYPE.SUBSCRIPTION_ID AND XSUBSCRSCHTYPE.SUBSCRIPTION_ID IN ( $ENTITY_PKS$ )
	END_ASSOCIATION_SQL_STATEMENT

<!-- ===========================================================================
     Definition for MyCompany_Store_Details access profile.
     =========================================================================== -->
BEGIN_PROFILE 
	name=MyCompany_Store_Details
	extends=IBM_Store_Details
	BEGIN_ENTITY 
	associated_sql_statement=MYCOMPANY_SUBSCR_DETAILS
    END_ENTITY
END_PROFILE
    6. Save the file.
  2. 2. Create a custom Update query file:
    1. Right-click the com.ibm.commerce.subscription-ext folder under the WC_eardir\xml\config path.
    2. Click New > File.
    3. Name the file: wc-query-MyCompanyCatalogEntry-update.tpl.
    4. Click Finish.
    5. Copy and paste the following query template into the file. 
      BEGIN_SYMBOL_DEFINITIONS
	
	<!-- The table for noun Subscription  -->
		<!-- Defining all columns of the table -->
		COLS:SUBSCRIPTION = SUBSCRIPTION:* 	
		<!-- Defining all columns of the XSUBSCRSCHTYPE table -->
		COLS:XSUBSCRSCHTYPE = XSUBSCRSCHTYPE:* 	
		<!-- Defining the unique ID column of the table -->
		COLS:SUBSCRIPTION_ID = SUBSCRIPTION:SUBSCRIPTION_ID, OPTCOUNTER
END_SYMBOL_DEFINITIONS

<!-- ================================================================================== -->
<!-- XPath: /Subscription[SubscriptionIdentifier[UniqueID=]]-->
<!-- AccessProfile:	IBM_IdResolve -->
<!-- Gets the subscription entity using the UniqueID provided as parameter -->
<!-- Access profile includes all columns from the table. -->
<!-- @param UniqueID  Unique id of Subscription to retrieve -->   
<!-- ================================================================================== -->
BEGIN_XPATH_TO_SQL_STATEMENT
	name=/Subscription[SubscriptionIdentifier[UniqueID=]]+IBM_IdResolve
	base_table=SUBSCRIPTION
	sql=	
	SELECT SUBSCRIPTION.$COLS:SUBSCRIPTION$, XSUBSCRSCHTYPE.$COLS:XSUBSCRSCHTYPE$
	FROM SUBSCRIPTION LEFT OUTER JOIN XSUBSCRSCHTYPE ON SUBSCRIPTION.SUBSCRIPTION_ID=XSUBSCRSCHTYPE.SUBSCRIPTION_ID
	WHERE SUBSCRIPTION.SUBSCRIPTION_ID = ?UniqueID?
END_XPATH_TO_SQL_STATEMENT
    6. Save the file.