MLDatabase Plugin

From Cinemar Wiki
Jump to: navigation, search

About

MLDatabase is a plugin for "Power Users". It provides highly flexible access to a SQL or ODBC database that you would create (not a novice task). You can enter data into the database from MainLobby, and you can run SQL commands upon the database from MainLobby. MLDatabase can then create a variable with the results of your query.

This opens many doors for all kinds of projects.

Because of it's nature, support for this plugin will be nominal as most all issues will be about understanding on how to install an SQL database, create a database, create tables, create store procedures etc. that are all outside of the support scope of Cinemar. The Cinemar community is welcome to share installation tips, databases, How To's, etc. on both this Wiki thread, as well as on the forum which will have a new thread created for it. This plugin is really intended for software developers to use.

Hopefully in the near future, we can script the installation of a SQL database with some database import tools that will allow other non experts to use applications developed by experts using MLDatabase.

Here is a web reference for basic SQL info SQL Tutorial

MLDatabase is now in BETA.

Installation

Install MDAC 2.8

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en

Download the plugin via MLInstaller: Here

Run the install file on the primary MLServer3 PC.

MLDatabase comes with drivers to attach via ODBC to an Access Database. If you want to connect to a SQL database, install SQL Express and create that database. Note the login and password needed to access the database.

Here are some info on how to do that Install SQLExpress

Launch MLServer3 and right click on the MLDatabase plugin in the Plugins window. Click Settings.

Click the Connection Aliases tab.

Click User/PW.

Enter your server's user Login name and password and click OK.

Click New.

Enter and Alias name of your database connection.

Click on the Server drop down.

Click on the server where your database is available.

Click on the database for use with MLDatabase.

Click OK.

You can now send MLServeCmds to this Alias.

When MLDatabase loads with MLServer3, it will auto log into the database you setup.


Advanced: Files installed with MLDatabase installer:

sqldmo.dll;

msbind.dll;

odbctool.dll;

tabctl32.ocx;

scgrid.ocx;

tab32x30.ocx;

mscomctl.ocx;

msvcr71.dll;

odbcbcp.dll;

MLServer Variables

delete_variable~variable name

variable name_recordcount

This variable is typically used for knowing when to page to end records

MLServer Commands

Here is a reference source for SQL commands:

http://www.w3schools.com/sql/sql_quickref.asp


Syntax:

MLServeCmd.MLDatabase|Process_Command~Alias~SQL Command~Output variable name


Here are some sample SQL commands:

MLServeCmd.MLDatabase|Process_Command~BarLobby1~select name from Drinks where GlassType =15~MLDB_Drinks

MLServeCmd.MLDatabase|Process_Command~BarLobby1~select name from Drinks where name like 'L%'~MLDB_Drinks


Once you have created variables, use the below to delete them from MLServer's Variables List:

MLServeCmd.MLDatabase|Delete_Variable~MLDB_Drinks_*

Process_Command_with_Scroll~Alias~SQLCommand~OutputVariablename~Start~Interval

Example:

Process_Command_with_Scroll~Alias~SQLCommand~OutputVariablename~1~8

Would give first 8

Process_Command_with_Scroll~Alias~SQLCommand~OutputVariablename~9~8

Would give the next 8

MLServeCmd.MLDatabase|Process_Command_with_Scroll~BarLobby1~select name from Drinks where name like 'L%'~MLDB_Drinks~1~8


Delete all rows in a table

Process_Command~db~delete from mytable~var


Graphing

mldatabase|generate_graph~test~SELECT DateTime, DeviceResponse From dbo.DeviceData WHERE (DeviceAlias = 'Cold_Air_Return') AND (DeviceID = 'temperature') ORDER BY DateTime ASC~500

Send the query for the x and y axes and it will send you a jpg of the graph

generate_graph~graphalias~variable(optional)

Example Select statement select datetime, deviceresponse from DeviceData where DeviceAlias = '<<VAR>>' and DeviceResponse <> 185 order by datetime

if use <<VAR>> then need to provide variable during generate_graph

Format the graph the way you want it, via right click then save.


Example graphing command for ML1Wire temperature sensor:

select datetime, deviceresponse from DeviceData where DeviceAlias = '<<VAR>>' and DeviceID = 'Temperature' and DeviceResponse <> 185 order by datetime

mldatabase|generate_graph~1wiregraph_humidity~server closet

Command to generate jpg of graph:

mldatabase|generate_graph~1wiregraph_temp~server closet


Configuration Examples

To Configure Microsoft Access as the MLDatabase ODBC database:

1. Create an ODBC connection. To do this go to Control Panel/Administrative Tools/Data Sources (ODBC)

2. Create a System DSN. Use the Microsoft Access Driver.

3. Type a Data Source Name and Description

4. In the connection Alias tab of MLDB, hit New. Then click the ODBC button in the lower right hand corner.

5. Set the alias and select the DSN you just created.

6. You now have a connection that you can use to query or update a database

You should be able to see the tables in the database using the Available Database tab. Select ODBC connection and double click on the name of the DSN you created. A list of available tables will come up in the tables tab. Double clicking on a table will show the data in windows on the bottom of the screen.

Check this post for some additional information how to use the MLDB commands. http://www.cinemaronlineforums.com/forum/viewtopic.php?t=11308&highlight=

Here are some commands I use to update a database with equipment run times

This creates the record: Code:

MLServeCmd.Macro|setvariable|MLWeederTech_B_A_Last_Update~<<NOW>>!mldatabase|Process_Command~Equipment_RunTime~Insert into Runtimes (StartTime, DeviceName) Values ('<<NOW>>', 'HWH')~MLDB_HWH


This updates the record: Code:

MLServeCmd.Macro|setvariable|MLWeederTech_B_A_Last_Update~<<NOW>>!mldatabase|Process_Command~Equipment_RunTime~Update Runtimes set EndTime='<<NOW>>' where ID={{MLDB_HWH_LastID}}~MLDB_HWH


Energy Monitoring Example:

The table is pretty simple. It contains the following fields: ID, StartTime, EndTime, DeviceName

The device goes on, data is added to ID (Auto index field), DeviceName and StartTime. When device goes off, Endtime is inserted using the ID provided by MLDB.

Here are a couple example SQL views:

Day to Date: SELECT DeviceName, ROUND(SUM(DISTINCT (CAST(EndTime AS float) - CAST(StartTime AS float)) * 24), 2) AS DSH_Day_To_Date FROM dbo.RunTimes WHERE (CONVERT(varchar, StartTime, 101) = CONVERT(varchar, GETDATE(), 101)) AND (DeviceName = 'DSH ') GROUP BY DeviceName

Month to Date: SELECT DeviceName, ROUND(SUM(DISTINCT (CAST(EndTime AS float) - CAST(StartTime AS float)) * 24), 2) AS DSH_Month_to_Date FROM dbo.RunTimes WHERE (MONTH(StartTime) = MONTH(GETDATE())) AND (YEAR(StartTime) = YEAR(GETDATE())) AND (DeviceName = 'DSH') GROUP BY DeviceName

Year to Date: SELECT DeviceName, ROUND(SUM(DISTINCT (CAST(EndTime AS float) - CAST(StartTime AS float)) * 24), 2) AS DSH_Year_to_Date FROM dbo.RunTimes WHERE (YEAR(StartTime) = YEAR(GETDATE())) AND (DeviceName = 'DSH') GROUP BY DeviceName

Very similar for each of the other devices.


Database techniques:

Code:

mldatabase|Process_Command~Equipment_RunTime~Insert into Runtimes (StartTime, DeviceName, MyMLSVariable) Values ('<<NOW>>', 'HWH', '{{sample_variable}}')~MLDB_HWH 


General format for insert is:

Code:

Insert into TABLENAME (Var1Name, Var2Name, Var3Name) Values ('Var1Value', 'Var2Value', 'Var3Value')~MLDB_ALIAS 


This will work if the database field is a string. If it is a number (integer or double), try it without the ' on the ends of the {{sample_variable}}.

Note: When you do an insert, MLDB will store the record ID in the variable databasealias_LastID. This allows you to go back and update that record anytime proir to another insert.


Stock Charting Example

Create a new Access database (.mdb type) and call it "StockInfo.mdb"

Within that new databse, create a table called "StockValues"

Within that table, create:

ID (autonumber data type), sDate (date/text), StockSymbol (text), StockPrice (number), StockValue (number)

Save this database into the MLServer\Data directory.

Follow the

"To Configure Microsoft Access as the MLDatabase ODBC database" and configure the DSN as "StockInfo".

Go into MLDatabase and ensure you have an unused database license.

If so, click Connection Aliases tab and click New.

Click the ODBC button in the Command Alias window.

Click on the DSN and find your "StockInfo" DSN

Type "StockInfo" in the ALIAS field and click OK to close that window.

You should now see StockInfo Alias added.

Click Available Databases tab and click Refresh SQL/ODBC Lists button. Then click ODBC Connections sub tab.

Click on StockInfo. Then, click on the Tables sub tab and click StockValues table. You should see your empty database fields below.

Click on Chart Settings Tab in MLDatabase Settings window.

Click Add Template button.

Type 'Stock_Graphs" in the Alias column. Connection should be "StockInfo" (same as on Connection Alias's tab). Copy / Paste the below into the Select field:

Select sDate, StockValue from StockValues where StockSymbol = '<<VAR>>' order by sDate

Under the Points column type 1500. Check the Date checkbox. Click the Save button.


To update your portfolio For each stock to be graphed, create a Command Map (samples provided in the MLServer\DownloadedFiles directory)

Command Map Name:

StockInfo.001

Command Map:

MLServeCmd.MLDatabase|Process_Command~StockInfo~Insert into StockValues (sDate, StockSymbol, StockPrice, StockValue) Values ('<<DATE>>', '{{mlstockinfo_symbol_001}}',{{mlstockinfo_price_001}},{{mlstockinfo_rawvalue_001}})~MLDB_Stockinfo_001 Repeat this for as many as you want to chart.

Create this Command Map:

Command Map Name:

StockInfo.Update

Command Map:

Macro|StockInfo.001!StockInfo.002!StockInfo.003!StockInfo.004!StockInfo.005!StockInfo.006

StockInfo.Update is the macro you need to run every weekday after the stock exchange closes. You can define this to happen on a regular basis using the MLScheduler plugin.

If you look in the StockInfo.mdb you should now see values for your stocks. If this is the case, then it's time to create a graph.

On the Chart tab, click on the Test button to the right of the Select Field. A popup window should appear. Type the trading symbol of one of the stocks in your portfolio like "IBM".

You should now see a basic chart. There likely will only be one data point since this is the first day you have run the database update (StockInfo.Update command).

You can now right click on the chart and customize it's parameters as to chart type, label scaling and naming, colors, etc. The MLDatabase plugin will create a JPG of this chart for presentation in MainLobby Client.

Your chart settings will be saved to Stock_Graphs.chd in the mlserver\images\mldatabase directory.


Viewing the graphs in MainLobby Client:

In MLCycleStates create a CycleStates labeled "IBM"

MLDatabase|Generate_Graph~Stock_Graphs~IBM


Bugs

Feature Requests

Release Notes

May 4, 2008 Beta timeout extended

April 11, 2008 Beta timeout extended


December 18, 2007 v 3.0.151 released. Extended beta timeout


October 21, 2007 v 3.0.132 released. Refined charting capability Wiki documentation updated (still rough) with some new charting examples.


October 20, 2007 v 3.0.100 released. Adds charting capability Wiki documentation updated (still rough) with some charting examples. You can now create graphs and display in MainLobby Client! Lots of usages for this. Examples will be created and posted soon!


September 26, 2007 v 3.0.84 released. Release Candidate. Adds MLDatabase licensing Adds SQL / ODBC Refresh button (to reconnect to databases) Addresses lockup when cancelling login / password. A few bug fixes.


July 3, 2007

V 3.0.32 updated. Fixed a bug.


July 1, 2007

V 3.0.25 updated. Added additional variables for debugging of SQL purposes.



June 30, 2007

V 3.0.23 updated. Fixes null database issue.


June 24, 2007 MLDatabase 3.0.13 updated to correct some errors and be more resilient to database password problems



June 12, 2007 MLDatabase 3.0.12 updated to support ODBC connections


June 9, 2007 MLDatabase plugin release to BETA