SQL Queries in SearchBlox have some requirements for the search results to be displayed correctly.
- SQL Query must be given as a single line.
- Only one query can be given per collection.
- The fields can be mapped to the SearchBlox fields (but this is not mandatory). These mapped SearchBlox fields will help with customizing search templates and searching in SearchBlox. The following lists the fields within SearchBlox to which field values can be mapped.
- Applications supported by database collections (such as Salesforce or Gmail) should have the query words such as "SELECT", "FROM", etc., in capital letters.
Primary or unique field from a table has to be mapped to this field.
This is a mandatory field. Make sure to map a unique field to this field
If any field has to appear as a title in search results, it should be mapped as the title.
If any field has to appear in the description section, it should be mapped as the description.
A few example queries are listed in the section below.
The query below is the most simple query that can be given in Database Collection.
select *,<primary or unique field> as uid from <tablename>
Other Mapped Queries
If you need to map a field to the UID field in SearchBlox, always make sure that it is unique.
Note that the SQL query must be given as a single line. Do not enter any new line characters in between, as this could cause problems with indexing.
Example Query 1
Select *,<primary or unique field> as uid,<title field> as title from table
This query will help index all the fields in the table. After searching, the UID will appear as the title.
Example Query 2
Select <index field> as uid , <column name2> as title, <column name3> as content from table
Example Query 3
Select <index field> as uid,<column2> as title,<column3> as description,<column4> as keywords, <column5> as content,<column 6> from table
Example Query 4
Select <index field> as uid,<column2>, <column3>, <column4>, <column5> from table
The above queries will help index the mapped fields from the table. After searching, the field mapped as the title will appear as the title in the search results.
You can also provide queries for views in SearchBlox, which would index the data from views.
The following are reserved words in SearchBlox, and cannot be used in the query directly.
Example query for using reserved words
Select id as “did”, indexdate as “mydate”,url as “myurl” from table
While indexing queries in the Oracle database, it is important to be aware of the rules and standards required. For example, the following query will not work in Oracle:
Select *,<index field> as “uid” from table
Queries similar to the one below only work while indexing Oracle databases in SearchBlox. Also, double quotes must be provided for mapping fields while using Oracle.
Example Query 1:
Select <index field> as “uid”,<varchar field> as "title", <varchar field> as content from table
Oracle has some reserved words such as "uid". Also, the column names are stored in capital letters. Therefore, it is mandatory to specify the assigned name of columns in double quotes as in the above query. Most importantly, do not use a semicolon at the end of the query.