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:
- Create a database and a table for GPS data;
- Create an ODBC connection to the database;
- Register the database as a service with Coral8, using the ODBC connection;
- Restart Coral8 Server;
- Attach a write-to-database output adapter to the output stream;
- 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.
CREATE TABLE dbo.GpsData(
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:
<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>
</service>
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
PROPERTIES
DBNAME = "DamirTest",
QUERY = [[
INSERT INTO [DamirTest].[dbo].[GpsData]
(Entity ,Latitude ,Longitude
,Course ,Speed ,TimeUTC)
VALUES
(?Entity ,?Latitude ,?Longitude
,?Course ,?Speed ,?TimeUTC);
]],
COMMITFREQUENCY = "0"
;
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.
Leave a comment