Temporary Query

Syntax

temporary query businessobject TYPE NAME REV	
[!expandtype]	
[vault VAULTNAME]	
[owner USERNAME]	
[limit VALUE]	
[querytrigger]
[includeAdaplet]	
[orderby FIELD_NAME]	
[where QUERY_EXPR]	
[select]
[size N]
[dump "SEPARATOR_STR"]	
[recordseparator "SEPARATOR_STR"]	
[tcl]	
[output FILENAME]; 

For example, to find all business objects in a small database use the following:

temporary query bus * * *;

• The owner, vault, and where clauses can be used with or without businessobject. For example, you could find all business objects owned by user cslewis, or all business objects of type Part owned by user cslewis:

  temporary query owner cslewis; temporary query bus Part * * owner cslewis;

• Use the Limit clause to control the number of items returned in the search. The system will stop searching after it has reached the specified number of items.
• Use the tcl clause after the Dump clause, if used, and before the output clause to return the results in Tcl list format. This facilitates the parsing of output from MQL select commands within Tcl code since the built-in list handling features of Tcl are used directly on the results. For more information, see Tcl Clause.
• Using a size of 0 is the same as not using the Size clause; the query is streamed using the value of MX_QUERY_PAGE_SIZE, if set. For information about query page size, see Installation Guide: Optional Variables.
• Include the querytrigger clause when you want a program named ValidateQuery to be executed. Even with triggers turned off, when querytrigger is included in a query command, the ValidateQuery program gets run.

For example, to find all Assemblies and Assembly subtype objects, use:

Orderby Clause

Use the orderby clause to specify a selectable for sorting. You can prefix the field name with a “+” to sort in ascending order or “-” to sort in descending order. If no prefix is specified, the results are sorted in ascending order.

Sorting is generally done inside the database, but some sorting uses memory. Thus, when sorting query results, it is necessary to have a larger amount of memory swap space since a large number of business objects and relationships might be brought into memory at the same time.

The results of saved queries cannot be sorted.

When using the orderby clause, it must precede any Select clause criteria in the query. You can use a maximum of three orderby clauses in a temporary query or in a query connection command to specify more than 1 sort criteria.

If using the orderby clause it must precede any Select clause criteria in the query.

For example:

temp query bus Part Meta* * orderby -type orderby name 
orderby +Color select attribute[Color]; 

String attributes that might contain values of more than 255 characters must be defined as multiline attributes, or sorting on such an attribute will fail.

Size Clause

Use the Size clause in a temp query or expand bus command to enable streaming in the query or expand and return the specified number of objects per page. When you enable streaming, data is returned when the first page is available, resulting in improvement in performance and memory consumption.

Only results from local vaults are streamed.

When streaming is used, no data is held in cache. Therefore repeating the same operation in a transaction can result in the system issuing the same sql database requests each time.

For example, to find all business objects in a small database using the streaming capability, use the following:

temporary query bus * * * size 100;

This will return all business objects in the database with one hundred entries listed in a page.

Streaming can be enabled for all temporary queries (and expands) through the MX_QUERY_PAGE_SIZE environment variable, through classes in the Studio Customization Toolkit, or at runtime by using the size clause in MQL temp query or expand bus commands.

Using a size of 0 is the same as not using the Size clause and the expand is streamed using the value of MX_QUERY_PAGE_SIZE or the classes in the Studio Customization Toolkit, if set. For information on MX_QUERY_PAGE_SIZE, see the Installation Guide: Optional Variables. The Size clause overrides any other settings.

Enabling streaming through the Java classes usually provides the greatest improvement in performance and memory consumption.

When processing selectables in a streaming query, memory consumption is not necessarily reduced versus not using streaming. Although paging is used when the selectables are processed, the entire result set is still buffered in memory. For example:

temp query bus T N R size 1000 select to.from;

This retrieves business objects 1000 at a time, and only 1000 will ever be held in memory at one time. However, the evaluation of to.from for ALL of the found objects will be held in memory until the query has completed.

includeAdaplet Flag

The includeAdaplet flag is used to include Adaplet vaults in query searches if mxEnv.sh is configured to exclude those vaults.

To exclude Adaplet vaults from query searches, set the value of this parameter in mxEnv.sh to TRUE: MX_IGNORE_ADAPLET_VAULTS_DURING_QUERY

If you then want to include Adaplet values in a search, add the includeAdaplet flag to the temp query command. For example:

temp query bus * * * where attribute[attr1]==1000 includeAdaplet;

Examples

temp query select output is the same as print bus or set select , unless the dump keyword is used.

Example 1. Temp query without dump keyword:

MQL<7>temp query bus Note *e* * select owner;
businessobject Note BingTest 1
    owner = creator
businessobject Note CapTest 1
    owner = creator
businessobject Note attrtest 1
    owner = creator
businessobject Note attrtest1 1
    owner = creator
businessobject Note attrtest2 1
    owner = creator

Example 2. Temp query with dump keyword:

MQL<8>temp query bus Note *e* * select owner dump;
Note,BingTest,1,creator
Note,CapTest,1,creator
Note,attrtest,1,creator
Note,attrtest1,1,creator
Note,attrtest2,1,creator

Example 3. Print set select without dump keyword:

MQL<11>print set seltestset select owner;
set seltestset
  member businessobject Note BingTest 1
    owner = creator
  member businessobject Note CapTest 1
    owner = creator
  member businessobject Note attrtest 1
    owner = creator
  member businessobject Note attrtest1 1
    owner = creator
  member businessobject Note attrtest2 1
    owner = creator

Example 4. Print set select with dump keyword:

MQL<12>print set seltestset select owner dump;
creator
creator
creator
creator
creator