Object Query Definition Guidelines

Support for various compact databases

Since object queries can run on multiple mobile devices that may run different compact databases (UltraLiteJ or SQLite for example), object queries support a subset of UltraLiteJ SQL statements.

Object query restrictions
SQL	Restrictions
Select statement	Supported – Order by Unsupported: Bulk and Math functions Group by For Option Row limitation As
Input-parameter	Supported – :name
Comparison operators	Supported: Date format is `YYYY-MM-DD` and contained in single quotes. Literal strings must be contained in single quotes.
From clause	Supports multiple parts but from only one MBO.

See the UltraLiteJ documentation for more information about theUltraLite SELECT statement clauses.

Object queries must use aliases

Define the object query using an alias that references the MBO and attribute names (not table and column names from which they are derived). For example, if you have an MBO named Cust with a cust_id attribute (which is a primary key), defining this object query:

SELECT c.* from Cust c WHERE c.cust_id = :cust_id

and this parameter:

Name – cust_id
Datatype – INT

results in an object query that returns a single row from the Customer table. You must use an alias (c and c.attribute_name) in the query definition or an error occurs during code generation.

General behavior

General object query behavior, including assigning parameters a default value/primary key includes:

For automatically generated object queries derived from "Primary key" settings, Unwired WorkSpace returns the attributes marked as primary key parameters.
For manually created object queries, the parameter field allows you to select any of the available attributes, and, when selected, matches the datatype accordingly. You can still manually type in the attribute name.

Additional implied attributes

When manually defining an object query, you can include additional implied attribute columns that allow you to filter a selection based on the implied attributes. Implied attributes and corresponding datatypes, include:

surrogateKey (long)
foreign keys (long, if the object has one or more parents). Use the actual name of the foreign key.
pending (boolean)
pendingChange (char) – if pending is true, then 'C' (create), 'U' (update), 'D' (delete), 'P' (indicates this row is a parent (source) in a composite relationship for one or more pending child (target) objects, but this row itself has no pending create, update or delete operations). If pending is false, then 'N'.
disableSubmit(boolean)
replayCounter

As an example, specifying the foreign key enables selection of children objects of the specified object. The pending flag locates only those objects that have pending changes, and so on.

This example finds the employee’s first name, last name, and pending state, identified by the employee's social security number (ssn):

select x.emp_fname, x.emp_lname, x.pending from Employee x where x.ss_number = :ssn

Validation rules

Follow these rules when defining a object query:

"findBy" is a reserved word for an object query. If you create an operation starting with "findBy", you receive the warning message: Name of operation ''{0}'' start with 'findBy'. This could cause a name conflict when generating client code.
Do not duplicate query or operation names.
Do not use these reserved names as the query name: "pull", "downloadData."
Do not use an operation name, query name, or attribute name that is the same as MBO name to which they belong.
While clicking Generate generates a valid query, Unwired WorkSpace does not parse or validate the generated query. If you modify the query, be mindful that parameters are not validated until code is generated. For example, this error (mixed case between parameter name and query definition) is not detected until code generation or deployment:
Parameter: Param1 (INT)

Query definition: SELECT x.* FROM TravelRequest x WHERE x.trvl_Id = :param1