Specify the Query Parameters

Documents are built by selecting columns from ordered lists returned by synchronization queries.

To index your database, you can configure the connector to use:

  • A full synchronization mode and index the document corpus entirely.

  • Or one of the incremental synchronization modes to push only a fraction of your database. Use incremental modes when your table contains either:

    • A timestamp representing the time of insertion
    • A unique serial number generated with AUTO_INCREMENT

During incremental synchronization, the connector fetches the current timestamp or serial number of the column, and populates a variable in the incremental query to retrieve new rows from the database.

For more information, see Implementing Synchronization in the Exalead CloudView Connector Programmer's Guide

This task shows you how to:

See Also
Examples of JDBC Database Connector Configurations

Step 1: Select a Synchronization Mode

The JDBC Database connector uses synchronization modes based on different indexing methods. You must first select one of them.

Mode

Description

Full synchronization

Pushes every document contained in the database when a Full Scan occurs (see Controlling Connectors).

This mode does not perform incremental synchronization.

Column-based incremental synchronization

This incremental synchronization mode performs:

  • a first full synchronization as described in the Full synchronization mode
  • and calculates the maximum values contained in the Checkpoint column. Supported SQL types for the maximum calculation are TINYINT, SMALLINT, INTEGER, BIGINT, TIMESTAMP, and DATE.

This mode performs subsequent synchronizations as follows:

  • The Incremental variable is populated in the Incremental query with the maximum value and used for the synchronization.
  • A new maximum value is calculated.

Query-based incremental synchronization

This mode is similar to the Column-based incremental mode except that is based on a query instead of a column checkpoint. A query, run before each synchronization, calculates the maximum value.

Trigger-based incremental synchronization

You can configure additional queries to maintain tables that keep track of added, updated, and deleted rows. Use these queries to set up triggers and maintain additional tables.

For large databases (> 1.000.000 entries), when indexing is not automatically run after a completed scan (Force indexing after scan option cleared), plan enough time between the initial scan and the first incremental scan to make sure that checkpoints are computed properly. Alternatively, you can click Force commit (Administration Console > Home > Indexing section) to make sure that all documents have been indexed before running your first incremental scan.

Step 2a: Define the Main Parameters

The other parameters for the database queries are based on the selected synchronization mode. The required and optional parameters of each mode are described in the following procedures.

Important: The JDBC connector does not insert quotes automatically when populating values in incremental and fetch queries. This means that you must surround variables by quotes for dates and strings.

A typical incremental query using an integer column to select new documents: SELECT * FROM table WHERE id_column > $(id) becomes, for example, SELECT * FROM table WHERE id_column > 42.

A typical incremental query using a stamp column to select new documents: SELECT * FROM table where stamp_column > {ts '$(stamp)'}. Once again, use quotes here.

A typical fetch query using a varchar column: SELECT * FROM table where id_column = '$(id_column)' becomes, for example, SELECT * FROM table where id_column = 'FRA0001' . If you omit quotes, the query would be: SELECT * FROM table where id_column = FRA0001, which is not a valid SQL query.

Important: For Query-based and column-based incremental synchronization, you can define an incremental variable to populate the incremental query:
  • Either by a verbatim string substitution (default option),
  • Or by a driver variable substitution, which allows you to apply the same SQL value on different RDBMS.

To edit this option, go to the Advanced tab and configure the Checkpoint replacement mode property as required.

For the Full Synchronization Mode

  1. Specify an Initial query to synchronize the entire document corpus. For example: SELECT * FROM table
  2. Optionally, specify an All URIs query or a Fetch query.

For the Query-Based Incremental Synchronization Mode

  1. Specify an Initial query to synchronize the entire document corpus. For example: SELECT * FROM table
  2. Specify a Checkpoint query to fetch the value of the checkpoint stored in the Incremental variable. For example: SELECT MAX(stamp_column) FROM table or SELECT NOW()
  3. Specify an Incremental variable that is to say, the variable name to populate in the Incremental query (contains the value of the Checkpoint query). For example: STAMP
  4. Specify an Incremental query to perform incremental updates. For example: SELECT * FROM table WHERE stamp_column > '$(STAMP)'
  5. Optionally, specify an All URIs query and/or a Fetch query.

For the Column-Based Incremental Synchronization Mode

  1. Specify an Initial query to synchronize the entire document corpus. For example: SELECT * FROM table
  2. Specify a Checkpoint column, that is to say the name of the column stored in the Incremental variable used with column-based incremental synchronization. For example: stamp_column
  3. Specify an Incremental variable that is to say, the variable name to populate in the Incremental query (contains the value of the Checkpoint column). For example: STAMP
  4. Specify an Incremental query to perform incremental updates. For example:SELECT * FROM table WHERE stamp_column > '$(STAMP)'

    The connector will populate ' $(STAMP)' with the maximum time stamp found in the column and use the query. For example: SELECT * FROM table WHERE stamp_column > '2011-11-25 11:25:00'

  5. Optionally, specify an All URIs query and/or a Fetch query.

For the Trigger-Based Incremental Synchronization Mode

  1. Specify a Checkpoint query to fetch the value of the checkpoint used as Incremental Start Variable.
  2. Specify an Incremental Start Variable that is to say, the variable name that will be used to identify the next scan run.
  3. Specify an Incremental Variable that is to say, the variable name that will be used to identify the last scan run.
  4. Specify an Initial Query to synchronize the entire document corpus. For example: SELECT * FROM table
  5. Optionally, for Initial, specify:
    • In Initial Pre Queries: The queries to run before the first synchronization.
    • In Initial Post Queries: The queries to run after the first synchronization. For example: CREATE TABLE eventTable(id INT, action VARCHAR(1)) CREATE TRIGGER after_test_insert AFTER INSERT ON testTable REFERENCING NEW AS NEW FOR EACH ROW INSERT INTO eventTable VALUES (NEW.id, 'N')
  6. Specify an Incremental query to perform incremental updates. For example: SELECT * FROM table WHERE id IN (SELECT id FROM eventTable WHERE action = 'N')
  7. Optionally, for Incremental, specify:
    • In Incremental Delete Queries: The queries to run to compute documents that will be deleted during incremental synchronizations. For example: SELECT * FROM eventTable WHERE action = 'D'
    • In Incremental Pre Queries: The queries to run before incremental synchronizations.
    • In Incremental Post Queries: The queries to run after incremental synchronizations. For example: DELETE FROM eventTable WHERE action = 'N'
  8. Optionally, specify an All URIs query.
    • In All URIs pre queries: The queries to run before the deletion process.
    • In All URIs post queries: The queries to run after the deletion process.
  9. Optionally, specify a Fetch query.

Step 2b: Define Optional SQL Queries

The following table describes the optional queries that can be defined for each synchronization mode.

Query

Description

All URIs query

The query used to list all document URIs in the database and compare them with those in the index.

You can use it to detect entries deleted from the database, and reflect these deletions in the index. Write it to go through the primary keys of all the tables crawled by Exalead CloudView.

For example:

SELECT products.productid,attributes.langid FROM products,attributes WHERE products.productid = attributes.productid

... selects all the rows corresponding to the union of the two primary keys productid and attrid.

This query allows Exalead CloudView to quickly mirror deleted rows from the database.

Note: The field names that make up must be the same as the names returned by the main query.

This query does not perform any accumulation. When a record belonging to an accumulated document is removed from the database, the document is left unchanged in the index.

Fetch query

The query used to fetch documents for previews. The preview lists the selected columns of every row returned by the query.

This query does not perform any accumulation.

Mapping names prefixed by a dollar sign ($) are populated with the corresponding values contained in the document URI.

For example, given an indexed document with the URI: customerID=550& and a Fetch query:

SELECT * FROM customers WHERE customerID = $(customerID)

The connector processes the query as follows:

SELECT * FROM customers WHERE customerID = 550

and uses this query to fetch the required document.

Step 3: Define Advanced Parameters

In More options, you configure the following optional advanced parameters.

Field

Description

Auto commit

In trigger-based synchronization, select the auto commit mode on connection:

  • true: the database makes the commit
  • false: the JDBC connector makes the commit

Read only

Specifies if the connection to the database is read-only.

Options: true|false|<empty>

Query timeout

Specifies the query execution timeout in seconds.

Options: <int>|<empty>

Max. field size

Specifies the maximum number of bytes to return for character and binary column values in a result set.

Options: <int>|<empty>

Max. rows in RAM

Specifies how many rows a result set can contain at a time. When exceeding the maximum number, rows are dropped and not loaded in the result set. Your JDBC driver may not support that.

Verbose

Enables the debug mode in logs for the connector only.

Fetcher class

Specifies a custom fetcher class (java code) to fetch documents. Enter the class name.

Custom classes must be in a jar file located in the connector directory: <DATADIR>/resource/all-arch/builtin_plugins_deployed/jdbc-java-connector

Recommendation: Use the following settings when the database contains many files or text parts or if you encounter OutOfMemory errors:

  • Auto commit: false

  • Read only: true

  • Max rows in RAM: lower the default value (5000)

  • Result set type: FORWARD_ONLY

  • Result set concurrency: READ_ONLY