GPS Event [15] – Coral8 [3]

In the previous post I have managed to send GPS data to Coral8. This time I would like to use Coral8 as a database-loading front end, with minimum changes to the already existing program. I already have MS SQL Server running on the same machine as Coral8, so this should not be too complicated.

Here is the to do list:

  1. Create a database and a table for GPS data;
  2. Create an ODBC connection to the database;
  3. Register the database as a service with Coral8, using the ODBC connection;
  4. Restart Coral8 Server;
  5. Attach a write-to-database output adapter to the output stream;
  6. Test.

Step 1 – Create a database

Using MS SQL Server Manager Studio I have created a new database named DamirTest and and a table GpsData.

USE DamirTest
	Entity varchar(25) NOT NULL
	,Latitude float
	,Longitude float
	,Course float
	,Speed float
	,TimeUTC datetime NOT NULL

As you can see, the idea is to match the record of the output stream (see the previous post).

Step 2 – Create an ODBC connection

Coral8 communicates with MS SQL Server using an ODBC connection, so one has to be added for the database. The dialog can be accessed via Start >>> Administrative Tools >>> Data Sources (ODBC).  For SQL Server 2005 — and latter — specify SQL Native Client for the driver.

Step 3 – Register the database as a service with Coral8

Coral8 remote services are listed in the coral8-services.xml file. The file can be found in /Coral8/Server/conf/ directory. I had to add the following segment to the file:

<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">my_username_here</param>
    <param name="Password">my_password_here</param>

Step 4 – Restart Coral8 Server

Coral8 reads configuration files on start-up, so the server has to be restarted. There is no need to re-boot the machine, stop and start the program only.

Step 5 – Attach a write-to-database output adapter

No need to remember this syntax. I have simply used point and click to attach the adapter to the output stream — I did have to enter the query in the adapter form.

    TYPE WriteToDBAdapterType
    TO STREAM StreamOut
    DBNAME = "DamirTest",
    QUERY  = [[
INSERT INTO [DamirTest].[dbo].[GpsData]
    (Entity ,Latitude ,Longitude
    ,Course ,Speed ,TimeUTC)
    (?Entity ,?Latitude ,?Longitude
    ,?Course ,?Speed ,?TimeUTC);

Note how all stream field names start with a question mark. They are positionally mapped to the list of database fields; so it is not really necessary to use same names for database and stream fields.

Step 6 – Test

This is all it takes, the rest of the code from the previous post remains unchanged. As in the last example, I have sent ten GPS readings to Coral8. Here they are in the output stream,

and in the database.

To be continued.