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;
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 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:
Connection to DamirTestDBDriverODBC DamirTest True my_username_here my_password_here
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.
ATTACH OUTPUT ADAPTER ToDB 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.