Thursday, July 31, 2008

CSV Logging in FactorySQL

As of version 4.2.3, FactorySQL can now be used with the CSV ODBC driver that is included with Windows. While we strongly advocate the use of databases instead of CSV when possible, sometimes it is useful or necessary to log simple data directly to a text file. The following is a short How-To illustrating the process.

Step 1: Create ODBC data source
Go to the ODBC administrator by going to Control Panel->Administrative Tools->Data Sources (ODBC) in Windows, or by clicking the appropriate button on the FactorySQL DSN connection configuration window.

Create a new System DSN datasource using the "Microsoft Text Driver".







Next, a config screen for the driver will appear:

It is recommended that you deselect the "Use Current Directory" option, and select a specific folder, so that you know exactly where the file will be. This is important, because you'll have to manually create the file there in a second.

Step 2: Configure the FactorySQL Data Connection

Create a new data connection under "Data Connection Settings", select DSN Connection under "Connection Type", and select the DSN that you just created.

To make things easy, just select the "Access" translator from the translator selector list. In reality it doesn't really get used, but left on automatic we would get a "Translator Not Found" error later, because we're using a different driver type.


Step 3: Set up the group/file
A few things need to be done:

1) Items need to be added to the group. Their column names will relate directly to the column names in the text file.

2) The text file must be manually created (for example, by right-clicking in the folder and selecting "New->Text File" from the Windows context menu). The text file must be named "tablename.txt", without quotes, where tablename is the same as the table name setting on the FactorySQL group.

3) The text file must be edited to include the column names in the first line. This is how the ODBC driver will like the FactorySQL items with the correct position in the file.

The following image illustrates all of these points:




Step 4: Start Logging
Hit the "Start" button to start the group. You'll be presented with a screen stating that the table could not be verified:

This is normal- the CSV odbc driver doesn't support SELECT statements or schema information. Just hit "Yes" to bypass the check and continue.






And... voila! You should now have data being logged to your text file!

If you have any questions or comments, please feel free to post them to this thread.

On small note: You may be thinking "well, if I have to manually click 'yes' to start the group, does that mean it won't start on its own in the case of a reboot?". The answer is that the table check is a frontend-only feature- the group will start correctly on its own and without any manual intervention when the FactorySQL service is started.

2 comments:

Anonymous said...

I have a reason to use the CSV option, and it works great. Now how would I utilize the "Delete records older than:" option?

It seems like even if I set it to delete records older than 1 minute, it ignores it and keeps logging.

Am I missing a step, or some way to do this?

Thanks!

plant Automation Mart said...

it is a really nice blog, thanks a lot for sharing with us
plant automation