Agile Product Lifecycle Management SDK Developer Guide - Using APIs Release 9.3.3 E39307-02 |
|
![]() Previous |
![]() Next |
This chapter includes the following:
About Queries
Creating a Query
Saving a Query to a Folder
Generating Ordered (sorted) or Unordered Query Results
Specifying Query Attributes when Creating a Query
Specifying Search Criteria
Using SQL Syntax for Search Criteria
Setting Result Attributes for a Query
Working with Query Results
Creating a Where-Used Query
Loading a Query
Deleting a Query
Simple Query Examples
An IQuery
is an object that defines how to search for Agile PLM data. It defines a search similar to the searches that you can use in Agile Web Client. The search can have multiple search criteria (like an Advanced Search in Agile Web Client), or it can be a simple search that specifies only one criterion.
To create and execute a query, you must first create an IQuery
object. As with other Agile API objects, you create the object using the IAgileSession.createObject()
method.
In its simplest form, the parameters that you pass with the createObject()
method to create a query are the IQuery object type and the query class used in the search. In the following example, the query class is the Item class.
Example 3-1 Creating a Query
try {IQuery query = (IQuery)session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_ITEM_BASE_CLASS); query.setCaseSensitive(false); query.setCriteria("[Title Block.Number] starts with 'P'"); ITable results = query.execute(); } catch (APIException ex) { System.out.println(ex);}
The query class you specify with the createObject()
method also includes objects from all of its subclasses. For example, if you search for objects in the Item class, the results include parts and documents. If you search for objects in the Change class, the results include objects from all Change subclasses (Deviation, ECO, ECR, MCO, PCO, SCO, and Stop Ship). If you want to search only a specific subclass, you should explicitly specify that class.
The following example shows how to create a query that searches for objects in a subclass named Foobar:
After you name a query using the IQuery.setName()
method, you can add it to a folder. The following example shows how to name a query and add it to the Personal Searches folder. You can retrieve the query from the folder later to reuse it.
Example 3-3 Naming a query and adding it to a folder
try {IQuery query =(IQuery)session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_ITEM_BASE_CLASS);
query.setCaseSensitive(false);query.setCriteria("[Title Block.Number] starts with 'P'");query.setName("Items Whose Number Starts with P");
I Folder folder =(IFolder)m_session.getObject(IFolder.OBJECT_TYPE,"/Personal Searches");folder.addChild(query);
} catch (APIException ex) {} System.out.println(ex); }
You can also use the IQuery.saveAs()
method to name a query and save it to a folder.
Example 3-4 Using IQuery.saveAs() to save a query to a folder
try {IQuery query = (IQuery)session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_ITEM_BASE_CLASS);query.setCaseSensitive(false);query.setCriteria("[Title Block.Number] starts with 'P'");IFolder folder = (IFolder)m_session.getObject(IFolder.OBJECT_TYPE,
"/Personal Searches"); query.saveAs("Items Whose Number Starts with P", folder);
}
} catch (APIException ex) {
System.out.println(ex);
}
Note: Any query that you create without explicitly saving it to a folder is considered a temporary query. The Agile Application will automatically delete all temporary queries when the user session is closed. |
As shown in examples for "Creating a Query," executing IQuery.execute()
and IQuery.execute(Object[] params)
methods returns an ordered query result in ITable
.
To improve query performance, the SDK provides the following methods to return results that are not sorted in the default order. However, if the query criteria starts with conditions, then results are always sorted on that attribute and passing skipOrdering
as true in execute(boolean)
does not skip ordering.
IQuery.execute(boolean skipOrdering)
IQuery.execute(Object[] params, boolean skipOrdering)
To skip or perform ordering, set the boolean skipOrdering
to true or false as shown in the following example.
Example 3-5 Skip ordering in query results
try { IQuery query = (IQuery)session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_ITEM_BASE_CLASS);
query.setCaseSensitive(false); query.setCriteria("[Title Block.Number] starts with 'P'"); // The boolean is set to true to skip ordering ITable results = query.execute(true)
; } catch (APIException ex) { System.out.println(ex);}
Instead of passing only the query class when you create a query, you can use a more advanced form of the createObject()
method and pass a Map object containing one or more attribute values. The QueryConstants
class contains several constants for query attributes that you can set when you create a query. These are virtual attributes that do not exist in the Agile PLM database, but that can be used to define the query at run time.
Attribute Constant | Description |
---|---|
ATT_CRITERIA_CLASS |
Query class |
ATT_CRITERIA_PARAM |
Search condition parameter value for a parameterized search condition |
ATT_CRITERIA_STRING |
Search condition string |
ATT_PARENT_FOLDER |
Parent folder where the query resides |
ATT_QUERY_NAME |
Query name |
The following example shows how to set the query class, search condition, parent folder, and query name when you create the query.
Example 3-6 Specifying query attributes when you create a query
try {String condition = "[Title Block.Number] starts with 'P'";IFolder parent = (IFolder)m_session.getObject(IFolder.OBJECT_TYPE, "/Personal Searches");HashMap map = new HashMap();map.put(QueryConstants.ATT_CRITERIA_CLASS,ItemConstants.CLASS_ITEM_BASE_CLASS);map.put(QueryConstants.ATT_CRITERIA_STRING, condition);map.put(QueryConstants.ATT_PARENT_FOLDER, parent);map.put(QueryConstants.ATT_QUERY_NAME, "Part Numbers Starting with P");IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, map); ITable results = query.execute();} catch (APIException ex) System.out.println(ex);}
You can narrow the number of objects returned from a search by specifying search criteria. If you don't specify search criteria, the query returns references to all objects in the specified query class. It's a good idea to limit the search criteria as much as possible, as the amount of data returned may be excessively large, resulting in decreased performance.
There are three different setCriteria()
methods you can use to specify query criteria:
setCriteria(ICriteria criteria)
- Sets the query criteria from data stored in the Criteria administrative node. The Criteria administrative node defines reusable criteria for the Workflow, but the nodes can also be used as ordinary search criteria.
Note: Workflow query is not supported in the current Release of the SDK. |
setCriteria(java.lang.String criteria)
- Sets the search criteria from a specified String.
setCriteria(java.lang.String criteria, java.lang.Object[] params)
- Sets the search criteria from a specified String that references one or more parameters.
Unless you use the first setCriteria()
method, which takes an ICriteria
object for its parameter, the Agile API parses the search criteria as a String.
The Agile API provides a simple yet powerful query language for specifying search criteria. The query language defines the proper syntax for filters, conditions, attribute references, relational operators, logical operators, and other elements.
Search criteria consist of one or more search conditions. Each search condition contains the following elements:
Left operand - The left operand is always an attribute enclosed in brackets, such as [Title Block.Number]
. You can specify the attribute as an attribute name (fully qualified name or short name) or attribute ID number. The attribute specifies which characteristic of the object to use in the search.
Relational operator - The relational operator defines the relationship that the attribute has to the specified value, for example, ”equal to” or ”not equal to.”
Right operand - The matching value for the specified attribute in the left operand. The right operand can be a constant expression or a set of constant expressions. A set of constant expressions is needed if the relational operator is ”between,” ”not between,” ”in,” or ”not in.”
Following are two search condition examples.
When you specify a search condition, you must use proper keywords to construct the statement. The following keywords are available:
and |
does | less | or | to |
asc |
equal |
like |
order |
union |
between |
from |
minus |
phrase |
where |
by |
greater |
none |
select |
with |
contain |
in |
not |
start |
word |
contains |
intersect |
null |
starts |
words |
desc |
is |
of |
than |
than |
Query language keywords are not localized. You must use English keywords, regardless of locale. You can use the keywords in lower case or upper case. In addition to keywords, you can use Agile PLM variables such as $USER
(for current user) and $TODAY
(for today's date) in Agile API queries.
Note: The ”in ”operator does not support MultiList in (set) query criteria. |
Every Agile PLM object that you can search for also has an associated set of attributes, which are inherent characteristics of the object. You can use these attributes as the left operand of a search condition. The right operand of the search condition specifies the attribute's value(s).
A search attribute must be enclosed within brackets, for example, [Title Block.Number]. The brackets are needed because many attribute names have spaces. If a search attribute is not enclosed within brackets, your query will fail.
You can specify a search attribute in the following ways:
Attribute reference | Example |
---|---|
attribute ID number | [1001] |
fully-qualified attribute name | [Title Block.number] |
short attribute name | [Number] |
Note: Because attribute names can be modified, Oracle recommends referencing attributes by ID number or constant. However, many of the examples in this chapter reference attributes by name simply to make them more readable. If you choose to reference attributes by name, use the fully-qualified attribute name instead of the short name. Short attribute names are not guaranteed to be unique and could therefore cause your query to fail or produce unexpected results. |
Attribute names, whether you use the long or short form, are case-insensitive. For example, [Title Block.Number]
and [TITLE BLOCK.NUMBER]
are both allowed. Attribute names are also localized. The names of Agile PLM attributes vary based on the locale of your Agile Application Server. If you are creating a query that is going to be used on servers in different locales, you should reference attributes by ID number (or the equivalent constant) instead of by name.
Note: The APIName field, described in "Accessing Metadata Using the APIName Field," does not support specifying any search attributes. |
If the attribute name contains special characters, such as quotes or back slashes, you can type these characters using the backslash (\) as an escape character. For example, to include a quote character in your string, type \'. If you want to write a backslash, type two of them together (\\). If the attribute name contains square brackets, enclose the entire name in quotes:
['Page Two.Unit of Measure [g or oz]'] query.setCriteria("[%0] == 'Computer'", new Object[] { attr });
There are other less intuitive ways to specify attributes. For example, you can pass in an IAttribute
reference using a parameter of the setCriteria()
method. In the following example, '%0' references the attribute in the Object array parameter.
You can also use String concatenation to reference an attribute constant:
query.setCriteria("[" + ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION + "] == 'Computer'");
The searchable attributes for a query depend on the specified query class or subclass. However, the searchable attributes for a subclass can differ greatly from searchable attributes for its parent class.
Due to database considerations, not all attributes are searchable. Generally, users select Page One attributes such as, Title Page, Cover Page, and General Info which are searchable for each class.
If a tab is not configured in Java Client to be visible, you can still search for an attribute on that tab in the Agile SDK. However, you must search for the Table name that corresponds to the Tab name.
Note: Because you use the table name to setupIQuery , it does not matter if an Agile administrator changes a Tab name from the name specified in Agile Java Client. Tab name changes do not affect SDK table names. |
To find a query's searchable attribute:
Use the IQuery.getSearchableAttributes()
method.
Note: Even though an attribute may not be searchable, it can still be included as a column in the query results. For more information, see "Setting Result Attributes for a Query." |
Agile PLM generates sequence IDs for all saved queries. However, these query IDs are subject to change, because they are associated with new IDs after a Re-order. The following SDK calls get a query's sequence IDs before and after a Re-Order.
Example 3-9 Getting the Sequence ID of a Saved Query
//Before Re-order, the id of one saved query QueryA is 1001. The SDK script is as below when you try to get the QueryA IQuery query = (IQuery) session.getObject(IQuery.OBJECT_TYPE, new Integer(1001));
Example 3-10 Getting the Sequence ID of a Query after the Re-Order
// After Re-order, the id of QueryA is changed. Assuming it is changed to 102, the //customer needs to modify the SDK script to correctly get QueryA as shown below. IQuery query = (IQuery) session.getObject(IQuery.OBJECT_TYPE, new Integer(102));
Table below lists relational operators that are supported by the Agile API query language.
English operator | Notation | Description |
---|---|---|
equal to |
== |
Finds only an exact match with the specified value. |
not equal to |
!= |
Finds any value other than an exact match with the specified value. |
greater thanr |
> |
Finds any value greater than the specified value. |
greater than or equal to |
>= |
Finds any value greater than or equal to the specified value. |
less than |
< |
Finds any value less than the specified value. |
less than or equal to |
<= |
Finds any value less than or equal to the specified value. |
contains, contains all |
Finds any value that includes the specified value. | |
contains any | Finds any value that does not include the specified value | |
does not contain any |
Finds any value that does not include the specified value. | |
contains none of | Finds any value that includes none of the specified values. | |
does not contain none of |
Behaves the same as does not contain any. |
|
starts with |
Finds values that begin with characters in the specified value. | |
does not start with |
Finds values that do not begin with characters in the specified value. | |
is null |
Finds objects where the selected attribute contains no value. | |
is not null |
Finds objects where the selected attribute contains a value. | |
like |
Performs a wildcard search, finding objects that match a single character or any string. |
|
not like |
Performs a wildcard search, finding objects that do not match a single character or any string. | |
between |
Finds objects that fall between the specified values. | |
not between |
Finds objects that do not fall between the specified values. | |
in |
Finds objects that match any of the specified values. | |
not in |
Finds objects that do not match any of the specified values. | |
contains phrase |
Finds objects with files that contain the specified phrase. | |
contains all words |
Finds objects with files that contain all of the specified words. | |
contains any word |
Finds objects with files that contain any of the specified words. | |
contains none of |
Finds objects with files that contain none of the specified words |
Relational operators are not localized. You must use English keywords, regardless of locale. As with other query language keywords, you can use them in lower case or upper case.
Agile SDK Query language supports Unicode escape sequences. The primary use of a Unicode escape sequence in a query string is to search for nonburnable or foreign local character sets. A Unicode character is represented with the Unicode escape sequence \uxxxx, where xxxx is a sequence of four hexadecimal digits.
For example, to search for an item with Unicode 3458, use the following query:
Select* from [Items] where [Description] contains '\u3458'
For contains' usage in the case of a MultiList,the query operation is different.
The 'between
', 'not between
', 'in
', and 'not in
' relational operators are not supported directly by Agile PLM Java and Web Clients. These relational operators provide a convenient shorthand method for specifying 'equal to
', 'not equal to, 'greater than
' or 'equal to
', or 'less than
' or 'equal to
' operations with a set of values.
Short form | Equivalent long form |
---|---|
[Number] between ('1','6') |
[Number] >= '1' and [Number] <= '6' |
[Number] not between ('1','6') |
[Number] < '1' and [Number] > '6' |
[Number] in ('1','2','3','4',5','6') |
[Number] == '1' or [Number] == '2' or [Number] == '3' or [Number] == '4' or [Number] == '5' or [Number] == '6' |
[Number] not in ('1','2','3','4','5','6') |
[Number]!= '1' and [Number] != '2' and [Number] != '3' and [Number] != '4' and [Number] != '5' and [Number] != '6' |
As shown in the preceding table, when you use the 'between
', 'not between
', 'in
', and 'not in
' relational operators, each value in the set of values must be enclosed in quotes and delimited by commas. These are additional examples that use 'between' and 'in
' relational operators:
[Title Block.Number] in ('1000-02', '1234-01', '4567-89')[Title Block.Effectivity Date] between ('01/01/2001', '01/01/2002')[Page Two.Numeric01] between ('1000', '2000')
Note: The relational operatorsany, all, none of , and not all are not supported in the SDK. |
Several lists in Agile PLM contain business objects, such as Agile PLM users. To search for an object in such a dynamic list, you can specify nested query criteria. Nested criteria are enclosed in parentheses and separated from each other by a logical AND (&&) or OR (||) operator. A comma can also be used to separate nested criteria; it's equivalent to a logical OR.
The following criteria find a user with the first name Christopher OR the last name Nolan.
[Page Two.Create User] in ([General Info.First Name] == 'Christopher',[General Info.Last Name] == 'Nolan')
The following criteria find a user with the first name Christopher AND the last name Nolan.
[Page Two.Create User] in ([General Info.First Name] == 'Christopher' &&[General Info.Last Name] == 'Nolan')
If Part.Page Three.List01
is enabled and is set to Part Families
list, the following criteria finds a Part Family with the name PartFamily_01
.
[Page Three.List01] in ([General Info.Name] == 'PartFamily_01')
The parameter query is not supported in nested queries and multiple values for one placeholder in query parameters must be specified in two dimensional arrays as shown in the following correct and incorrect examples of parameter query in nested query criteria:
The parameter query specified in the following nested query criteria will fail to execute:
[Page Two.User1] in ([General Info.First Name] == %0)
However, when it is explicitly specified as a string value, instead of the placeholder, it will succeed:
[Page Two.User1] in ([General Info.First Name] == 'Christopher')
Criteria nodes in Java or Web Client's Criteria library are ICriteria
objects that you can use in SDK queries. To view a listing in Java Client as shown below, select Admin > Settings > Data Settings > Criteria.
The following example gets a Criteria node from the Criteria library and loads and sets it as the SDK query criteria.
Example 3-11 Using criteria from the Criteria Library in SDK queries
IQuery query = (IQuery) session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_ITEM_BASE_CLASS); IAdmin admin = session.getAdminInstance(); // Get the Criteria Library nodeINode criteriaLibrary = admin.getNode(NodeConstants.NODE_CRITERIA_LIBRARY);// Load the Criteria relevant to the query class (For example it is Items base class) ICriteria criteria = (ICriteria) criteriaLibrary.getChild("All Released Items"); // Set the ICriteria in SDK Query Criteria query.setCriteria(criteria);
Agile SDK provides APIs to perform the Relationships and Content Search using the IQuery
interface. The query criteria can contain the attributes of both the base search class and the related class.
To search using an object's Relationships:
Set searchType
to QueryConstants.RELATIONSHIPS
using IQuery.setSearchType(int searchType)
.
Set the related class using IQuery.setRelatedContentClass(Object relatedClass)
.
Example 3-12 Using an object's Relationships as the query criteria
IQuery query1 = (IQuery) session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_PART);query1.setSearchType(QueryConstants.RELATIONSHIPS);query1.setRelatedContentClass("Substance"); // ID or API Namequery1.setCriteria("[Relationships.Name] Is Not Null and [Title Block.Number] equals ’P00001' and [Relationships.Substance.General Info.Name] Is Not Null");
To search using a Project object's Content:
Set searchType
to QueryConstants.RELATIONSHIPS
using IQuery.setSearchType(int searchType)
.
Set the related class using IQuery.setRelatedContentClass(Object relatedClass)
.
Example 3-13 Using a Project object's Content as the query criteria
IQuery query1 = (IQuery) session.createObject(IQuery.OBJECT_TYPE ProgramConstants.CLASS_ACTIVITIES_CLASS);query1.setSearchType(QueryConstants.RELATIONSHIPS);query1.setRelatedContentClass("ECO"); // ID or API Namequery1.setCriteria("[Content.Criteria Met] Is Not Null and [Content.ECO.Cover Page.Originator] in ([General Info.First Name] == ’admin')
To search using a Transfer Orders object's Selected Content:
Set searchType
to QueryConstants.TRANSFER_ORDER_SELECTED_CONTENT
using IQuery.setSearchType(int searchType)
.
Set the related class using IQuery.setRelatedContentClass(Object relatedClass)
.
Example 3-14 Using a Transfer Orders object's Selected Content as the query criteria
IQuery query1 = (IQuery) session.createObject(IQuery.OBJECT_TYPE, TransferOrderConstants.CLASS_CTO);query1.setSearchType(QueryConstants.TRANSFER_ORDER_SELECTED_CONTENT);query1.setRelatedContentClass("ECR"); // ID or API Namequery1.setCriteria("[Selected Content.ECR.Cover Page.Number] equal to 'C0001'");
Two special attributes, [Attachments.File Document Text]
and [Files.Document Text] are used to index files and search for files indexed by File Manager that reside on the Agile file management server. If you are hosting your database on Oracle, you can take advantage of a feature that lets you search for words or phrases contained in attachments, when you create search criteria that use either of these attributes.
There are four additional relational operators that you can use:
contains phrase
contains all words
contains any word
contains none of
The following table shows several search conditions that search for words or phrases in attachments.
Search Condition | Finds |
---|---|
[Attachments.File Document Text] contains phrase 'adding new materials' |
Objects in which any of their attachments contain the phrase ”adding new materials ” |
all[Attachments.File Document Text] contains all words 'adding new materials ' |
Objects in which all their attachments contain the words ”adding", "new ”and ”materials” |
none of [Attachments.File Document Text] contains any word 'containers BOM return output' |
Objects in which none of their attachments contain any of the words "containers," "BOM," "return," or ”output ” |
[Attachments.File Document Text] contains none of 'containers BOM output' ' |
Objects in which any of their attachments do not contain the words "containers," "BOM," or ”output ” |
Orphaned parts are parts that do not have parents. This arises from the notion that in a BOM Tree, there are Parents and Children. When the Child part no longer has any Parents, it is an Orphan part. SDK Provides the following two attributes to enable retrieving Orphan Parts:
Where Used.Item Number[1039]
- The null value returns all revision having no parents and Where [1039]
is null.
Where Used.Item Number All Revision
- The null value returns all revision having no parents and Where [20W00025272]
is null.
The following code sample uses these attributes to retrieve Orphan parts in the current version and all versions of the BOM.
Example 3-15 Searching for Orphan parts
System.out.println("Where Used.Item Number[1039]
=" +ItemConstants.ATT_WHERE_USED_ITEM_NUMBER);
System.out.println("Where Used.Item Number All Revision[2000025272] = " +ItemConstants.ATT_WHERE_USED_ITEM_NUMBER_ALL_REVISIONS);
IAgileSession session = AgileSessionFactory.createSessionEx(params);IQuery query = (IQuery)session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_PARTS_CLASS);query.setCaseSensitive(false);query.setCriteria("[1039] is null");
//query.setCriteria("[2000025272] is null");ITable results = query.execute();System.out.println("result size:"+results.size());
When you specify criteria for a query, you can use a number preceded by a percent sign (%) to indicate a parameter placeholder. The parameter value is specified later, for example at runtime. Parameters provide a convenient way to pass values to a query, and they can save time and reduce extra coding. Parameterized queries can be saved and reused later.
Note: The right hand operand query parameter supports one placeholder per each query operator, so if the query criteria have three query operators, then the query can have a total of three placeholders corresponding to the three operators. The between and not between query operations are different. For example,[2091] contains none of (%0,%1); is not allowed, but [2091] contains none of (%0); is allowed, and query.execute(new Object[]{new Object[]{"B", "C"}}); is not allowed. |
Indexes for query parameters are 0-based. Parameters are numbered 0, 1, 2, and so on. Always enumerate the parameters in ascending order. The following example shows a query with three parameters whose values are specified using the IQuery.execute(Object[])
method.
Example 3-16 Parameterized query using IQuery.execute(Object[])
public ITable runParameterizedQuery() throws Exception { String condition = "[Title Block.Number] starts with%0
and" + "[Title Block.Part Category] ==%1
and" + "[Title Block.Description] contains%2
";IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_PART); query.setCriteria(condition);ITable table = query.execute(new Object[] {"1", "Electrical", "Resistor"});
return table;}
You can also specify query parameters using IQuery.setParams()
method, as shown in the following example. Make sure you set the query parameter values before calling IQuery.execute()
. Otherwise, when you run the query it will use previous parameter values. If parameters have not been set, the query uses null values. Similarly, if you do not pass any parameters to a query, then the IQuery.getParams()
method returns null.
Example 3-17 Parameterized query using IQuery.setParams()
public ITable runParameterizedQuery() throws Exception { String condition = "[Title Block.Number] starts with%0
and" + "[Title Block.Part Category] ==%1
and" + "[Title Block.Description] contains%2
"; IQuery query =IQuery) m_session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_ PART); query.setCriteria(condition); query.setParams(new Object[] {"1", "Electrical", "Resistor"});
ITable table = query.execute(); return table;}
Do not use quote characters around parameterized queries because they will create a set of values (more than one element) for the query when parameters can only refer to a given value. The following examples show the proper use of quote characters when creating parameterized queries:
Several types of queries require date values. To pass a date as a String, use the IAgileSession.setDateFormats()
method to specify a date format. The setDateFormats()
method also applies to all Agile API values that you specify with setValue()
methods.
Note: If you do not set date formats explicitly using thesetDateFormats() method, the Agile API uses the user's date format for the Agile PLM system. To see your date format in Agile Web Client, choose Settings > User Profile and then click the Preferences tab. |
Example 3-19 Setting the date format for a query
m_session.setDateFormats(new DateFormat[] {new SimpleDateFormat("MM/dd/yyyy")})
;query.setCriteria("[Title Block.Rev Release Date] between” +”('9/2/2001', '9/2/2003')");query.setCriteria
("[Title Block.Rev Release Date] between (%0,%1)", new String[] {"9/2/2001", "9/2/200
Alternatively, if you use the setCriteria(String criteria, Object[] params)
method, you can pass Date objects as parameters to the method.
You can use logical operators to combine multiple search conditions into a complex filter. When you have two or more conditions defined in a set of query criteria, the relationship between them is defined as either 'and
' or ''.
and narrows the search by requiring that both conditions are met. Each item in the results must match both conditions. The 'and' logical operator can also be specified using two ampersands, '&&'.
or broadens the search by including any object that meets either condition. Each item in the results table needs to match only one of the conditions, but may match both. The 'or' logical operator can also be specified using two vertical bars, '||'.
Logical operators are case-insensitive. For example, 'and' or 'AND' are both allowed.
The following query criteria finds parts that have both a part category equal to Electrical and a lifecycle phase equal to Inactive.
[Title Block.Part Category] == 'Electrical
'
and
[Title Block.Lifecycle Phase] == 'Inactive'
If you replace the 'and' operator with 'or', the query locates all parts with either a part category of Electrical or a lifecycle phase of Inactive, which could be a large number of parts.
[Title Block.Part Category] == 'Electrical'
or
[Title Block.Lifecycle Phase] == 'Inactive'
Note: The Agile API provides three where-used set operators. For more information, see "Creating a Where-Used Query." Logical operators, including the where-used set operators, are not localized. You must use English keywords, regardless of locale. |
If you define a search condition using the 'like' operator, you can use two wildcard characters: the asterisk (*) and question mark (?). The asterisk matches any string of any length, so *at finds cat, splat, and big hat.
For example, [Title Block.Description] like '*book*'
returns all objects that contain the word ”book,” such as textbook, bookstore, books, and so on.
The question mark matches any single character. For example, ”?at” finds hat, cat, and fat, but not splat. For example, [Title Block.Description] like '?al*'
matches any word containing ”al” that is preceded by a single letter, such as tall, wall, mall, calendar, and so on.
Where-used, set operators have higher priority than and as well as the or logical operators, as shown by the following table.
Priority | Operator(s) |
---|---|
1 | union
intersection minus |
2 | and
or |
Therefore, search conditions joined by union, intersection, and minusand
operators are evaluated before conditions joined by or or
.
If you use where-used set operators ('union', 'intersect', or 'minus') in search criteria, you can use parentheses to change the order that criteria are evaluated. If only 'and
' or 'or
' logical operators are used in a search criteria, additional parentheses aren't needed because they do not change the result of criteria evaluation.
The following two criteria, although they contain the same search conditions, provide different results because parentheses are placed differently:
([Title Block.Part Category] == 'Electrical' and
[Title Block.Description] contains 'Resistor') union
([Title Block.Description] contains '400' and
[Title Block.Product Line(s)] contains 'Taurus')
[Title Block.Part Category] == 'Electrical' and
([Title Block.Description] contains 'Resistor' union
[Title Block.Description] contains '400') and
[Title Block.Product Line(s)] contains 'Taurus'
When using the SDK to query lists that contain a large number of objects, you can improve performance if you use the object ID in the query criteria to set the value for the list. For example, you can replace this routine:
query.setCriteria("[Page Three.List25] equal to 'Administrator (admin)'");
with the following to improve performance:
IUser user = (IUser)session.getObject(IUser.OBJECT_TYPE, ”admin”);
query.setCriteria(”[Page Three.List25] equal to ”+user.getObjectId());
In addition to its standard query language, the Agile API also supports SQL-like syntax for search criteria. If you're familiar with how to write SQL statements, you may find this extended query language easier to work with, more flexible, and more powerful. It combines in one operation the specification of the query result attributes, the query class, the search condition, and the sort column(s).
This is a simple example that demonstrates the syntax:
Query result attributes: SELECT [Title Block.Number], [Title Block.Description]
Query class: FROM [Items]
Search condition: WHERE [Title Block.Number] starts with 'P'
Sort column(s): ORDER BY 1 asc
To improve readability, it's recommended that SQL key words such as SELECT
and FROM
are all typed using capital letters and each part of the statement appears on a separate line. This is merely a convention, not a requirement. SQL key words are not case-sensitive, and you can write the entire query string on one line if you prefer.
The best way to demonstrate the advantages of SQL syntax is to compare the code for a query that uses standard Agile API query syntax for search criteria with one that uses the SQL syntax. This is an example of a query created using the standard Agile API query syntax:
Example 3-21 Query using the standard Agile API query syntax
try {IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, "Items");query.setCriteria("[Page Two.Nummeric01] between (1000, 2000)");//Set result attributesString[] attrs = { "Title Block.Number", "Title Block.Description", query.setResultAttributes(attrs);//Run the queryITable results = query.execute();} catch (APIException ex) {System.out.println(ex); }
This example shows the same query rewritten in SQL syntax. Although the example doesn't have fewer lines of code, you may find that it's more readable than Agile API query syntax, particularly if you're familiar with SQL.
Example 3-22 Query using the SQL syntax
try {IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE,"SELECT " +"[Title Block.Number],[Title Block.Description], " +"[Title Block.Lifecycle Phase] " +"FROM " +"[Items] " +"WHERE " +"[Title Block.Number] between (1000, 2000)");//Run the queryITable results = query.execute();} catch (APIException ex) {System.out.println(ex);}
The following example shows a query written with SQL syntax that specifies the search criteria using the ATT_CRITERIA_STRING
query attribute. For more information about how to use query attributes, see Specifying Query Attributes when Creating a Query.
Example 3-23 Using SQL syntax to specify query attributes
try {String statement ="SELECT " +"[Title Block.Number], [Title Block.Description] " +"FROM " +"[Items] " +"WHERE " +"[Title Block.Description] like %0";HashMap map = new HashMap();map.put(QueryConstants.ATT_CRITERIA_STRING, statement);map.put(QueryConstants.ATT_CRITERIA_PARAM, new Object[] { "Comp*" } );IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE, map);ITable results = query.execute();} catch (APIException ex) {System.out.println(ex);}
Note: Recall, theFROM part of the search condition specifies the query class. If you use the ATT_CRITERIA_CLASS attribute to also specify a query class, the query class specified in the SQL search condition takes precedence. |
Although you can use the IQuery.setCriteria()
method to specify a search condition in SQL syntax, the IQuery.getCriteria()
method always returns the search condition in the standard Agile API query syntax.
You can use both the asterisk (*) and question mark (?) wild cards in a query that uses SQL syntax. As in standard Agile API query language, the asterisk matches any string and the question mark matches any single character. You can use wild cards in the SELECT
statement (the specified query result attributes) and the WHERE
statement (the search condition). For example, ”SELECT *” specifies all available query result attributes.
If you specify search criteria using SQL syntax instead of the standard Agile API query language, you can use the ORDER BY
keyword to sort the query results. You can sort the results in ascending or descending order by any attributes specified in the SELECT statement.
In the ORDER BY
statement, refer to attributes by the one-based numerical order in which they appear in the SELECT statement. To specify whether to sort in ascending or descending order, type ”asc
”or ”desc
”after the attribute number. If ”asc
" or "desc
" is omitted, ascending order is used by default.
Example | Description |
---|---|
ORDER BY 1 | Sort by the first SELECT attribute in ascending order (the default) |
ORDER BY 2 desc | Sort by the second SELECT attribute in descending order |
ORDER BY 1 asc, 3 desc | Sort by the first SELECT attribute in ascending order and the third SELECT attribute in descending order |
Attributes not specified in the SELECT statement cannot be used to sort query results. Also, if you use ”SELECT *” to select all available result attributes, the results cannot be sorted because the attribute order is undefined.
The following example sorts results in ascending order by [Title Block.Number]
and [Title Block.Number],
the first and third attributes in the SELECT statement.
Example 3-24 Using SQL syntax to sort query results
IQuery query = (IQuery)m_session.createObject(IQuery.OBJECT_TYPE,"SELECT " +"[Title Block.Number],[Title Block.Description], " +"[Title Block.Sites],[Title Block.Lifecycle Phase] " +"FROM " +"[Items] " +"WHERE " +"[Title Block.Number] between (1000, 2000)" +"ORDER BY " +"1, 3" );
When you run a query, it returns several output fields, which are also called result attributes. By default, there are only a few result attributes for each query class. You can add or remove result attributes using the IQuery.setResultAttributes()
method.
The following table shows the default query result attributes for each predefined Agile PLM class.
Query class | Default result attributes |
---|---|
Changes | Cover Page.Change Type
Cover Page.Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
Customers | General Info.Customer Type
General Info.Customer Number General Info.Customer Name General Info.Description General Info.Lifecycle Phase |
Declarations | Cover Page.Name
Cover Page.Description Cover Page.Supplier Cover Page.Status Cover Page.Workflow Cover Page.Compliance Manager Cover Page.Due Date Cover Page.Declaration Type |
Discussions | Cover Page.Subject
Cover Page.Status Cover Page.Priority Cover Page.Type |
File Folders | Title Block.Type
Title Block.Number Title Block.Description Title Block.Lifecycle Phase |
Items | Title Block.Item Type
Title Block.Number Title Block.Description Title Block.Lifecycle Phase Title Block.Rev |
Manufacturers | General Info.Name
General Info.City General Info.State General Info.Lifecycle Phase General Info.URL |
Manufacturer Parts | General Info.Manufacturer Part Number
General Info.Manufacturer Name General Info.Description General Info.Lifecycle Phase |
Packages | Cover Page.Package Number
Cover Page.Description Cover Page.Assembly Number Cover Page.Status Cover Page.Workflow |
Part Groups | General Info.Name
General Info.Description General Info.Lifecycle Phase General Info.Commodity Type General Info.Overall Compliance |
Prices | General Info.Price Number
General Info.Description General Info.Rev General Info.Price Type General Info.Lifecycle Phase General Info.Projects General Info.Customer General Info.Supplier |
Product Service Requests | Cover Page.PSR Type
Cover Page.Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
Projects | General Info.Name
General Info.Description General Info.Status General Info.Health General Info.Owner General Info.Root Parent General Info.Workflow General Info.Type |
Sourcing Projects | General Info.Project Type
General Info.Number General Info.Description General Info.Manufacturing Site General Info.Ship To Location General Info.Projects General Info.Customer General Info.Lifecycle Phase |
Quality Change Requests | Cover Page.QCR Type
Cover Page.QCR Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
RFQ Responses | Cover Page.RFQ Number
Cover Page.RFQ Description Cover Page.Lifecycle Phase Cover Page.Requested Cover Page.Completed Cover Page.Due Date |
RFQs | Cover Page.RFQ Number
Cover Page.RFQ Description Cover Page.MFG Site Cover Page.Ship-To Location Cover Page.Projects Cover Page.Customer Cover Page.Lifecycle Phase Cover Page.RFQ Type |
Sites | General Info.Name
General Info.Contact General Info.Phone |
Specifications | General Info.Name
General Info.Description General Info.Lifecycle Phase General Info.Jurisdictions General Info.Validation Type General Info.Specification Type |
Substances | General Info.Name
General Info.Description General Info.CAS Number General Info.Lifecycle Phase General Info.Substance Type |
Suppliers | General Info.Supplier Type
General Info.Number General Info.Name General Info.Description General Info.Status |
Transfer Orders | Cover Page.Transfer Order Type (See "Retrieving CTO Originator Name.")
Cover Page.Transfer Order Number Cover Page.Description Cover Page.Status Cover Page.Workflow |
If you run a query and find that the resulting ITable
object does not contain the attributes you expected, it's because you didn't specify result attributes. The following example shows how to specify the result attributes for a query.
Example 3-25 Setting query result attributes
private void setQueryResultColumns(IQuery query) throws APIException { �// Get Admin instance � IAdmin admin = m_session.getAdminInstance(); �// Get the Part class � IAgileClass cls = admin.getAgileClass("Part"); �// Get some Part attributes, including Page Two and Page Three attributes � IAribute attr1 = cls.getAttribute(ItemConstants.ATT_TITLE_BLOCK_NUMBER); � IAribute attr2 = cls.getAttribute(ItemConstants.ATT_TITLE_BLOCK_DESCRIPTION); � IAribute attr3 = cls.getAttribute(ItemConstants.ATT_TITLE_BLOCK_LIFECYCLE_PHASE);� IAttribute attr4 = cls.getAttribute(ItemConstants.ATT_PAGE_TWO_TEXT01); � IAttribute attr5 = cls.getAttribute(ItemConstants.ATT_PAGE_TWO_NUMERIC01); � IAttribute attr6 = cls.getAttribute(ItemConstants.ATT_PAGE_THREE_TEXT01); �// Put the attributes into an array � IAttribute[] attrs = {attr1, attr2, attr3, attr4, attr5, attr6}; �// Set the result attributes for the query � query.setResultAttributes(attrs);
}
When you use the setResultAttributes()
method, make sure you specify valid result attributes. Otherwise, the setResultAttributes()
method will fail. To get an array of available result attributes that can be used for a query, use getResultAttributes()
, as shown in the following example.
The Cover Page of the Content Transfer Order (CTO) includes the Originator field which specifies roles and site assignments of users who originate CTOs. To retrieve the user name, you can not query this field directly and need to retrieve data in UserConstants
. For example, the following statement which attempts to retrieve the user name directly, will not work:
QueryString = ("[Cover Page.Originator] equal to '<Last_name>, <First_name>'");
But the following statements which also specify the data in UserConstants will work:
QueryString = "[Cover Page.Originator] in (["+UserConstants.ATT_GENERAL_INFO_USER_ID+"]=='<UserID>')";
Or,
QueryString = ”[Cover Page.Originator] in ([”+UserConstants.ATT_GENERAL_INFO_LAST_NAME+”]=='<Last_name>'”+”&&
[”+UserConstants.ATT_GENERAL_INFO_FIRST_NAME+”]=='<First_name>');
The query criteria for any innumerable attribute type such as IItem, IChange
, and so on, must be in a nested form. This applies to the Originator attribute which points to Agile All users.
The manufacturing sites functionality of the Agile Application Server can have unintended results when you search for items or changes. If you search for items or changes and include a sites attribute ([Title Block.Site]
for items and [Cover Page.Site(s)]
for changes) in the result attributes, the query results include duplicate objects for each site associated with the object. Similarly, if you search for items and include an AML attribute-such as [Manufacturers.Mfr. Part Number]
in the result attributes, the query results include duplicate items for each manufacturer part listed on an item's Manufacturers table.
For example, a part with the number 1000-02 has five sites associated with it. If you search for that part and include Title Block.Site
in the result attributes, the resulting ITable
object returned by the IQuery.execute
method contains five rows, not one. Each row references the same object, part number 1000-02, but the Site cell has a different value. If you use ITable.getReferentIterator
to iterate through referenced objects in the search results, the duplicate objects would be more apparent; in this example, you would iterate over the same item five times.
When you run a query, the Agile API returns an ITable
object, which extends java.Util.Collection
. You can use the methods of ITable and of java.Util.Collection
to work with the results. For example, the following code shows how to use the Collection.iterator()
method.
Iterator it = query.execute().iterator();
The ITwoWayIterator interface enables you to traverse the list of rows in either direction using the next() and previous() methods.
ITwoWayIterator it = query.execute().getTableIterator();
ITwoWayIterator it = query.execute().getReferentIterator();
For more information about using ITwoWayIterator
, see "Iterating Over Table Rows."
Unlike other Agile API tables, you cannot create a sorted Iterator for query results using the ITable.ISortBy
interface. To sort query results, use SQL syntax and specify an ORDER BY
statement with the search criteria. For more information, see "Using SQL Syntax for Search Criteria."
Values in a query results table have the same datatype as their attributes. That is, if an attribute's datatype is an Integer
, its value in a query results table is also an Integer.
Important: In Agile 9.0 SDK, all values in a query results table were strings, but in post Agile 9.2, these values were converted to integers. |
Using PLM's Java Client, a user with Admin privileges can set the following Administrator Preferences by selecting Admin > Server Settings > Preferences and any one of these or other supported Preference. You can also set the above three Admin Preferences attributes from the SDK.
Maximum Query Results Displayed
Maximum BOM Reports Results
Search Based on (Table or Row)
For more information about Admin Preferences (Systemwide Preferences), settings, and returned values, refer to Agile PLM Administrator Guide. For SDK-related descriptions and procedures, see "Setting Maximum Query Results Displayed," "Setting Maximum BOM Report Results," and "Selecting Search Results Based on Table or Row."
This preference sets a limit on the maximum number of rows that are returned by a query and displayed on the monitor. However, this preference does not affect Agile PLM Clients. Queries that you run from an Agile SDK Client always return all results. That is, although you can access the entire query result set with the returned ITable
object, the Agile API internally manages retrieving partial results when necessary. For example, if a particular query returns 5000 records, you can use the ITable interface to access any of these 5000 rows, regardless of how many of the 5000 rows the Agile API actually loaded into memory.
Note: Searches that you run from other Agile PLM Clients, such as Agile Web Client, adhere to the limit set in the Maximum Query Results Displayed preference. |
This Admin Preference is based on the Maximum Report Results attribute which determines the maximum number of objects displayed in Agile PLM Custom Reports. This preference does not apply to Standard Reports and is overridden by the Full Search Display (FSD) privilege. For more information, refer to Agile PLM Administrator Guide.
Note: Users with the FSD privilege see all results of reports; also, all privilege checking is bypassed on users with this privilege when they view report results. Users without the FSD privilege see the maximum number of reports specified in this property. |
This Preference uses the Property.Constants.PROP_MAXIMUM_BOM_REPORT_RESULTS
attribute to set a limit on the maximum number of BOM Report Results returned.
Example 3-27 Setting Property.Constants.PROP_MAXIMUM_BOM_REPORT_RESULTS values
Example: /* This example sets the maximum BOM Report Results to 666 */ public static void testMaxBomReportResults() throws Exception { INode preferences = session.getAdminInstance().getNode(NodeConstants.NODE_PREFERENCES); prop = preferences.getProperty(PropertyConstants.PROP_MAXIMUM_BOM_REPORT_ RESULTS); Object original = prop.getValue();System.out.println("Current MaxBomReportResults value:" + original); prop.setValue("666");System.out.println("Changed MaxBomReportResults to:" + prop.getValue());}
This preference uses the PropertyConstants.PROP_SEARCH_BASED_ON
attribute to set the search based on Property, where Property is a list and valid values are ”Table” and ”Row.”
Example 3-28 Setting PropertyConstants.PROP_SEARCH_BASED_ON Table or Row
public static void testSearchBasedOn() throws Exception {INode preferences = session.getAdminInstance().getNode(NodeConstants.NODE_PREFERENCES);prop =preferences.getProperty(PropertyConstants.PROP_SEARCH_BASED_ON);Object original = prop.getValue();System.out.println("Current Search Based On value:" + original);IAgileList list = prop.getAvailableValues();// Valid values are "Table" and "Row"list.setSelection(new Object[] {"Table"});prop.setValue(list);System.out.println("Changed Search Based On property to:" + prop.getValue());}
The response time for running queries can be the biggest bottleneck in your Agile API program. To improve performance, you should try to construct queries that return no more than a few hundred results. A query that returns more than a 1000 results can take several minutes to finish processing. Such queries also eat up valuable processing on the Agile Application Server, potentially slowing down your server for all users.
Previous sections of this chapter described how to create queries that search for Agile PLM objects, for example, items or changes. You can also create where-used queries. In a where-used query, the search conditions define the items that appear on the BOMs of objects. You can use a where-used query to find the assemblies on which a particular part is used.
The interface for a where-used query is similar to a standard object query. With minor changes, you can turn an object query into a where-used query as long as the query class is an Item class.
Note: Where-used queries are only defined for Item classes. |
To define a where-used query, use the IQuery.setSearchType()
method. You can also use the following logical operators, also called where-used set operators, to further define the relationships between grouped sets of search conditions. Only one logical operator can be used for each search condition.
Where Used set operator | Description |
---|---|
intersect | Produces records that appear in both result sets from two different groups of search conditions. |
minus | Produces records that result from the first group of search conditions but not the second. |
union | Produces records that are the combination of results from two groups of search conditions. |
Note: Where-used set operators have higher priority than other logical operators. Therefore, search conditions joined by where-used set operations are evaluated before those joined by ”AND” or ”OR” operators. |
Example 3-29 A Where-used query
void btnFind_actionPerformed(ActionEvent e) {// Create the query try { IQuery wuquery =(IQuery)m_session.createObject(IQuery.OBJECT_TYPE, ItemConstants.CLASS_ITEM_BASE_CLASS);// Set the where-used typewuquery.setSearchType(QueryConstants.WHERE_USED_ONE_LEVEL_LATEST_RELEASED); // Add query criteria wuquery.setCriteria(
"[Title Block.Part Category] == 'Electrical'" +"and [Title Block.Description] contains 'Resistor'" +"union [Title Block.Description] contains '400'" +"and
[Title Block.Product Line(s)] contains 'Taurus'");// Run the query ITable results = wuquery.execute(); // Add code here to display the resultscatch (APIException ex) {System.out.println(ex);}}}
There are two ways to load a query:
Use the IAgileSession.getObject()
method to specify the full path of a query.
Use the IFolder.getChild()
method to specify the location of a query relative to a folder.
The following example shows how to load a query by specifying its full path.
Example 3-30 Loading a query using IAgileSession.getObject()
try {//Load the "Changes Submitted to Me" queryIQuery query =(IQuery)m_session.getObject(IQuery.OBJECT_TYPE,"/Workflow Routings/Changes Submitted To Me")
;} catch (APIException ex) {
System.out.println(ex);
}
The following example loads a query using IFolder.getChild()
.
Example 3-31 Loading a query using IFolder.getChild()
try {//Get the Workflow Routings folder IFolder folder =(IFolder)m_session.getObject(IFolder.OBJECT_TYPE, "/Workflow Routings");//Load the "Changes Submitted to Me" query IQuery query = (IQuery)folder.getChild("Changes Submitted To Me");
}
catch (APIException ex) { System.out.println(ex);}
To delete a saved query, use the IQuery.delete()
method. Temporary queries, that is, queries that are created but not saved to a folder are automatically deleted after the user session is closed. For lengthy sessions, you can use the delete()method
to explicitly delete a temporary query after you're finished running it.
The Simple Query dialog box enables the user to specify an item number as the search criteria. When you click the Find button, the program constructs a query to find all items that contain the specified text in the Item# (Item number) field. This example shows the code that runs the query when the user clicks the Find button.
Example 3-33 A simple Query code
void btnFind_actionPerformed(ActionEvent e) { try { // Create the queryIQuery query =(IQuery)m_session.createObject(IQuery.OBJECT_TYPE,ItemConstants.CLASS_ITEM_BASE_CLASS);
// Turn off case-sensitivity query.setCaseSensitive(false)
; // Specify the criteria dataquery.setCriteria("[Title Block.Number] contains (%0)",new String[] { this.txtItemNum.getText().toString() });// Run the queryITable queryResults = query.execute();Iterator i = queryResults.iterator();// If there are no matching items, display an error message.if (!i.hasNext()) {JOptionPane.showMessageDialog(null, "No matching items.", "Error",JOptionPane.ERROR_MESSAGE);return;}// Define arrays for the table datafinal String[] names = {"Item Number", "Item Description"};final Object[][] data = new Object[resultCount][names.length];int j = 0;while (i.hasNext()) {IRow row = (IRow)i.next();data[j][0] =row.getValue(ItemConstants.ATT_TITLE_BLOCK_NUMBER).toString();data[j][1] =row.getValue(ItemConstants.ATT_TITLE_BLOCK_DESCRITPION).toString();j++;}catch (APIException ex) {System.out.println(ex);}// Create a table modelTableModel newDataModel = new AbstractTableModel() {// Add code here to implement the table model};// Populate the table with data from the table model myTable.setModel(newDataModel); }