SQL parameters

Within query templates, we can use SQL parameters to substitute values in SQL queries. The extended XPath query expression specifies the values of the SQL parameters in two ways, using XPath attributes or elements, and using control parameters.

Control parameters are specified before the XPath expression, between the curly brackets. For example: {_wcf.ap=IBM_Admin_Summary&_cat.rank=1}. XPath attributes are specified within the expression itself. SQL parameter names should match the names of the XPath nodes (elements or attributes) or the names of the control parameters.

Note: IBM_Admin_ prefixes all services intended to be used by admin/CMC based services calls. Access profiles which do not follow the new naming conventions continue to function correctly, as compatibility is maintained with earlier versions. IBM recommends, however, that they are followed for existing access profiles, and when making changes to future access profiles.

The following sample extended XPath expression contains the _cat.rank control parameter, and relationshipType and catGroupId XPath attributes:

{_wcf.ap=IBM_Admin_Summary&_cat.rank=1} /CatEntry[inRelCatGroups[@relationshipType=1 and @catGroupId= 10001]]

These values can be specified for the following SQL template:

SELECT CATENTRY.$COLS:CATENTRY$, CEPROPERTY.$COLS:CEPROPERTY$ 
FROM CATENTRY 
LEFT OUTER JOIN CEPROPERTY ON CATENTRY.CATENTRY_ID = CEPROPERTY.CATENTRY_ID,CECGREL
WHERE	CATENTRY.CATENTRY_ID = CECGREL.CATENTRY_ID AND 
			CATENTRY.MARKFORDELETE = 0 AND 
			CECGREL.CATGROUP_ID = ?catGroupId? AND 
			CECGREL.USAGECODE = ?relationshipType? AND 
			CATENTRY.RANK = ?_cat.rank.0? 

The WHERE clause contains conditions referencing the input parameters, catGroupId, relationshipType, and _cat.rank. The SQL parameters should be surrounded by the '?' symbols.

SQL template must use the 'in' predicate if an SQL parameter can have multiple values ('OR' semantics). In this case, the multi-valued XPath properties should be specified in the parenthesized 'or' expression. In the example below, the catEntryId XPath attribute is multi-valued:

/CatEntry[@buyable='true' and (@catEntryId='123' or @catEntryId='124')]

The XPath key generated for this multi-valued property is shown below:

/CatEntry[@buyable='true' and (@catEntryId=)]

The SQL template will look as follows:

SELECT CATENTRY.$COLS:CATENTRY$, FROM CATENTRY 
WHERE CATENTRY.CATENTRY_ID IN (?catEntryId?) AND CATENTRY.BUYABLE = '?buyable?'

Here is the query after parameter substitution is performed:

SELECT CATENTRY.CATENTRY_ID, CATENTRY.PARTNUMBER FROM CATENTRY 
WHERE CATENTRY.CATENTRY_ID IN (123, 124) AND CATENTRY.BUYABLE = 'true' 

Parameter indexing

If an XPath expression contains multiple elements or attributes with the same name, the SQL template parameter names must be indexed to resolve the ambiguity. This is done by appending a '.' and an index to the name of the parameter. For example, ?catGroupId.1?, ?catGroupId.2?.

Any SQL parameters with the same name get numbered according to the order in which they appeared in the extended XPath query expression. Index numbering for XPath query nodes start with 1. Control parameters used in the query template must be specified with an index of 0.