Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to get the DataLogdb driver up and running
#1
I'm re-posting this here to make it easier to find. Please note that the username Brightan no longer exists and has been replaced by Zanthic

The following mini tutorial was written to outline the steps required to get the DataLogdb driver up and running. This information was derived from the driver description, various members of the support forum, my personal experience and private emails between the driver developer (jscheller) and myself (Brightan(now Zanthic)) while debugging.
The following assumptions are made
- The driver is running on the CQC main server
- The OS is Windows XP
- No database experience by the user (or myself for that matter!)
- The database used is SQL
- Any deviation from these instructions and you are on your own!
- A simple log will be made of the ELK M1 alarm status. The CQC moniker for the Elk driver used in this example is 'ELK'
- The graphing functions are not implemented in this example (not yet anyway)

Steps required
1) Install Dot Net 2.0
2) Install the two software packages needed for creating SQL databases
3) Create a configure the SQL database for use
4) Set up computer to create a standard ODBC connection to the SQL database. ODBC (Open DataBase Connectivity) is a standard way of communicating with our particular database.
5) Install driver in CQC
6) Display data in CQC

Overview: The basic idea of this driver is that you can log data to a database with a date and time stamp and then display the data within CQC. The displayed data might look like a simple list (as will be done in this example) or it can be graphed out (not part of this tutorial). The data stored can be something as simple as the date and time of a door opening and closing that is connected to your ELK alarm system or it can be the current tempurature saved every 60 seconds (or whatever time you want)

Note: What you need to know is that CQC cannot create a database so your CQC server computer must be equipped to create an empty database (with certain exact parameters) and then the datalogdb driver can insert data into the database and take data out of the database to display it. Most of the setup that follows is in fact to get the actual empty database up and running!

Inside our database there are two lists of data, called tables. One table contains a list of the CQC fields that we want to store to our database. This means that if we want to store 4 digital inputs to our Elk alarm that are connected to 4 magnetic door sensors, we will have 4 entries in our first table, which is named DataLogSchedule. The second table is going to be filled with the actual data that CQC will store to the database as the doors open and close. This second table is called DataLogValues.

Ok, let's get going...

Step 1) Install Dot Net 2.0 This is the link that appears in the forum http://msdn.microsoft.com/netframework/ but as of this writing it is pointing to .Net 3.0 Beta 2 so I thought it best to stick with .Net 2.0 so if you scroll down a little on the same page you will see "popular downloads" and ".Net Framework Version 2.0 Redistributable Package (x86) and if you click on it it takes you to a download page that you can use to download (of course)
This downloads DotNetFX.exe Download it, save it and run it (save it because you know you'll want to do this again and again because it is so much fun) There are no install options to worry about.


Step 2)(The following is a quote from a forum thread)
The two links below should be the direct file downloads you need to install everything for the DataLogDB driver. The first link is the actual database, and the second is the management console app:

http://go.microsoft.com/fwlink/?LinkId=65212

http://go.microsoft.com/fwlink/?LinkId=65110

These links for SP1, which is the current released version. I'd stick with this unless you need the SP2 update for some reason (like you're running Vista apparently). The download page for SQL Server 2005 SP2 Beta is here... http://www.microsoft.com/sql/ctp.mspx
(end quote)

If you save these files to your hard drive, you'll have SQLEXPR32.exe and SQLServer2005_SSMSEE.msl
run the SQLEXPR32.exe file first to install the required database files. I used all the default settings during install.
I got a "minimum hardware requirement not met" message on my machine but it still seems to work ok.

Now run the SQLServer2005_SSMSEE.msl program. This is going to install the management studio, its full name is "Microsoft SQL Server Management Studio Express". This is what you are going to use to create a blank database on your computer (which should be on your CQC server, remember?)
I used all the default settings for this one as well.

Step 3)
At this point you should have finished installing the above programs and in your start menu there should be an entry for "Microsoft SQL Server 2005" and under that "SQL Server Management Studio Express". Run this now. When this program loads it will display a screen asking you to "Connect to Server" Make a note of the default entry under server name. On mine it is the <name of my computer>\SQLEXPRESS You are going to need this later, trust me!
Click the connect button and wait for the program to finish loading.

On the left hand part of the screen will be an area called the Object Explorer and the right hand side has a summary tab.
Under the Object explorer, right click on the "Database" entry and a menu will pop up that has the first entry "New Database". Click this now.

A new Database window will pop up with it asking you for the name of your new database. This is not going to be the name that CQC will use, this is just a template of sorts so the name is not all that important. I'll call mine CQC_Blank_DB
Click OK at the bottom.

When it is done you can go back to the Object explorer part of the program and click on the plus sign next to the "Databases" entry. It should open and show you two databases
1) System Databases
2) CQC_Blank_DB (unless you're a real rebel and called yours something else!)

Right click on CQC_Blank_DB and choose "New Query" This should open up a new tab on the right side of the program with the title ending in SQLQuery1.sql
Now, you are going to have to cut and paste the following information

CREATE TABLE [dbo].[DataLogSchedule](
[CQCMoniker] [varchar](50) NOT NULL,
[CQCField] [varchar](50) NOT NULL,
[UpdateInterval] [smallint] NOT NULL,
[ChangesOnly] [bit] NOT NULL,
[Descriptor] [varchar](50) NOT NULL ) ON [PRIMARY]

CREATE TABLE [dbo].[DataLogValues](
[Descriptor] [varchar](50) NOT NULL,
[Value] [varchar](50) NOT NULL,
[TimeStamp] [datetime] NOT NULL ) ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_DataLogValues] ON [dbo].[DataLogValues]
([Descriptor] ASC, [TimeStamp] ASC )
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

This is from the Driver description from the main pages of the CQC web site so it might be worth while to check back there to see if there are any changes from what I have listed above.
You are going to cut these commands and paste them into the query window on the right side of the program.
After the entire chunk of text is pasted in, click on the "execute" button on the menu bar of the program. It is also found under the menu item "Query"
You should get a new window at the bottom that tells you the Command(s) completed successfully. Yippee, you're almost there, well, not really but I thought it would make you feel better. (ok, it's late, what do you expect?)

OK, go back to the object explorer (to the left) and click the plus sign next to CQC_Blank_DB to show it's entries. One of the entries should be "Tables". Click on its plus sign to open it up. You should now have three enties
- System Tables
- dbo.DataLogSchedule
- dbo.DataLogValues

Remember that the dbo.DataLogSchedule will hold a list of the CQC fields that we want to store to our database and dbo.DataLogValues is going to store the actual data.

Right click on dbo.DataLogSchedule in the Object explorer and choose "Open Table" You should see a new window with the first line being columns containing CQCMoniker | CQCField | UpdateInterval | ChangesOnly | Descriptor
and under that a line with NULL in each of the columns.

Click on the first Null and change this to "ELK" (without the quotes of course). This is the CQC driver moniker that you assigned in CQC when you loaded the driver for the ELK M1. Of course, if you are logging data for some other driver, you'll use that moniker instead.

Tab over to the next column (CQCField) and enter "AreaArm1" This is the field that I use to show me the alarm state of my Elk M1. Enter 0 (zero) in the next column for UpdateInterval (you can also enter a seconds value if you want to log something like the current temp every N seconds. Check the driver description for more details) and tab to the next entry (ChangesOnly) and enter "True" Because my UpdateInterval is 0 and my ChangesOnly is True, CQC will only log a database entry when my alarm changes state, from "Disarmed" to "Arm Away" for example.

You'll notice that a red exclamation mark is showing up next to your entries, that's OK, don't panic!

Enter "Alarm State" for the last column entry (Descriptor) and jot this down on a piece of paper, you'll need this descriptor entry later on when setting CQC up. Press Enter and the red exclamation marks should go away. You'll also notice the second line of Nulls was added, this is OK too.
Shut this program down, it will automatically save your data and you are now done this step! If the program asks you if you want to save your SQLQuery1.sql file you can safetly say no

(end of part 1)
Embedded micro-controller design and Controller Area Network interfaces at http://www.zanthic.com
Reply
#2
Part Two

Step 4) Set up computer to create a standard ODBC connection to the SQL database.
On your computer, click the Start menu, Settings, Control Panel, Administrative Tools, and finally "Data Sources (OBDC)"
This will start a program called "ODBC Data Source Administrator"

Click the tab that says "System DSN"
Click "Add" (to the right)
Scroll down the list shown and choose "SQL Server"
Click "Finish" at the bottom

A new screen called "Create a New Data Source to SQL Server" will pop up.
Give it a name. This is the name that CQC will see. I'll call mine "CQC Alarm Log"
Give it a description. (I'm not going to spell everything out, make something up!)
Now, Under the server entry (this is important) Remember way back at the beginning of Step 3 I said my server name was <name of mycomputer>\SQLEXPRESS. Use that here!
Click "Next" at the bottom of the screen

Leave the defaults the way they are for this screen (How should SQL Server verify the authentication of the login ID?) Just press "Next"

Now you should see a check box saying "Change the default database to:" Check this box and using the pull down menu, choose the name of the database (the original one) "CQC_Blank_DB"
Leave everything else the way it is and click "Next"

Leave the next screen the way it is and press "Finish"
At this point a new screen will appear with a button at the bottom "Test Data Source". Click it and you should get "Tests completed successfully" If not, well, you're hooped. (or at least beyond my ability to help you)
Close this program cause you're done with it.

5) Install driver in CQC
Open the CQC Admin interface
Choose "Administer server drivers"
Choose your server computer from the list
Choose "Add Driver"
Scroll down the list and pick "Data Log Database" (currently V1.2 as of this writing)
Click "select" and give it the Moniker "DataLogger" (you can pick something else but then my examples are going to confuse you so just do as I say and quit with the indepentent thought nonsense!)
Click "Continue"

You should see "CQC Alarm Log (SQL Server)" in the list, choose it!
click continue to finish
Your Driver State (in the lower right) should say "connected to device". If not, refer to the hooped comment above.
(ok, it's not that bad, I just can't help you here)

6) Display data in CQC
This is going to be a really simple example because this is the same point that I am currently at.
Open your CQC Interface editor and open one of your templates that has some open area to play in.
Add a "Vert List Browser" to your screen and open its properties.
Under the Field tab, select the Driver to be "DataLogger" and the field to be "QueryResults"
Close this configuration (CQC will give you a warning that the widget's actions are not configured but close it anyway)

Click on the background screen of your template so the "Configure a 'Template' widget" screen pops up
Under the 'Action' Tab, edit this and add the following
Devices::FieldWrite(DataLogger.QueryDescriptors, Alarm State)
"Alarm State" was the Descriptor that you used in the last column of the database table when you were setting it up, remember? (I told you to write it on a piece of paper)
add this as the second line
Devices::FieldWrite(DataLogger.Command, RunQuery)

At this point if you save this template and try opening it in the viewer, as soon as it opens it should automatically populate the Vertical List Browser with the data that has been collected. This is assuming of course that you have changed the state of your Elk alarm system so that there is data in the database.
Anything beyond this will be up to you to figure out or maybe if I continue to play with this I will update this file if it is being used.

Closing comments: It took me roughly 8 hours to get to this point due to a few missing details and my ignorance of database creation so hopefully this will save you some time.
Embedded micro-controller design and Controller Area Network interfaces at http://www.zanthic.com
Reply
#3
Thanks for the writeup...it worked! I'm logging my outdoor temps now...though I don't have anything yet to do with it.

This is a good step towards implementing some kind of graphing of power usage from the Brultech driver, so thanks! I don't fully understand most of what I did to get this logging working, but it works at least.

One quirk, I think...in step 5, during the driver install, when we're supposed to select the ODBC to connect to....I found that this only worked if I was actually installing the driver while ON the PC in question. In other words, you know how normally you can go to any client PC, and install a device driver on any other PC via the Admin Interface? Well, in this case, when I tried installing the database log driver from a different PC via the admin interface, the ODBC box was empty. So, I have to remote login to the target PC and install the driver from there.

I guess it makes sense and all...but it's a little confusing at first since we're so used to CQC handling that kind of stuff. I guess it falls along the same line as if a driver needs a file, it has to be on the target PC already. *shrug*

But hey...it works!
Reply
#4
I'm assuming step 2 in post 1 has a typo and should say SQL databases.

Russ...
Reply
#5
I've installed the driver. It is interesting that there are some quirks specific to MS SQL vs. MySQL. I'm still trying to work my way through them. The first obvious thing is that the date/time stamp won't write appropriately. I would love to see a mechanism where I can execute a full SQL statement to this driver so I could add data as necessary. I would clearly like to be able to set a triggered event that writes data instead of having a driver handle that for me.

I'm going to let this think run a bit and see how it behaves.

Russ...
Reply
#6
There are a couple of things that prevent this driver from being used with MySQL. One is the way the query selection is formed. It uses "SELECT Top" for the query to get the most recent entries. MySQL doesn't support that command. For MySQL you have to use "Select...... LIMIT #" to limit the results to the ones you want. The other thing is the time stamp used in the queries. I played around with this alot (it's been a while) and if you use the ODBC timestemp format it will work for either one. I've attached the text format for the DatalogDB driver that supports Acess, MySQL, and SQL Server. Just look for the sections that are using Switch(m_ODBCType) to see where different formatting/commands are required.


Attached Files
.zip   DataLogDB_2_2_5_v1_1.zip (Size: 5.56 KB / Downloads: 7)
It's the early bird that catches the worm, but it's the second mouse that gets the cheese...
Reply
#7
zaccari Wrote:I'm assuming step 2 in post 1 has a typo and should say SQL databases.

Russ...

what, you've never heard of a SQP database? :-D
Embedded micro-controller design and Controller Area Network interfaces at http://www.zanthic.com
Reply
#8
Anybody getting more advanced than the tutorial with this? I'm interested in this stuff mainly for power usage and weather data logging, but would like to know there's a way to get the data back out and graph to present it in CQC.
Reply
#9
I'm exploring the current options of charting data in this thread here, though I've been temporarily halted in that effort due to some work at the house. I hope to get back to it soon. I'm mainly focused on semi-realtime power use graphing, but temperatures would be a close second.
Reply
#10
I've seen some other write about RRD tools http://oss.oetiker.ch/rrdtool/

Which is a fixed size DB...

With the DB discussed in this thread how do you control the size of the thing and purging of the info?
Reply


Possibly Related Threads...
Thread Author Replies Views Last Post
  How To - Configure MyMovies Driver on Windows 7 x64 wuench 18 13,643 10-10-2016, 09:05 AM
Last Post: MikeA
  create a grid of temp values from DataLogDB IVB 6 2,199 12-13-2015, 09:37 PM
Last Post: IVB
  How to diagnose a runaway driver issue Dean Roddey 4 4,824 05-22-2013, 04:33 AM
Last Post: DaveAlvarado
  How To: Logitech Squeeze Center Driver jkish 328 62,999 12-17-2010, 02:09 AM
Last Post: gacevich
  How to setup the Weather Channel data feed driver. beelzerob 3 12,449 05-24-2010, 04:39 PM
Last Post: Dean Roddey
  Get Album Art Working with J. River Repository Driver Squiddy 0 5,682 11-23-2008, 06:15 PM
Last Post: Squiddy

Forum Jump:


Users browsing this thread: 1 Guest(s)