DataLogDB Driver Thread

[jscheller]

This is a new thread for the DataLogDB driver discussion. The old thread was lost during a forum conversion. Information that can be recovered from other sources will be posted here, and any new discussion regarding that drivers should occur here. I'll monitor this thread for questions and comments.

[robertmee]

I was able to find around 11 pages of the original thread through various google searches. I have them saved as offline html files. Don't know if they would be useful to you, but I can e-mail them if needed.

[jscheller]

That'd be great... I can digest them all here and try to get one or two posts with all the core information back into this thread. That'd make this thread a lot more useful than the prior one in any case... Thanks!

[Brightan]

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) 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 SQP 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)

[Brightan]

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. If you found this useful, my username is Brightan. If you're ticked off, talk to jscheller

[beelzerob]

Wow....finally a guide for someone as db dumb as me....I'm actually interested in this logging driver again! Still don't have the time, but I do have the interest now at least.....

[robertmee]

Great work! I followed it to a 'T' and it installed perfectly. (Other than the Net viewer 2.0....I didn't see a popular section on the link, but there was a tab at the top that said 2.0).

As far as adding additional fields and additional drivers, just do it under that one table and add new lines? Or for effeciency sake should we create new tables for different drivers?

[robertmee]

Another question, more towards UI design. I don't suppose there is a way to display multiple query results is there? Right now, I have a button for each zone of my alarm system to query the activity and display it in a single vertical list browser. Is there any way to have more than one list browser to show multiple zones at once? It would be useful for tracking activity in the house in a single snapshot view.

[jscheller]

Originally Posted by robertmee

As far as adding additional fields and additional drivers, just do it under that one table and add new lines? Or for effeciency sake should we create new tables for different drivers?

The tables names are hard coded, so you'll always be adding rows to the DataLogSchedule table to record new values...

Originally Posted by robertmee

Another question, more towards UI design. I don't suppose there is a way to display multiple query results is there? Right now, I have a button for each zone of my alarm system to query the activity and display it in a single vertical list browser. Is there any way to have more than one list browser to show multiple zones at once? It would be useful for tracking activity in the house in a single snapshot view.

Right now, not directly.

We're basically using a set of fields to specify what the query looks like ("QueryDescriptors" and similar), and then another one to return the results ("QueryResults") when a specific command is sent ("RUNQUERY" passed to the driver' "Command" field).

There may be some way to capture the values returned in QueryResults (which is a StringList) into a local variable, then display this local vairable in a VerticalListBrowser widget or something similar. You could then maybe setup a template that displayed these local vars instead of the browser fields, and with the right action scripts and/or macros, maybe come up with a set of commands to populate multiple different views. I haven't attempted something like this; Dean might comment if this is even feasible or not.

Another tactic if there's enough interest would be to add another field like 'QueryResults2' and a different command to populate that field from the query... This would be a pretty easy change. I'm a little bit biased towards keeping the driver interface light, but if the approach above seems infeasible or overly complex, my arm wouldn't need to be twisted more than a degree or two either direction...

[robertmee]

Just a quick idea of the power of this driver....I'm able to click on any zone in my layout and get a popup that shows all changes of that zone. Very Cool.