skip to main content
Formulating queries : Searching text-based columns
 

Try DataDirect Cloud Now

Searching text-based columns

Different data store types support different levels of indexing and searching. Indexing increases the efficiency of searches in tables with many records. Querying to find particular values can be expensive when the search must span many columns and many records. To improve performance, you can restrict searches to particular text-based columns using the DataDirect Cloud proprietary query parameter, ddSearch. To search across all columns in the schema, even those not enabled in the schema map for searching, you can use OData $filter. But, you cannot combine ddSearch and $filter in the same request.
This release supports use of ddSearch for all data store types, and full-text search taking advantages of indexes in the following data source types:
*DB2 on Linux, UNIX, and Windows — Each column to be searched must have a separate full text index, the full text services must be running, and the database must be enabled for full text. See the DB2 documentation for more information.
*Oracle — Each column to be searched must have a separate full text index, the full text services must be running, and the database must be enabled for full text. See the Oracle documentation for more information.
*Microsoft SQL Server — Each column to be searched must have a separate full text index and the full text index engine must be running. See the Microsoft documentation for more information.
To use text search:
*For data stores that support full-text search, make sure that the underlying data store is indexed and is up to date with the current schema.
*For Salesforce data stores that access external objects, follow the steps described in Configuring Salesforce external objects for search optimization.
*Enable search for the indexed columns in the DataDirect Cloud Data Source schema map, as described in Configuring an OData schema map and selecting Full text as the search type.
*Use the ddsearch parameter with a search string, as described below.
DataDirect Cloud treats multiple terms by using a logical and. For example, a search for Sales & Marketing returns records that contain both the word Sales and the word Marketing, the ampersand is ignored. The case-sensitivity of the search string depends on the underlying data source.
The ddsearch parameter will either return an empty response or an error in the following circumstances:
*If the schema map does not specify the table as searchable.
*If the table does not contain searchable fields.
*If searching is not enabled in the backend data store.
*For Salesforce, if you have not enabled use of ddSearch as a custom query parameter.
The following example returns a list of records containing the string "TX" from an ACCOUNT table: https://service.datadirectcloud.com/api/odata/DDCdemo/ACCOUNTS?ddsearch=TX
* Configuring Salesforce external objects for search optimization