SQL Queries Supported by the OLE DB Provider for Commerce Server
This section describes the subset of ANSI SQL language used to manipulate objects aggregated across multiple data sources.
SQL Query Syntax
The following code example shows the SQL query syntax supported by the OLE DB Provider for Commerce Server Core Systems:
SELECT select_list FROM class_name
[WHERE search_condition]
[ORDER BY order_expression]
select_list ::= member_name | member_name , member_list
search_condition ::= in_exression | predicate |
predicate AND search_condition
in_expression ::= member_name IN ( value1, , valueN )
predicate ::=
member_name { = | <> | > | >= | < | <= | LIKE } value |
member_name IS [NOT] NULL
order_expression::= member_name*
INSERT [INTO] class_name member_list VALUES value_list
member_list ::= member_name | member_name , member_list
value_list ::= value | value , value_list
UPDATE class_name SET member_value_list WHERE search_condition
member_value_list ::= member_value | member_value , member_value_list
member_value ::= member_name = value
DELETE [FROM] class_name WHERE search_condition
Restrictions
The following restrictions apply to the query syntax:
All members in all queries must be specified by their fully qualified names.
JOIN operations are not supported.
Active Directory does not support queries using the less than (<) or greater than (>) operators. For more information, see RFC 2254, "The String Representation of LDAP Search Filters".
Queries cannot be ordered by multiple properties when these properties are stored in Active Directory.
Membership Directory does not support sorting.
Nonprintable characters are not supported.
The results set from a SELECT query is streamed back in the order in which the underlying data store returns the data.
All members in the WHERE or ORDER_BY clauses must belong to the same underlying data store. If both WHERE and ORDER_BY clauses are present, then all members in both clauses must map to the same underlying data store.
Only single-valued Number (Integer, Float), String, Currency or Date/Time/Datetime data types are supported in the WHERE clause.
LIKE clauses support wildcard searches through the '%' character only. All members in LIKE clauses must be of the String data type.
The semicolon (;) is used as the delimiter for values in a multiple-valued property. Thus, values in a multiple-valued property must not contain a semicolon.
INSERT and UPDATE commands allow multiple-valued property values. The following examples illustrate the use of multiple-valued properties:
INSERT INTO UserObject (Name, Interests) VALUES ('Joe', ('Basketball', 'Baseball')) UPDATE UserObject SET (Interests = ('Basketball', Baseball')) WHERE Name = 'Joe'A single quote must be escaped with another single quote.
A double quote does not need to be escaped with another double quote unless the query is in Visual Basic Scripting Edition (VBScript) code.