Version 22.0.8486


Version 22.0.8486

The CData Connect API supports the OData Search functionality. Search, is supported with all data-sources using the $search URL parameter:


Enabling Search for Specific Columns

Search is enabled by default for all string columns. It can be restricted to specific columns if necessary. In the schema for a resource, add other:search="true" for each column definition to include the column in searches. Other string columns will be assumed to be non-searchable.

<api:info title="Cars" desc="Access the Cars data through REST APIs." connection="SQLiteCars">
  <attr name="ID"   key="true" xs:type="int"  />
  <attr name="Make"        xs:type="string"   other:search="true" />
  <attr name="Model"       xs:type="string"   other:search="true" />
  <attr name="Trim"        xs:type="string"   />
  <attr name="Color"       xs:type="string"   />
  <attr name="Cost"        xs:type="double"   />
  <attr name="CreatedDate" xs:type="datetime" />
  <attr name="InStock"     xs:type="boolean"  />

This request results in the following SQL query being passed to the underlying connector:

SELECT Make, Model, Trim, Color FROM Cars WHERE (Make LIKE "%Peugeot%") OR (Model LIKE "%Peugeot%")

To search for an exact expression that includes spaces, enclose the expression with double quotes. The following expression will search for records that match the exact phrase “Mini Cooper”:

https://myconnectserver/odata.rsc/Cars?$search="Mini Cooper"

Using Logical Operators

The $search functionality of the CData Connect API supports the following operators:

Operator Description Sample OData
NOT Returns records that do not match the term odata.rsc/Cars?$search=NOT Peugeot
AND Returns records that match both terms odata.rsc/Cars?$search=Peugeot AND Renault
OR Returns records that match either term odata.rsc/Cars?$search=Peugeot OR Renault

Logical operators with multiple search expressions can be combined and ordered using parentheses. Note that in accordance with OData standards, specifying two search terms separated by a space and not surrounded by quotes is equivalent to using the AND operator.