Skip to content

db.incremental_sql_query

The SQL query to perform to fetch new or changed records from a database.

Key: db.incremental_sql_query
Type: String
Can be set in: collection.cfg

Description

This parameter specifies the SQL query to execute when performing an incremental SQL database collection update. The syntax of the SQL query must conform to that required by the underlying database.

The query is expected to contain a column name matching the db.primary_id_column value and this column's value will be used to identify the record. If a new record has the same primary id column value as an existing one, the existing record will be replaced.

Each row resulting from the SQL query will be indexed, and the column names will be used as XML element names to allow metadata mapping configuration to control the indexing.

Please note that with most database drivers, a semicolon (;) should not be included at the end of the query.

The incremental query may contain two token values while will be automatically substituted when the query is executed.

  • $last_max_key will contain the largest primary key value seen since the last full gather.
  • $last_update_time will contain the time of the last collection update, in the form yyyy-MM-dd HH:mm:ss

Use of these token values allows your collection to be configured to fetch only new database records when performing an incremental update.

Default Value

(none)

Examples

db.incremental_sql_query=SELECT id, summary, body FROM pages WHERE search_index = 'true' AND id > $last_max_key
db.incremental_sql_query=SELECT id, summary, body FROM pages WHERE last_modified > $last_update_time

See Also

top

Funnelback logo
v15.24.0