mySQL for Data Storage

I haven’t registered the software yet; but, I will do it before the end of the 30-day trial - and, the answer to this question won’t change that!

Is there any chance you’ll be adding the option of storing the data in a “real” database (mySQL comes to mind and it’s great and basically free…)? (I’m a SysAdmin/Web Application Programmer for a Health Plan Pharmacy Network.)

If you don’t think the database “thing” will happen, I’ll just try pulling the rawdata into a mySQL database every minute or so…

By the way - great software! It’s a little hard to find your way around at first (and I keep finding things); but, it’ll let you do just about anything!

Thanks.

Dwain

P.S. Here’s the weather page I’ve created so far (keep in mind, I’ve only had the AAG v.3.0 since 7/18): http://home.net-router.com/wx/wx2.html

I wrote a quickie wx-local file to create a comma-delimeted file that’s ftp’d to the linux server every minute. Then I wrote a small PERL script that’s called via crontab every minute that inserts the data into a mySQL database. WORKS!

Thanks.

Dwain

Dwain

Would you consider posting the wx-local and PERL scripts here for others to use? I’d like to get WD data into MySQL to play around with other presentational methods, so I’d be interested in how you did it. I’ve not investigated this for myself yet because I’ve got a couple of other biggish projects on the go at the moment.

I thought you’d never ask!

Since I’m still playing with it, I only setup the wxlocal file to use a few of the fields you have available; but, you will get the idea:

wxlocal3.html:[code:1]
%temp%,%heati%,%windch%,%hum%,%dirlabel%,%avgspd%,%gstspd%,%dew%,%baro%,%pressuretrendname%,%dayrnusa%,%maxrain/minlasthour%,%indoortemp%
[/code:1]
That’s it! Just remember, it needs to be a single line…

The PERL script isn’t pretty. It was “quick and dirty” to get it to work. When I get a chance I’ll clean it up…

wx_data_loader.pl:

[code:1]
#!/usr/bin/perl

use DBI;

$| = 1;

$script_path = “/home/httpd/home.net-router.com/cgi-bin/wx”;
$file_path = “/home/httpd/home.net-router.com/wx”;
@reformat_list = (“0”,“1”,“2”,“3”,“5”,“6”,“7”,“8”,“10”,“11”,“12”);

&ConnectToDB;
&LoadWxDataFile;
&WriteWxData;
exit 0;

sub ConnectToDB
{
$dbh = DBI->connect(“DBI:mysql:database=wx”,“username”,“password”, {RaiseError => 1});
}

sub LoadWxDataFile
{
print “Loading Wx Data File…
\n”;
open (WXFILE, “$file_path/wx3.html”);
@wx_filedata = ;
close WXFILE;
}

sub WriteWxData
{
foreach $line (@wx_filedata) {
@wx_data = split(/,/,$line);

	if ($wx_data[2] ne "") {

		foreach $entry (@reformat_list) {
			$wx_data[$entry] =~ s/[^0-9\.]//g;
		}

		chop($wx_data[8]);
		chop($wx_data[10]);
		chop($wx_data[11]);

		eval {
			$dbh->do(" insert into current set wxdatetime = SYSDATE(), temp = \"$wx_data[0]\", windchill = \"$wx_data[1]\", heatindex = \"$wx_data[2]\", humidity = \"$wx_data[3]\", winddir = \"$wx_data[4]\", windspeed = \"$wx_data[5]\", windgust = \"$wx_data[6]\", dewpt = \"$wx_data[7]\", barometer = \"$wx_data[8]\", barotrend = \"$wx_data[9]\", todayrain = \"$wx_data[10]\", maxrainlasthour = \"$wx_data[11]\", extratemp1 = \"$wx_data[12]\"   ") };
		if ($@) {
			print "

WARNING: Database Error: $@
sub WriteWxData: insert into wx
\n";
&DatabaseError;
exit 0;
}
}
}
}
[/code:1]

[EDIT: It looks like the message board is trying to mangle my source…hope it still makes sense!]

And, that’s it for the PERL script. Then, just setup a cronjob to run every minute (or whatever) to run the script.

The reformat_list array is for the fields you want to force to numeric only - i.e. remove the &F, etc.

If anyone wants a copy via email, just let me know and I’ll zip up both of them for you. (I will make the source available on my weather website when I feel it’s more complete.)

ALSO, I’ve setup an easy access URL to my weather page:
http://wx.coufal.net

If you have any questions or need any help, just let me know!

Dwain Coufal

When I register the software, how many software upgrades are allowed?

Since we’re currently on version 9.85e, do I pay again for version 10 or 11?

Just curious…

Thanks.

Dwain Coufal

hi
its a life time of free upgrades

the problem with borland (delphi) database is you need the BDE, which is hefty
but i have come across slimmer mysql components …i will investigate those
also, i should be more easily be able to add mysql to the linux vers of wd

i have produced a data base program before (for a cancer project for a hospital)

[EDIT: It looks like the message board is trying to mangle my source...hope it still makes sense!]

Use the Code button round chunks of code to keep the formatting and other constructs looking like you want them to.

I’ll come back to your code when I’ve finished what I’m just embarking on…moving this domain/forum (and a few others) onto a new server and then back again to the current server (there is method in my madness…honest!).

We all love Linux (Don’t we?) and we all love FREE stuff… but how about making it so we could connect to other databases? If we setup a DSN in Windows to connect to a .mdb file or to SQL Server, it would be WAY COOL to have WD store it’s data there in addition to, or instead of in a local data file. Somebody could easily create a standard .mdb file in access with all the right tables and columns and we could just distribute it for download with WD… then all we would have to do is create the DSN and WD should be able to connect to that database with no problem…

I’d thought about trying to do this on my own, but I’d rather stay with WD and not have two different processes going at the same time, and I’d guess it’s not too difficult to implement in Windows…

What do the rest of you out there think?

mySQL has a Windows ODBC driver… I use it all the time at work. Your idea would work with mine - just need to know the db layout to create the schema.

P.S. The db I’m populating with the code I listed above currently has 11,558 rows! Not bad since 7/28/03…

P.P.S. MAN, I wish it would rain! Hasn’t since I put the system together… I want to see the counter count!!!

i have found some mysql components,…
i will see what i can do…

Brian… do you have MS Access? I figure since most of us are running Windows, we could just make a generic .mdb file available for download… I can try to throw out such a file, if you need it… I’ve even got an extra copy of Access here if you would like me to mail it to you. I figure with ODBC we could connect to any database (SQL, Access or Mysql) as long as WD new what the database layout was… Once the data is in a database we could do all kinds of things with it…

Let me know if you need or even want MS Access…

-Robert

i have office, no worries ther
i found this:
http://www.delphifaq.net/modules.php?op=modload&name=FAQ&op=view&id=185

looks easy to use
what do you think?

I guess I could install Mysql on my Windows server platform… should work the same??

From what I see in that link you mention, it looks pretty straight forward… my experience with MySql is that the hardest part is just setting it up… but what in the Linux/Unix world isn’t suffering from the same thing?

From there, I suppose we could develop via odbc to the mysql server (in the windows world)… I’ll download Mysql today and take a look at it…

Just what you needed… some new project to look at!

You’re probably better off using ODBC for a windows environment for ease of portability across databases…

But, then the only programming I do for windows is in VB. Delphi may very well be different and I yield to your knowledge on that matter…

I’ve tried mySQL on windows and it seemed quite a bit more difficult to install than it was on Linux. It did work - just seemed more difficult. (And, before you say it - I do 85+% of my work in Windows. I just feel that UNIX has it’s place anytime you need the stability of a server. And, I prefer coding in PERL than VB any day of the week!)

Brian - take a break. Certainly don’t do this just for me! My little cron job is working great. Take a vacation! Oh, and HAPPY BIRTHDAY! (I know, I know - a little late, right?)

You are right… I install MYSql on my WIndows box… installed great, runs great, management utilities work… but I can tell you it is NOT an easy or trivial thing to develop any type of application, web application, or web service to connect to it from the Windows world. Setting up ODBC using a system DSN to a MSAccess or SQL Server database gives Windows users and developers all the tools they need… and Unix users can still connect via ODBC to windows databases…

If I were a Linux guy, I’d probably not have much trouble with it, but as I’m a Windows guy and an aspiring .Net programmer… all I can say about MySQL is “YIKES”!

But perhaps using ODBC in Delphi is a tough task… ODBC has been around a long time, I’d ‘guess’ there is some functionality for it… but I too defer to Brian’s expertise on the subject!

I’m trying to write a DTS package to pull data from a custom webpage generated by WD… should work… so don’t knock yourself out Brian! :slight_smile:

I ran accross this site…

http://gd.tuwien.ac.at/softeng/delphi/newl/d70/f001_001.htm

Which has a product called “KAAdo”… claims it can connect to just about anything Microsoft…

There must be more out there… a lot depends on what version of Delphi is being used…

Just looking and sharin’

:slight_smile:

and Unix users can still connect via ODBC to windows databases..

It all depends what you’re trying to do! What I’d like to do is have a database on my Linux server (the one running this forum which is 200 miles from my home) and have it updated from my home PC running WD. My home PCs are behind a firewall, so the easiest way is for the WD PC to update the MySQL database on the remote server rather than the other way round.

Something like ADODB http://php.weblogs.com/ADODB might be more useful and being less Microsoft-centric might make it more generally useful. This isn’t said from the perspective of MS-bashing. I’ll happily use MS technology where it makes sense, but some situations call for different solutions.

The problem is… almost nobody uses or has access to a MySql server… if you want to download it, you can, but getting it to function as anything other than a storage medium is not trivial… I’d like to be able to not only store the data, but write applications against it… a web service perhaps, or a task bar application that scrolls current conditions… I’d like to create a web page that allows me to input a day and time and return the weather at that moment… or perhaps a range… perhaps to return the average windspeed for the last 63 days… all of that requires some type of connectivity to the database through the webserver… I know I could run apache, and PHP and mysql on my WIndows system, or build a whole new computer for it… but, realizing most of us don’t use or know our way around that stuff, using IIS, MSAccess, SQL Server and ODBC… and VB, VB.NET… or any of the MS Platform based development tools would make doing such a thing much easier. My system currently runs both SQL Server and MSAccess… on the same machine as is running WD… all I’d like to see is rather than WD writing it’s data to it’s own log file, have the option to write it to a different log file, either instead of, or in addition to it’s current logging… and using a system DSN via ODBC would be a no brainer for doing it…

It’s obviously not a show stopper, and would just be a cool feature… It took me just a few minutes to create a wxlocal.html file with all the custom tags, and then a DTS package from SQL server that imports that data into the database, but that is just an extra layer of stuff… would be very cool to just have WD dump it in there itself.

If Brian decides to engage in this concept at all (who would blame him if he didn’t!!), and decides to support only Linux databases, I’d be happy to write a little VB application that would grab data from a wxlocal file and dump it into a .mdb file… run it as a service or standalone… and make that available for WD users to download…

I guess the long and the short of it is as you said… it depends on what you want to do with it… if you plan only to use the feature to ship your data out to an external database for redundancy, that’s cool… but you could already do that by ftping the datalogs offsite… if you want to develop applications or websites against the data in that database, then you would be limited to only Linux tools in doing so… and that isn’t practical (yet anyhow)…

So, Brian… the debate continues… but the reality of it is that there are already ways to do all of this, so none of what we are discussing is very important… don’t worry about it… we can ftp data out, then cron it into a MySql database using perl, or we can write a vb application to dump it into any type of MS database, or use DTS and have SQL server import it by itself…

Don’t let our desire to make things easier for us, make things harder for you!!!

I’ve stepped off my soapbox now
:slight_smile:

I just want to clarify my previous comments regarding mySQL.

Setup/Installation of mySQL on Windows is a pain in the @$$ - at least it is if you want it to function properly, with decent stability and security.

Setup/Installation of mySQL on a Unix box is quite a nice experience. I’ve used mySQL from v.2.63 and installed it on Redhat 6.2 - 9.0 – and never had any real difficulties. The DBD/DBI’s for perl that are available on mySQL’s website are GREAT and I have never had a single problem with them. The ODBC on mySQL’s website for use on Windows platforms allows you to easily connect your Windows app to any mySQL server (Windows or Unix).

Just as an example of the capabilities of mySQL: I have one database at work that generates 5,000,000 records/rows in just one of the database’s tables every night, in less than 45 minutes (this inlcludes the time required to update 5 other tables that contain in excess of 500,000 records each). The database created each night is over 5GB in size!

STUNTMAN - I agree with you 100% - BRIAN, don’t do this unless you’re doing it for yourself. My perl/cron implementation appears to be working quite well. There aren’t many people out there that have multiple servers running in their homes… nor many that would want to (including my wife - ha!)

My ultimate goal is to let this system control my sprinkler system… It should cut down on the water used and keep my plants happy! Then comes the AC/Heater…

As an aside: I sure would like to put that color camera I have up on the roof - but, yesterday’s high was almost 112 degrees!! That would put the shingled roof somewhere around 130+ deg!

Dwain

i have just discovered Kylix (delphi for linux) has Dbexpress, which hhas the sql components, and looks relatively easy to use…
but those components are not in my verison of delphi
how do setup a mysql or sql in linux so i can test?