SQL Queries

  • Article

This section describes the subset of ANSI SQL language used to manipulate objects aggregated across multiple data sources.

SQL Query Syntax

Restrictions

SQL Query Syntax

The following code example shows the SQL query syntax supported by OLE DB Provider for Commerce Server:

SELECT select_list FROM class_name
   [WHERE search_condition] 
   [ORDER BY order_expression]

select_list ::= * | member_name*
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.

  • 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.

  • Non-printable 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 wild-card searches through the '%' character only. All members in LIKE clauses must be of the String data type.

  • The semi-colon (;) is used as the delimiter for values in a multi-valued property. Thus, values in a multi-valued property must not contain a semi-colon.

  • INSERT and UPDATE commands allow multi-valued property values. The following examples illustrate the use of multi-valued properties:

    INSERT INTO UserObject (Name, Interests)
VALUES ('Joe', ('Basketball', 'Baseball'))

UPDATE UserObject
SET (Interests = ('Basketball', Baseball')) 
WHERE Name = 'Joe'


All rights reserved.