The connector uses verb application-specific information for the Retrieve and RetrieveByContent verbs. This text allows you to specify the attributes to be included in the WHERE clause for a retrieval. You can also specify operators and attribute values.
The syntax for application-specific information for the Retrieve and RetrieveByContent verbs is shown below:
[condition_variable conditional_operator @ [...]:[..]attribute_name [, ...]]
where:
condition_variable | The name of the database column. |
conditonal_operator | The operator supported by the database, for example =, >, OR, AND, and IN (value1, value2). |
@ | A variable that is substituted with the value retrieved by getAttrValue(attribute_name). The substitution is positional; that is, the connector substitutes the first @ with the value of the first attribute_name variable specified after the : delimiter. |
.. | The attribute specified in the attribute_name variable belongs to the immediate parent business object; if this value is missing, the attribute belongs in the current business object. |
attribute_name | The name of the attribute whose value the connector substitutes for @. |
To understand the syntax of this property, assume that an Item business object has an item_id attribute whose value is XY45 and a Color attribute whose value is RED. Assume further that you specify the Retrieve verb's AppSpecificInfo property as:
Color='RED'
The above application-specific information value causes the connector to build the following WHERE clause for a retrieval:
where item_id=XY45 and Color = 'RED'
For a more complicated example, assume that the Customer business object has a customer_id attribute whose value is 1234 and a creation_date attribute whose value is 01/01/90. Assume also that this business object's parent has a quantity attribute whose value is 20.
Assume further that you specify the Retrieve verb's AppSpecificInfo property as:
creation_date > @ OR quantity = @ AND customer_status IN ('GOLD', 'PLATINUM') : creation_date, ..quantity
The above application-specific information value causes the connector to build the following WHERE clause for a retrieval:
where customer_id=1234 and creation_date > '01/01/90' OR quantity = 20 AND customer_status IN ('GOLD', 'PLATINUM')
The connector gets the date value ('01/01/90') from the creation_date attribute in the current business object. It gets the quantity value (20) from the quantity attribute in the parent business object (as indicated by ..quantity in the application-specific information.
After the connector parses the application-specific information for the Retrieve verb, it adds the text to the WHERE clause of the RETRIEVE statement that it constructs from the business object's primary or foreign keys. The connector adds the leading AND to the WHERE clause. The value of the application-specific information must be valid SQL syntax. In the case of RetrieveByContent, the application-specific information is added to the WHERE clause of the RETRIEVE statement that it constructs from the business object's attributes that have their values populated.
The WHERE clause can also refer to placeholder attributes instead of the actual attributes in the parent business object. These placeholders do not have any application-specific information. An attribute can be a placeholder if it satisfies one of the following conditions for its ASI:
For example: An Order business object contains a multiple cardinality line item business object, and retrieval of only specific line items is needed. This retrieval can be handled through a placeholder attribute in the Order business object. This placeholder is required in the parent object because the child objects are all pruned. The placeholder attribute can be populated at runtime by the integration broker with a list of the specific line items, separated by a comma (,).
For this example, you would add the following information to the WHERE clause for the retrieve verb on the child line item business object:
line_item_id in(@,@,@):..placeholder1,..placeholder2,..placeholder3
Where line_item_id in is the ID in the child business object, placeholder is the attribute in the parent. If placeholder contains the values 12,13,14 the query would select the following from the WHERE clause:
line_item_id in(12,13,14)
Where SELECT:..FROM:..WHERE x in (1,2,3) is a standard database SQL syntax.
In the RetrieveByContent verb, if the length of the WHERE clause is 0, the connector will use the application-specific information in the WHERE clause of the RETRIEVE statement. With this feature, the user can send a business object with no attribute values populated and specify verb application-specific information for RetrieveByContent, and the connector will build the WHERE clause based on what was specified in the verb application-specific information alone.