Database Polling Device

The Database Polling device reads records from a database and converts each record into a message. It uses ADO.NET providers for database access, so you may need to download and install a provider depending on your database type.

On the Connection tab, select the provider and input the required connection settings. In this sample, we’re using the Npgsql Postgres provider.

Use the Options tab to configure the behavior of the device. The Polling Interval (in seconds) determines the period between each database poll. This can be scheduled, so polling only operates during specific periods.

The Sleep Interval is the period to wait when retrieving the next batch of messages, when there are more records to retrieve. This setting lets you throttle how quickly data is returned from the database (and reduce the database load).

The Processing Behavior can be set to:

Run Until Empty: The Select query is run continuously until no records are returned.
Run Once: The Select query is run once per database poll.

The Error Behavior lets you retry after an error immediately (Retry), or, wait until the next database poll (Retry Next Schedule).

The Command Timeout specifies how long the Select query can run before returning data, before a Timeout exception is thrown.

There are two settings which determine how database records are converted to messages. The Output Format setting can be:

Data Table: Record(s) returned from the database are converted to a System.Data.DataTable object. This is a common format which can easily be consumed by Custom Code or Custom Devices.
HL7, XML, JSON: If you are returning a single field from the database, and the field is HL7, XML, or JSON.

The Output Mode determines if each record will be a message (Message Per Row), or, if multiple records (a batch) will be stored as a single message (Message Per Batch).

Select Operation

The SQL which will be used to pull data from the database. This is typically a SELECT statement against a table or view in your database. You can also execute a stored procedure which returns rows. This SQL must include a Query Field.

Query Field

In order to keep track of the current position within your table/view/stored procedure, a Query Field is used. The Query Field is part of your WHERE clause and typically operates against a numeric ID, Date/Time, or RowVersion. The Query Field is often the table's primary key. For example, in the query SELECT Top 1 * FROM Events WHERE EventId > @EventId, EventId is the Query Field. A parameter in the form of @XXXXX is expected after your Query Field. In the above query, @EventId is the parameter. Connexion tracks the Query Field value returned by your query and injects this value into the parameter. For example, if the above query returned an EventId value of 6, then 6 would be injected into the @EventId parameter the next time this query is executed. This ensures a forward-only cursor which prevents rows from being read multiple times.

You can use the Generate SQL link to launch a SQL generation wizard.

Select the table, view, or stored procedure you want to query, and click the Next button. Then select the columns you want to return and click the Next button.

Then select the Query Field which will be used to track the position within the table. This is usually a sequential ID, DateTime, or RowVersion.

The SQL will then be generated and displayed.

Note that the generated SQL is targeted for Sql Server. Since we’re targeting Postgres, we need to replace the TOP X statement with LIMIT X and remove the square brackets from each field name.

Also note that the number of records returned by each query call is determined by the SQL itself - with either a TOP or LIMIT keyword. If you want one record per message, you can change this setting to a reasonable size (10 for large payloads and 100 for smaller payloads, for example). If you want multiple records per message, then set the TOP/LIMIT to the desired number of records per message.

Before you can run the device, you must manually set a seed value. To do this, update the Set Seed Value field and click the save button. This is a safety measure to ensure unwanted data isn’t accidentally ingested.

If a seed value isn’t set, you’ll get the following error:

No initial Seed value returned from query. Please manually set a valid Seed value in the Database Polling Device UI.

You should also verify that the Column and Variable match the values from the query. If not, you can click on each field to edit.

Update Operation (Optional)

You can optionally execute an Update operation after each Select operation. For example, if you have a status or state field, it may have a value of 'U' (for Unprocessed). Once a record has been placed onto a queue, you may wish to update this field to 'P' (for Processed). This is typically an UPDATE statement against the table/view/stored procedure from which the data was read.

If you are updating your database after each record is successfully stored on a queue (After Every Message), then your update SQL should contain the same parameter used in the query SQL. If your query SQL is SELECT Top 1 * FROM Events WHERE EventId > @EventId, then the update SQL should have the following: WHERE EventId = @EventId.

If you are updating your database after each batch of records is successfully stored on a queue (After Batch), then your update SQL should contain the @first and @last parameters. If your query SQL is SELECT Top 10 * FROM Events WHERE EventId > @EventId, then the update SQL should have the range update: WHERE EventId >= @first AND EventId <= @last.

Note that when updating a batch, instead of using the parameter in the query SQL (@EventId), you use @first and @last.

In the below example, we’re updating the Ingested field to 1 to indicate the message has been ingested into Connexion.

Seed Operation (Optional)

When the seed value is null or empty (typically when this device is newly created), you can optionally specify a query to set the initial seed value. By default, the seed value starts at 0 (or equivalent) and is updated after each query. You can also manually set the seed value in the 'SELECT Operation' tab.

The seed SQL is usually a SELECT TOP 1 [DiscriminatorField] WHERE XXX ORDER BY [DiscriminatorField] DESC. In the example below, we’re finding the highest message_id with the Ingested field set to 0 (not ingested).

Viewing Messages

If your device is configured to post DataTable messages, they are displayed as JSON in the message viewers. The DataTable type has many features should you need to work with it in code.

Event Hooks

This device exposes two event hooks. The BeforeExecuteDbCommand hook exposes the DbCommand object, allowing you to modify the query string and parameters prior to execution. The AfterResultsReceived hook is fired immediately after receiving the query results (as a data table). This lets you interact with the raw query results.