I was excited to see that RTMCpro can be used with a SQL database, but so far I haven't had much success. I am able to add the db as a source, but none of the variables are visible below it in the Data Source Browser (there's no "+" there). My SQL db was not created by LNDB, is that a problem? Also, I've never bothered storing RecNum since they aren't actually unique (I believe they reset when a new logger program is installed). If I create a RecNum column in the db will that fix a formatting issue that RTMC seems to be having, or are there other parameters that an independently generated SQL db will not meet?
Thanks,
Alex
I believe RTMCPro needs definition/information about the data source. When using a database, RTMCPro is looking for meta information about stations, tables, and fields. LNDB creates and manages this information for you.
RTMCPro is designed to only use SQL databases that were created by LNDB. As Sam indicated part of what LNDB does is to create some metadata tables. There are also some strict requirements about how the primary keys on the data tables are created. An advanced DBAdmin could take a look at what LNDB does to create the database and mimic that (if the data is coming from non Datalogger/LoggerNet sources), and then RTMCPro would work, but CSI isn't going to be able to give support for that scenario, and it isn't documented.
The easiest route would be to get LNDB -- it really isn't very expensive. There is a trial version of it that can provide you some time for evaluation, etc.
Thanks for the clarification, I figured that was the case based on my experimentation. I didn't see it expressly stated in any of the documentation that the database source feature is only for use with LNDB.
We'll get something in the help file so that it is clearer the database source must be LNDB.
Dana W.
Hi Alex,
have you figured out how to display data from an SQL server in RTMC pro 4 which hasn't been created by LNDB?
I have created a summary table on my server which is just querying a LNDB written table, however to much dispair I cant get it to display in RTMC...
thanks
Martin
Hi Martin,
We have it working for the most part, but I'm not responsible for the SQL end of things. I've emailed our database manager to encourage him to write an explanation of what he did to make it happen since I only understand it conceptually.
Alex
Hi Alex,
thank you for your response, that would be greatly appreciated as I am workng on my own with limited programming knowledge.
Looking forward to your Database managers responce
Regards
Martin
Hi,
You don't have to use LNDB as long as the data structure is the same as what LNDB creates so that includes the metadata tables and data tables that mirror what LNDB produces.
I am getting external (non loggernet) daat into RTMCPro via a java program. All I did was to use the LNDB created tabl;es as a template for my new data table and then add the details of the new data table into the metadata tables and then wrote a java program to insert the non loggernet data into the table and I can see it in RTMCPro - easy!
The best route would be to get a trial version of LNDB and let it create some data tables and the metadata tables so you have the format and then from there yopu are free to create and populate data tables using that structure and RTMCPro will see them and use them. You do need to use the LNDB key fields such as recnum but this is not a huge deal as you should have a primary key anyway.
Let me know if you need more help. For me this is a real break through as now I can seamlessly intergate loggernet data and non-loggernet data into the one system - cool!
Cheers,
Scott.
Hi Martin,
Alex is my colleague, and I'm the DBA Alex mentioned in his post.
do you still need help setting up your database to use with RTMC?
Scott has provided a good summary of the steps to take in order to reverse-engineer the database struture required by RTMC software.
If you post what exactly you're trying to create I'll be able to customize my SQL scripts to fit your needs, and you'll just have to run them on your SQL server to create the whole RTMC-compatible database structure.
Note: RTMC is not compatible with SQL Server 2000 and earlier.
Regards,
Omar
Hi Omar,
thank you for your reply, yes your help would be great.
if we took a simple monthly average table (table1) which is autoupdated based on a LNDB created table on my Server. there are two variables in table 1 (V1,V2)
how would you go about addpting your code for this to be displayed in RTMC as a Table display?
thanks
for your help
Martin
Hi Omar,
I do hope you can help with this and I that things are clear enough.
hope to hear from you soon
Martin
Hi Martin, Sorry for the delay in responding, things have been hectic lately.
Here are the SQL statements you need to run on your server to create an RTMC-compatible database, run this code on a test database and do some testing with it first. Let me know how it goes.
--1st Create table that holds station metadata
CREATE TABLE [dbo].[LNDBStationMeta](
[stationID] [int] IDENTITY(1,1) NOT NULL,
[lnStationName] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[stationID] ASC
))
GO
--2nd Create table that holds station tables metadata
CREATE TABLE [dbo].[LNDBTableMeta](
[tableID] [int] IDENTITY(1,1) NOT NULL,
[LNDBStationMeta_stationID] [int] NOT NULL,
[lnTableName] [nvarchar](max) NULL,
[dbTableName] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[tableID] ASC
))
GO
ALTER TABLE [dbo].[LNDBTableMeta] WITH CHECK ADD FOREIGN KEY([LNDBStationMeta_stationID])
REFERENCES [dbo].[LNDBStationMeta] ([stationID])
GO
--3rd Create table that holds columns metadata
CREATE TABLE [dbo].[LNDBColumnMeta](
[columnID] [int] IDENTITY(1,1) NOT NULL,
[LNDBStationMeta_stationID] [int] NOT NULL,
[LNDBTableMeta_tableID] [int] NOT NULL,
[lnColumnName] [nvarchar](max) NULL,
[dbColumnName] [nvarchar](max) NULL,
[process] [nvarchar](max) NULL,
[units] [nvarchar](max) NULL,
[dataType] [int] NULL,
[columnOrder] [bigint] NULL,
[active] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[columnID] ASC
))
GO
ALTER TABLE [dbo].[LNDBColumnMeta] WITH NOCHECK ADD FOREIGN KEY([LNDBStationMeta_stationID])
REFERENCES [dbo].[LNDBStationMeta] ([stationID])
GO
ALTER TABLE [dbo].[LNDBColumnMeta] WITH NOCHECK ADD FOREIGN KEY([LNDBTableMeta_tableID])
REFERENCES [dbo].[LNDBTableMeta] ([tableID])
GO
--4th Populate your metadata tables
--4a-Enter your station(s) name
INSERT INTO [dbo].[LNDBStationMeta]
([lnStationName])
SELECT 'Station1'
GO
--4b-Enter your station-table(s) name
INSERT INTO [dbo].[LNDBTableMeta]
([LNDBStationMeta_stationID]
,[lnTableName]
,[dbTableName])
SELECT (SELECT stationID FROM LNDBStationMeta WHERE lnStationName = 'Station1')
,'30'
,'Station1_30'
GO
INSERT INTO [dbo].[LNDBColumnMeta]
([LNDBStationMeta_stationID]
,[LNDBTableMeta_tableID]
,[lnColumnName]
,[dbColumnName]
,[process]
,[units]
,[dataType]
,[columnOrder]
,[active])
--Schema for the station-table 'Station1_30' - station1
SELECT (SELECT stationID FROM LNDBStationMeta WHERE lnStationName = 'Station1')
,(SELECT tableID FROM dbo.LNDBTableMeta WHERE dbTableName = 'Station1_30' )
,'TmStamp' ,'TmStamp' ,'TmStamp', '' ,14 ,0 ,1 --Required by RTMC
UNION
SELECT (SELECT stationID FROM LNDBStationMeta WHERE lnStationName = 'Station1')
,(SELECT tableID FROM dbo.LNDBTableMeta WHERE dbTableName = 'Station1_30' )
,'RecNum' ,'RecNum' ,'RecNum', '' ,3 ,0 ,1 --Required by RTMC
UNION
SELECT (SELECT stationID FROM LNDBStationMeta WHERE lnStationName = 'Station1')
,(SELECT tableID FROM dbo.LNDBTableMeta WHERE dbTableName = 'Station1_30' )
,'V1' ,'V1' ,'', '' ,27 ,0 ,1
UNION
SELECT (SELECT stationID FROM LNDBStationMeta WHERE lnStationName = 'Station1')
,(SELECT tableID FROM dbo.LNDBTableMeta WHERE dbTableName = 'Station1_30' )
,'V2' ,'V2' ,'', '' ,27 ,0 ,1
--4c-Your data table should look like this:
CREATE TABLE [dbo].[Station1_30](
[TmStamp] [datetime] NOT NULL,--Required by RTMC
[RecNum] [bigint] IDENTITY(1,1) NOT NULL,--Required by RTMC
[V1] [real] NULL,
[V2] [real] NULL
PRIMARY KEY CLUSTERED
(
[TmStamp] ASC,
[RecNum] ASC
))
GO
Note: Table name must be "Station1_30" to be recognized by RTMC software.
* Last updated by: Omar on 2/25/2012 @ 12:26 PM *
Fixed some formatting issue.
* Last updated by: Omar on 2/20/2012 @ 1:07 PM *