Connecting automation direct hardware to an SQL database

N

Thread Starter

Nathan

Automation Direct have these cool panels that can locally cache data then upload them to an FTP source (http://c-more.automationdirect.com/). This is cool, but my customer wants to make the data accessible in an SQL database. My idea was to run a PHP script (must use Windows in this application) with the Windows scheduler that:
1. Polls for the file
2. If it exists, parse it out, write the values to the DB, then delete the file.

I would normally use FactorySQL http://www.inductiveautomation.com/products/factorysql
but in this case the hardware device can handle the data logging and it has the added feature of caching the data before it sends it to the FTP server.

My main questions are these:
1. Is there some way to trigger a program/script/etc to run on an FTP upload (to get away from the polling)? Would this be easier in Linux? It certainly would be easier if those c-mores could POST to a web site.
2. I know how to write this script in PHP, but I was wondering if you guys can think of a cleaner, easier, or more straightforward way of accomplishing the task.

After investing the product info more closely, it looks like it has a built in FTP server and has the ability to email the data out periodically. Does this make getting it into an SQL database any easier? Thanks for your help in advance.
 
C

Curt Wuollet

Hi Nathan

>My main questions are these:
>1. Is there some way to trigger a program/script/etc to run on an FTP upload (to get away from the polling)? Would this be easier in Linux?
>

Yes. Either at or cron is frequently used for things like this and would be a one-liner. And there are more ftp versions, and they are more flexible, tftp comes to mind.

Or you could simply use Samba or NFS to make the file available on the other side without a batch transfer. Then use a local script to add records
as they become available. Using something like tail, but I disremember the name at the moment. Linux has an unbelievable number of these tools
to automate tasks as this is a big part of systems administration. I get kinds depressed in the Windows environment as there is little or nothing to compare. Not that you can get to your data anyway.

>2. I know how to write this script in PHP, but I was wondering if you guys can think of a cleaner, easier, or more straightforward way of accomplishing the task.
>
>After investing the product info more closely, it looks like it has a built in FTP server and has the ability to email the data out periodically. Does this make getting it into an SQL database any easier? Thanks for your help in advance.
>

Depends on which database. I would bet my weekend that the functionality already exists for MySQL and is free for the asking since it is used in millions of websites as part of the LAMP formula. You truly wouldn't believe how
much easier these tasks are to do when the tools are open and free and folks are happy to share. Tasks like that are a given, you can pretty much
assume that you have the tools and bits and pieces already on your Linux box.

Google mysql ftp. even if you use something else, the ideas may be useful. People should use the resources even if they are trapped in the proprietary world. The OSS community is sorta like this list. on steroids.

Regards
cww
 
C

Curt Wuollet

Oh, I forgot to mention that you should check with Host Engineering. They have done the software work for some of the AD stuff. they
might have some stuff already cooked up.

Regards
cww
 
M

Michael Griffin

I am not sure what might be the best overall approach for your application, but I will try to answer your questions however within the limits of what you have described.

> 1. Is there some way to trigger a program/script/etc to run on an
> FTP upload (to get away from the polling)? <

Reply - I'm not sure how this avoids polling if you are referring to communications polling. Only the AD panel knows if it has data, so anything the computer initiates would be in the form of some sort of communications poll.

As for triggering the scripts, MS-Windows (you mentioned you must use MS-Windows in this application) has what is called "scheduled tasks". This is somewhere in the "control panel" or "settings" menus. This scheduler is
similar in concept to "cron" but if I recall correctly has a minimum resolution of one hour. So, either your script must run continuously to provide its own scheduling (e.g. using "sleep" statements), or you are limited to running the script once per hour.

>Would this be easier in Linux?

With Linux, "cron" would give you a scheduling resolution down to the minute, so yes this would likely be easier in Linux if you need the script to run more frequently.

> 2. I know how to write this script in PHP, but I was wondering if you
> guys can think of a cleaner, easier, or more straightforward way
> of accomplishing the task. <

Python has a library (ftplib) which provides ftp client functions. There is likely something similar available for PHP as well. It is probably cleanest to do the FTP transfer, file parsing, and database connection all in one
script using a high level scripting language, rather than trying to do it with a shell script and separate utilities. I would think that Python is more suited to this, but if you are more familiar with PHP, you may wish to use that if it offers all the functionality you need.

> It certainly would be easier if those c-mores could POST to a web site. <

You could perhaps use the built in web server in the AD C-more to exchange data, provided you are not trying to store a number of records in the panel. The web page could be populated with current data which the receiving computer could parse out of the tags. This wouldn't be as "efficient" in terms of communications overhead, but it would likely require a lot less programming at the PLC or panel end, as it would be making use of a lot of built-in features. You would need to use a library which acts as an http client, instead of an ftp client. This would allow you to keep as much of the sophistication at the computer end, rather than distributing it at both ends.

This of course is only relevant provided you are only trying to retrieve current data from the PLC.
 
"polling" was referring to the fact the the cron job or windows scheduler checks to see if the file's there every time it runs. This device has the capability to "upload" to an FTP server whenever it's ready to send a batch of data.

If the device could do an HTML POST, then a PHP, perl, python, whatever, script could perform the neccessary action.

I was really wondering if there's a way to make an FTP server "do something" when it recieves a file. This may include a cron job that watches the directory that the file will reside in on a frequent basis (still "polling" for the information - ie, not event driven)
 
C

Curt Wuollet

I would expect that with a "third party" ftp program, one could watch the retval and act accordingly if a file was transferred or not. So much depends on what is supported by the "black box" part of the system. We mildly disagree that one would need a programming language for this, but if he has to use Windows his choices are severely limited and the point is moot. Php or Python would be easier than getting decent scripting and utilities on a Windows box.

One thing you have to do with any of these special interfaces is document what is going on. Otherwise some "Windows Guru" will load a new os or run a "recovery disk" and things will stop working, possibly forever, if they can't contact you. Floobydust is expected on other systems, but Windows stuff is expected to be "box stock" so you can reload as needed.

Regards
cww
 
M

Michael Griffin

I did not realise that the AD panel could act as an FTP client to upload to an FTP server. I had assumed it was an FTP server only. I understand your questions better now.

As far as what can be done with the FTP server itself, ProFTPd has an optional module called "mod_exec". This is described as "The mod_exec module can be used to execute external programs or scripts at various points in the process of handling FTP commands".

"mod_exec" is not a standard part of ProFTPd, and must be installed separately. "mod_exec" has a number of restrictions imposed on it however, as triggering scripts based on FTP events presents some serious security concerns. Your scripts will have to work within the security limits imposed on them. I haven't used "mod_exec", so I can't offer you any advice on it.

There are also features in Linux itself for detecting when a directory has changed and notifying a program which has registered interest in that directory. I don't unfortunately recall what this feature is called.

The "cron" approach is probably still easier than either of the above though.
 
A

Andrey Romanenko

Michael,

Just to complement your response:

> There are also features in Linux itself for detecting when a directory has
> changed and notifying a program which has registered interest in that
> directory. I don't unfortunately recall what this feature is called. <

NAME
famd - The File Alteration Monitor (FAM) daemon

SYNOPSIS
famd [-CdflLv] [-C conffile] [-p prog.vers] [-t period] [-T timeout]

DESCRIPTION
FAM, the File Alteration Monitor, is a subsystem that applications can use to be notified when specific files or directories are changed. It is intended as a replacement for mechanisms such as poll and select.

FAM comes in two parts: famd, the daemon that listens for requests and provides notifications, and libfam a library that client applications can use to communicate with FAM. For further information on libfam, see the fam(3) manual page.

Andrey Romanenko
 
C
Or for a single file you can simply use a named pipe and tail -f.

Many, many ways to do this under Linux.

Regards
cww
 
i have a similar problem. i have to connect a PLC by Mitsubishi company used for a tablet coating machine. i tried connecting the PLC to computer and checking it with the scheduler from MS but it was not detected.

i have to collect the data of the ongoing process and record it in SQL. for the interfacing part i'm using VB dot net.

please help me find a way out.
 
Top