Database Polling

A Database communication point running in input mode essentially watches one main table. Every time new rows are inserted into this table, the database communication point will retrieve these rows and return the information contained in them as an XML message. Retrieval of these rows is the responsibility of the root SQL statement.

The input database polling options are used only for input Database communication points. These properties determine how frequently the communication polls the database, and how it detects new rows. Click the Database Polling icon at the top left of the Database Configuration Editor to display the Database Polling Options dialog:

The following table describes the fields on this dialog:

Database Polling Using a Key Column

A Key column for the main table is used by the root statement to order and restrict the rows returned from the main table. In addition, the queries it runs must be static; that is, they cannot be parameterized based on values from a message.

The root SQL statement must always have the following general form:

SELECT col_1, col_2, ...
FROM the_main_table
WHERE some_other_conditions
AND key_column > @key_column
ORDER BY key_column ASC

Or

SELECT col_1, col_2, ...
FROM the_main_table
WHERE some_other_conditions
AND key_column < @key_column
ORDER BY key_column DESC

The set of columns returned (col_1, col_2, ...) must contain the Key column so that it can be stored and used for the next query. The current value of the Key column is stored internally within Rhapsody after each successful query, and is unique for each communication point. This means the value is saved and if Rhapsody is restarted, the Database communication point will pick up where it left off.

The first time the Database communication point is run, no value will exist within Rhapsody for the key value. In this case, the initial key value property must be used to set an initial value for the property. If a value exists in the Rhapsody database for the key, the initial key value property is ignored. The SQL for the root statement must return a column with the name of the key column and should have appropriate where and order by clauses referring to the key column to prevent the same row being returned more than once.

Database Polling Without a Key Column

When not using a key value, a Key column is specified in the configuration, but the script must update the row so that it does not get read again.

 Select id, patientid, name, etc... from patient where state = 'new' 

 UPDATE Patient SET state = 'inprogress' WHERE id = @id 

Refresh Rate

The refresh rate is only invoked if no new data is returned by the input query. If the SQL query is returning data, the query is called continuously until no new data is found. That is, if the query is always returning data, the database communication point is continuously polling the database. This can cause the database to spin and makes the communication point difficult to stop.

For example, the following query:

 SELECT patientid, name from updates;

will be called continuously until something deletes all the data in the table.

Alternatively, the following query can be used:

SELECT TOP 1 id, patientid, name FROM updates WHERE state = 'new'

with the nested statement:

UPDATE updates SET state = 'inprogress' WHERE id = @id

or the following query by date:

SELECT patientid, name, Now() As RC07NowTime FROM updates WHERE (Now() > (DateAdd('s', 60, @RC07NowTime)))