The SEARCH operation lets you search objects in the SmartOffice database by constructing SQL-like queries.
In your XML requests, use the
The <search> element contains the following child elements, which are used to construct search requests:
Child Element | Description | |
---|---|---|
<object> | This element specifies the object type to be searched (e.g., Contact) and the properties of that object type that will be returned in the response. If no properties are specified, the server returns a set of default properties. | |
<condition> | This element contains your search conditions. | |
<sort> | This element defines how search results should be sorted in the response. |
These child elements are described in detail in the following sections.
The following example shows a basic search request that does the following:
<request version='1.0'> <header> <office>myoffice</office> <user>jdoe</user> <password>password</password> </header> <search> <object> <Contact> <LastName/> <FirstName/> </Contact> </object> <condition> <expr prop='LastName' op='starts'> <v>A</v> </expr> </condition> <sort> <item prop='FirstName' order='asc'/> </sort> </search> </request>
The <object> element specifies the object type to be searched. It also defines a template that specifies the object properties that will be returned in the XML response.
In the following example, the <object> element specifies that the search will be limited to Contact objects. For each record matching the query conditions, the response should contain the contact's first name, last name and preferred phone number.
<object> <Contact> <LastName/> <FirstName/> <PreferredPhone> <PhoneNumber/> </PreferredPhone> </Contact> </object>
The <search> element must contain a <condition> child element that specifies the conditions of the search query. Conditions are defined in an XML structure that borrows from SQL database query syntax.
The <condition> element must contain at least one <expr> child element that defines the expression.
For example, the following <condition> element contains an expression (expr) that looks for Contact objects whose LastName property (prop) begins with "A." The expression employs an SQL-style LIKE operator (op) and A% wildcard pattern value (v).
<condition> <expr prop="Contact.LastName" op="like"> <v>A%</v> </expr> </condition>
Note the following regarding the <condition> element:
To understand how multiple <condition> and <expr> elements can be used, refer to the following example. The main <condition> element contains a child <condition> element, which in turn contains its own <expr> elements. The resulting search request returns all contacts whose first names start with A and whose last names start with either A or B.
<request version='1.0'> <header> <office>myoffice</office> <user>jdoe</user> <password>password</password> </header> <search> <object> <Contact> <LastName/> <FirstName/> </Contact> </object> <condition> <expr prop='FirstName' op='starts'> <v>A</v> </expr> <and/> <condition> <expr prop='LastName' op='starts'> <v>A</v> </expr> <or/> <expr prop='LastName' op='starts'> <v>B</v> </expr> </condition> </condition> </search> </request>
An expr node defines a minimal Boolean expression. The left side is always a property, but the right side can have one or more constant values or a property.
Left side information is defined by the attributes of the expr node.
Attributes of expr:
Name | Description |
---|---|
prop | Left side property name. [object].[property].<sub-property> Example: Contact.Type Contact.PreferredPhone.AreaCode |
op | operators |
fn | function name |
“val” The “v” node defines the right side information.
For example:
A A
Attributes of “val” “v”
Name | Description |
---|---|
type | Type of value. prop property name; const constant value. ref reference to current objects property value. Default value is const. ref type normally used for sync method. |
fn | Function name. |
The value type must be const for an operator: between, in, like, starts, ends and contain.
No “val” “v” node should exist for the operator that is “null” and “not null.”
The ref type of value indicates that the value is a parameter and it comes from the specified property of the current object.
Operators include: eq, gt, ge, lt, le, not eq, between, not between, contain, not contain, like, not like, in, not in.
Operator | Description |
---|---|
eq | Equal, = |
gt | Greater then, > |
ge | Greater than or equal to, >= |
lt | Less then, < |
le | Less than or equal to, <= |
ne | Not equal, <> |
between | Between |
like | Like |
contains | Contains |
starts | Starts with |
ends | Ends with |
in | In |
isnull | Is null |
not-between | Not between |
not-in | Not in |
not-null | Is not null |
not-like | Not like |
not-contains | Not contains |
not-starts | Not starts |
not-ends | Not ends |
any | Any of the child object matches with given data. (Only supported by the sync operations logic_step condition.) |
between
Between two values. It must be an integer type.
Sample:
<expr prop="Contact.Type" op="between"> <v>1</v> <v>10</v> </expr>
like
The client is responsible for escaping the string value.
Sample:
<condition> <expr prop="Contact.Name" op="like"> <v>A%</v> </expr> </condition>
contain
A means of testing if a string property contains a specified string. Special characters should not be used in a string value.
Sample:
<condition> <expr prop="Contact.Name" op="contain"> <v>abc</v> </expr> </condition>
The SQL query is Name like %abc%.
starts
A means of testing if a string property starts with a specified string.
Sample:
<condition> <expr prop="Contact.Name" op="starts"> <v>A</v> </expr> </condition>
The SQL query is Name like A%.
ends
A means of testing if a string property ends with a specified string.
Sample:
<condition> <expr prop="Contact.Name" op="ends"> <v>A</v> </expr> </condition>
The SQL query is Name like %A.
in
A means of testing if the property is from a list of values. Only simple type values are allowed.
Sample:
<condition> <expr prop="Contact.Type" op="in"> <v>1</v> <v>2</v> <v>3</v> </expr> </condition>
The SQL query is Type in (1,2,3).
Null
A means of testing if the property is null.
Sample:
<condition> <expr prop="Contact.Name" op="null"/> </condition>
The SQL query is Name is null.
not
Not can be used with: eq, like, contain, starts, ends, in and null operators.
<condition> <expr prop="Contact.Name" op="not-null"/> </condition>
SmartIntegrator supports only the SQL functions that are also supported by the SmartOffice framework. The query object in the base system must support these functions. Functions can only be applied to the properties.
Function Name | Description |
---|---|
YEAR | Year |
MONTH | Month |
DAY | Day of month, 1 31 |
HOUR | Hour |
MINUTE | Minute |
SECOND | Second |
QUARTER | Quarter of year |
WEEKOFYEAR | Week of year |
DAYOFYEAR | Day of year |
DAYOFWEEK | Day of week |
UCASE | Upper case |
LCASE | Lower case |
Sample:
All contacts with a birthday in March:
3 3
Searchable properties of child objects can also be used in the condition. Relationships must be defined by the Sub-Type and foreign keys.
Sample: All contacts with a ZIP Code (Postal) of 91106.
91106 91106
Attribute | Description |
---|---|
searchid | Specifies the unique id of the search. The searchid will be necessary when the search results are on multiple pages. Please see pagination for more details. |
pagesize | Number of objects listed on each page. The default page size is 2000 records. |
page | If the results are greater than the pagesize, then this attribute will list the current page number to note where you are. |
total | Total number of records retrieved in the response. |
more | Are there more records to be retrieved? Specifies whether there are more records to be retrieved. This attribute can have the following values:
|
Sample:
Example 1: Lists all contacts with a last name that starts with R.
Request XML
<request version='1.0'> <header> <office/> <user/> <password/> </header> <search> <object> <Contact> <LastName/> <FirstName/> </Contact> </object> <condition> <expr prop='LastName' op='starts'> <v>R</v> </expr> </condition> </search> </request>
Response XML
<response version="1.0"> <header> <sessionClosed/> </header> <search page="" more="false" pagesize="2000" total="1" searchid=""> <Contact _type="obj" id="Contact.1.25"> <LastName>Ryan</LastName> <FirstName>Joseph</FirstName> </Contact> </search> <_status>OK</_status> </response>
In the case of a large amount of data, the SmartIntegrator server provides the ability to produce paginated data returned within SEARCH results. Using the Search IDs generated after the first search, a client application can retrieve a selected elements searchid attribute from the response XML.
To retrieve other pages, the searchid and requested page need to be specified in the request XML. Not specifying a page or leaving it blank defaults to retrieving the next page. Clients will not know the total number of records until the last page (more="false").
keepsession is also required.
Sample:
Request XML
<request version='1.0'> <header> <office/> <user/> <password/> <keepsession>true</keepsession> </header> <search pagesize="5"> <object> <Contact> <LastName/> <FirstName/> </Contact> </object> <condition> <expr prop='LastName' op='starts'> <v>Ac</v> </expr> </condition> </search> </request>
Response XML
<response version="1.0"> <header/> <search page="0" more="true" pagesize="5" total="5" searchid="NQ=="> <Contact _type="obj" id="Contact.1.21"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> <Contact _type="obj" id="Contact.1.22"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> <Contact _type="obj" id="Contact.1.23"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> <Contact _type="obj" id="Contact.1.24"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> <Contact _type="obj" id="Contact.1.26"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> </search> <_status>OK</_status> <!-- Cost 21 mill seconds.--> </response>
Request XML
<request version='1.0'> <header/> <search searchid="NQ==" pagesize="5"> <object> <Contact> <LastName/> <FirstName/> </Contact> </object> <condition> <expr prop='LastName' op='starts'> <v>Ac</v> </expr> </condition> </search> </request>
Response XML
<response version="1.0"> <header/> <search page="-1" more="false" pagesize="5" total="5" searchid="NQ=="> <Contact _type="obj" id="Contact.1.27"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> <Contact _type="obj" id="Contact.1.29"> <LastName>Ackerman</LastName> <FirstName>Kathleen</FirstName> </Contact> <Contact _type="obj" id="Contact.1.31"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> <Contact _type="obj" id="Contact.1.34"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> <Contact _type="obj" id="Contact.1.35"> <LastName>Ackerman</LastName> <FirstName>Joseph</FirstName> </Contact> </search> <_status>OK</_status> <!-- Cost 10 mill seconds.--> </response>
Why does the SEARCH request with pagination sometimes terminate before bringing back all of the records?
SEARCH requests are extremely powerful in letting you simultaneously retrieve data across many linked objects from the data model while dynamically filtering that data on criteria for more than one of the objects at a time. When used in conjunction with the pagination feature, the SEARCH request can push the SmartOffice server to its resource limits. This most often results in SmartOffice flushing the server-side cache for your SmartIntegrator session, which appears as a premature end to the paginated results being requested.
There is no easy way to advise development partners on exactly what might cause this issue as it is dependent on which objects are defined in the body and criteria of the SEARCH, how many records from those tables are visible to the user, and what level of stress the SmartOffice server is currently under.
We manage this by using a paginated SEARCH request with our full criteria to only retrieve the object id for the top-level object, then using a GET for each object id to get the data we need. The paginated SEARCH result is scalable up to hundreds of thousands of records for almost all objects when the SEARCH criteria is kept simple. In scenarios where the desired SEARCH criteria is complex, the criteria should be removed or simplified and the filtering needs to happen on the client-side.
Sample SEARCH Request to retrieve the object ids:
<request version='1.0'>
<header>
<office/>
<user/>
<password/>
<KeepSession/>
</header>
<search total="true" pagesize="2000">
<object>
<Policy/>
</object>
</search>
</request>
Sample SEARCH Response
<response version="1.0">
<header/>
<search searchid="MQ==" total="11694" pagesize="2000" more="true" page="0">
<Policy id="Policy.70746.15" _type="obj"/>
<Policy id="Policy.70746.16" _type="obj"/>
<Policy id="Policy.70746.17" _type="obj"/>...
<Policy id="Policy.70746.11005" _type="obj"/>
</search>
<_status>OK</_status>
</response>
Sample GET Request using the object ids and retrieving details
<request version='1.0'>
<header>
<office/>
<user/>
<password/>
<KeepSession/>
</header>
<get>
<Policy id="Policy.70746.15">
<UniqueID/>
<PolicyStatus/>
<PolicyNumber/>
<Premium/>
<AnnualPremium/>
<IssuedDate/>
<CreatedOn/>
<ModifiedOn/>
<Carrier>
<Vendor/>
</Carrier>
<PrimaryAdvisor>
<Agent>
<AgencyID/>
<CustomData>
<ObjectIDVal/>
<AlphaNum10/>
</CustomData>
</Agent>
</PrimaryAdvisor>
<PendingCase>
<CashReceived/>
</PendingCase>
</Policy>...
<Policy id="Policy.70746.11005">
<UniqueID/>
<PolicyStatus/>
<PolicyNumber/>
<Premium/>
<AnnualPremium/>
<IssuedDate/>
<CreatedOn/>
<ModifiedOn/>
<Carrier>
<Vendor/>
</Carrier>
<PrimaryAdvisor>
<Agent>
<AgencyID/>
<CustomData>
<ObjectIDVal/>
<AlphaNum10/>
</CustomData>
</Agent>
</PrimaryAdvisor>
<PendingCase>
<CashReceived/>
</PendingCase>
</Policy>
</get>
</request>
The Sort option defines the sorting information. Only searchable properties can be sorted.
<sort> <item prop="AreaCode" order="asc"/> </sort>
The attribute prop is the name of a property but it is a short name and does not include an object name.
The attribute order can have only one of the two values: asc or desc. The default is asc.
This element specifies the object type to be searched (e.g., Contact) and the properties of that object type that should be returned in the response. If no properties are specified, the SmartIntegrator server returns a set of default properties.
Note the following regarding the <condition> element:
A <condition> element contains at least one <expr> element that defines a minimal Boolean expression. A <condition> element can contain multiple <expr> elements (see the next section for more about the <expr> element).
The elements <and> and <or> define relationships between expressions and conditions.
<condition> elements can be nested within other condition elements to create complex search queries.
The following example shows a SEARCH request containing nested conditions and expressions. The main <condition> element contains a child <condition> element, which in turn contains its own <expr> elements. The query returns all contacts whose first names start with A and whose last names start with either A or B.