Arbitrary Find

The arbitrary find method provides custom device applications the ability to dynamically build queries based on user input.

AttributeTest

In addition to allowing for arbitrary search criteria, the arbitrary find method lets the user specify a desired ordering of the results and object state criteria. A Query class is included in the client object API’s core assembly sup-client.dll Sybase.Persistence namespace. The Query class is the single object passed to the arbitrary search methods and consists of search conditions, object/row state filter conditions, and data ordering information.

In MBO Customer.cs:

public static  Sybase.Collections.GenericList<sample.Customer> FindWithQuery(Sybase.Persistence.Query query)

In Database class SampleAppDB.cs:

public static Sybase.Persistence.QueryResultSet ExecuteQuery(Sybase.Persistence.Query query)

The following classes define arbitrary search methods and filter conditions, and provide methods for combining test criteria and dynamically querying result sets.

Query and Related Classes
Class	Description
Query	Defines arbitrary search methods and can be composed of search conditions, object/row state filter conditions, and data ordering information.
AttributeTest	Defines filter conditions for MBO attributes.
CompositeTest	Contains a method to combine test criteria using the logical operators AND, OR, and NOT to create a compound filter.
QueryResultSet	Provides for querying a result set for the dynamic query API.

In addition queries support select, where, and join statements.

Define these conditions by setting properties in a query:

TestCriteria – criteria used to filter returned data.
SortCriteria – criteria used to order returned data.
Skip – an integer specifying how many rows to skip. Used for paging.
Take – an integer specifying the maximum number of rows to return. Used for paging.

TestCriteria can be an AttributeTest or a CompositeTest.

An AttributeTest defines a filter condition using an MBO attribute, and supports these conditions:

IS_NULL
NOT_NULL
EQUAL
NOT_EQUAL
LIKE
NOT_LIKE
LESS_THAN
LESS_EQUAL
GREATER_THAN
GREATER_EQUAL
CONTAINS
STARTS_WITH
ENDS_WITH
DOES_NOT_START_WITH
DOES_NOT_END_WITH
DOES_NOT_CONTAIN

User can use query to construct a query SQL statement as he wants to query data from local database. This query may across multiple tables (MBOs).

Query query2 = new Query();
query2.Select("c.fname,c.lname,s.order_date,s.region");
query2.From("Customer", "c");
//
// Convenience method for adding a join to the query
// Detailed construction of the join criteria
query2.Join("Sales_order", "s", "c.id", "s.cust_id");
AttributeTest ts = new AttributeTest();
ts.Attribute = ("fname");
ts.TestValue = "Beth";
query2.Where(ts);
QueryResultSet resultSet = SampleAppDB.ExecuteQuery(query2);

On low memory devices, retrieving up to 30,000 records from the database may cause the custom client to fail and throw an OutOfMemoryException.

Consider using the Query object to limit the result set:

Query props = new Query();
props.Skip =10;
props.Take = 5;

CustomerList customers = Customer.FindWithQuery(props);

A CompositeTest combines multiple TestCriteria using the logical operators AND, OR and NOT to create a compound filter.

SortCriteria defines a SortOrder, which contains an attribute name and an order type (ASCENDING or DESCENDING).

For example, to locate all customer objects based on this criteria:

FirstName = John AND LastName = Doe AND (State = CA or State = NY)
Customer is New or Updated
Ordered by LastName ASC, FirstName ASC, Credit DESC
Skip the first 10 and take 5

This code demonstrate the usage of CompositeTest, SortCriteria and Query:

Query props = new Query();
            //define the attribute based conditions
            //Users can pass in a string if they know the attribute name. R1 column name = attribute name.
            CompositeTest innerCompTest = new CompositeTest();
            innerCompTest.Operator = CompositeTest.OR;
            innerCompTest.Add(new AttributeTest("state", "CA", AttributeTest.EQUAL));
            innerCompTest.Add(new AttributeTest("state", "NY", AttributeTest.EQUAL));
            CompositeTest outerCompTest = new CompositeTest();
            outerCompTest.Operator = CompositeTest.OR;
            outerCompTest.Add(new AttributeTest("fname", "Jane", AttributeTest.EQUAL));
            outerCompTest.Add(new AttributeTest("lname", "Doe", AttributeTest.EQUAL));
            outerCompTest.Add(innerCompTest);
            //define the ordering
            SortCriteria sort = new SortCriteria();

            sort.Add("fname", SortOrder.ASCENDING);
            sort.Add("lname", SortOrder.ASCENDING);
            //set the Query object
            props.TestCriteria = outerCompTest;
            props.SortCriteria = sort;
            props.Skip = 10;
            props.Take = 5;
            Sybase.Collections.GenericList<Customer> customers2 = Customer.FindWithQuery(props);