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
,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 -->
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>
<!-- DBDriverConnectString = DSN name -->
<!-- Username and Password -->
<!-- DB Time Zone -->
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
You may have noticed that I have chosen different names for time-stamps:
TimeUTC. This was done on purpose, to clarify adapter definition. Here is the adapter:
-- Poll from Db adtapter
ATTACH INPUT ADAPTER PollDB
TO STREAM StreamIn
DBNAME = "DamirTest",
QUERY = [[
SELECT TimeUTC, Entity, Latitude, Longitude
WHERE TimeUTC> ?C8_TIMESTAMP_FIELD
ORDER BY TimeUTC;
TIMESTAMPFIELD = "DbTime"
,POLLINTERVAL = "2000000"
Looks complicated, however the code was auto-generated using a point-and-click procedure and filling-in the adapter form.
TYPE ReadFromDBAdapterType may sound a bit confusing, but this its the Poll Adapter.
||Name of the database connection as specified in the
||Interval at which the adapter polls the database. Note that the form allows time units, while the generated code uses microseconds.
||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.
||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
When I manually insert a row into the database using:
INSERT INTO dbo.GpsData
(TimeUTC, Entity, Latitude, Longitude)
(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.