Database Collection

SearchBlox allows you to easily index records from the database table, and results are customizable with Faceted Search.

Creating a Database Collection

You can Create a Database Collection with the following steps:

• After logging in to the Admin Console, select the Collections tab and click on Create a New Collection or "+" icon.
• Choose Database Collection as Collection Type.
• Enter a unique name for your collection (for example, Databasecollection).
• Choose Private/Public Collection Access and Collection Encryption as per the requirements.
• Choose the language of the content (if the language is other than English).
• Click Save to create the collection.

• Once the DB collection is created you will be taken to the Database Settings tab.

Supported Databases

SearchBlox databases for database collection indexing include:

• MySQL
• SQL Server
• Oracle
• PostgreSQL
• Other Applications supported through Database Collection

Database Settings

• Database setting values must be set explicitly for Database collections.
• The mandatory settings for Database collection for databases like MySQL, Oracle, etc., are
  • Database Type
  • Host IP/Name
  • Host Port No
  • Database Name
  • Database Username
  • Database Password
  • SQL Query
• The mandatory settings for Database collection for External DB Connectors are
  • Database Type
  • Database URL String
  • SQL Query
• SearchBlox comes pre-configured with parameters when a new collection is created, but you can also select your preferred setting values for database collections.
• The following table has the list of settings available in Database collection

🚧

NOTE:

• While providing a SQL Query in Database Settings page, make sure to provide the primary key as uid else while reindexing/scheduled indexing duplicate documents gets added.
• If you notice document missing while scheduled index/reindex, please increase the SQL Settings-Fetch Size, which you will find under Settings page as shown in the following screenshots.

After saving the Database settings, test the connection. The connection has to be successful for
indexing to take place.

Schedule and Index

Sets the frequency and the start date/time for indexing a collection. Schedule Frequency supported in SearchBlox is as follows:

• Once
• Hourly
• Daily
• Every 48 Hours
• Every 96 Hours
• Weekly
• Monthly

The following operations can be performed in Database collections.

Data Fields Tab

Using Data Fields tab we can create custom fields for search and we can see the Default Data Fields with non-encrypted collection. SearchBlox supports 4 types of Data Fields as listed below:

Keyword
Number
Date
Text

• Once the Data fields are configured, collection must be cleared and re-indexed to take effect.

To know more about Data Fields please refer to Data Fields Tab

👍

Best Practices

• Please check the type of application/database that is to be indexed and provide the database settings accordingly.
• If you do not have any unique id to map in SQL SELECT query, create a view with a unique id and use the same in the SELECT query to fetch the data.
• If you have multiple collections, always schedule the activity to prevent more than 2-3 collections indexing at the same time.

Viewing Search Results

• View the search results by searching for the records here: https://localhost:8443/search/index.html.
• After clicking the search results, the data will appear in a grid format.

If you want search results in the grid view, use SearchBlox's Database plugin. Learn more about Database Search