Poll from DB – Coral8 [7] – Damir Systems Inc.

I read somewhere that — when it comes to programming — being lazy is good. Hence, I will reuse as much code as possible from previous posts. The objective for today is to use Coral8 Poll from Database Input Adapter.

First, let us look at the database (same as here):

CREATE TABLE dbo.GpsData(
	Entity varchar(25) NOT NULL
	,Latitude float
	,Longitude float
	,Course float
	,Speed float
	,TimeUTC datetime NOT NULL
)

A caveat; the SQL server DATETIME type is not time zone aware, while the Coral8 TIMESTAMP is. If the time zone is not specified, Coral8 assumes UTC. In general, this is not a problem; however, stream viewers always display time in local format, so for me (Toronto, EST) all database time-stamps are shifted by five hours. One way to remedy the problem is to use the Timezone parameter in the service specification, like this:

<!-- DB Time Zone -->
<param name="Timezone">EST</param>

The code has to be added to the coral8-services.xml file; here is the whole section for the database used:

<!-- DamirTest on DAMIR5 -->
<service name="DamirTest" type="DATABASE">
  <description>Connection to DamirTest</description>
  <param name="DBDriverType">DBDriverODBC</param>
  <!-- DBDriverConnectString = DSN name -->
  <param name="DBDriverConnectString" >DamirTest</param>
  <param name="DBReadNoCommit">True</param>
  <!-- Username and Password -->
  <param name="Username">username_here</param>
  <param name="Password">password_here</param>
  <!-- DB Time Zone -->
  <param name="Timezone">EST</param>
</service>

Now I need a stream to read the records into. For the example, I will use only: time-stamp, entity, latitude, and longitude.

-- Input stream
CREATE INPUT STREAM StreamIn
SCHEMA (
    DbTime TIMESTAMP
    ,Entity STRING
    ,Latitude FLOAT
    ,Longitude FLOAT
);

You may have noticed that I have chosen different names for time-stamps: DbTime vs. TimeUTC. This was done on purpose, to clarify adapter definition. Here is the adapter:

-- Poll from Db adtapter
ATTACH INPUT ADAPTER PollDB
    TYPE ReadFromDBAdapterType
TO STREAM StreamIn
PROPERTIES
    DBNAME = "DamirTest",
    QUERY  = [[
SELECT TimeUTC, Entity, Latitude, Longitude
FROM GpsData
WHERE TimeUTC > ?C8_TIMESTAMP_FIELD
ORDER BY TimeUTC;
]],
TIMESTAMPFIELD = "DbTime"
,TIMESTAMPFIELDINITVALUE =
    "2009-01-01 00:00:00"
,POLLINTERVAL = "2000000"
;

Looks complicated, however the code was auto-generated using a point-and-click procedure and filling-in the adapter form.

The adapter TYPE ReadFromDBAdapterType may sound a bit confusing, but this its the Poll Adapter.

Field	Comment
DBConnectionName	Name of the database connection as specified in the `coral8-services.xml` file.
Poll Interval	Interval at which the adapter polls the database. Note that the form allows time units, while the generated code uses microseconds.
Timestamp field	Refers to the name of the time-stamp field in the stream schema.
Timestamp field initial value	Initial value for the time-stamp. In the example above — upon start-up — the adapter reads all records since New Year 2009.
Query	The query to run against the database. Thing of everything from the `WHERE` clause on, as a boiler-plate example. The `TimeUTC` is the timestamp column name in the database.

As described in the previous post, the adapter can use a time-stamp field or a counter (ID) field to remember the last record read. I am using the time-stamp, hence counter fields are left empty. The last row of the query, ORDER BY TimeUTC must be present, because the the adapter remembers the last value read — not necessarily the latest one.

When the server is started, the adapter reads all records between the TIMESTAMPFIELDINITVALUE and NOW().

When I manually insert a row into the database using:

INSERT INTO dbo.GpsData
	(TimeUTC, Entity, Latitude, Longitude)
VALUES 
	(getUTCdate(), 'ABC', 1.0, 2.0)
;

The new row appears in the stream viewer too.

From this point on, only new database rows are added to the stream. All looks fine, but one problem still remains. Suppose we run this for several months and then re-start the server. The adapter would (upon start-up) read all these records again.

To avoid the scenario, the module persistence option should be enabled for modules containing the Poll DB Adapter. The persistence can be enabled in Coral8 Studio on the module property tab. With the persistence enabled, Coral8 periodically stores all module variables and rows to a disk dive, and re-loads them upon server start-up. This way the adapter will remember the timestamp of the latest row and will continue from that point.