FactorySQL supports automatic data caching when it loses connection to the SQL database, but is still communicating with the PLC. If you have a central database/FactoryPMI setup with remote sites over a questionable connection the recommended solution is to run FactorySQL at each location, logging data centrally. If a network connection drops, FactorySQL will continue to cache data until the connection resumes.
This post will describe a method of using a single central copy of FactorySQL, caching the data in the PLC. This method works if you have a fairly reliable network connection and don’t need to log much data rapidly (or can tolerate a short cache history). We will set up a queue in the PLC. The PLC will trigger every data logging event. This approach takes some work, but could save you money in certain situations. It does have the benefit of the PLC deciding when to log data. You could set up a deadband change instead of a fixed time interval or use multiple conditions.
Suppose we have 2 values that we wish to log: temp and level. In this example we cache 49 rows, using array indices 0 through 49, with zero being our special case - these will be the only registers that FactorySQL reads from.
The PLC first needs to be able to store a datetime. Storing as a string is ok as long as you can emulate the following format yyyy-MM-dd HH:mm:ss.
We now add a single boolean register, trigger. When 1, FactorySQL should perform a read then reset the value to 0. We also add an integer, pointer, that will take a value from 0-49 to tell the PLC where it’s at in the queue.
We then create our queue. It consists of array(s) that contains 50 rows of our 3 columns. We’ll denote these temp[], level[], and date[].
Setting up the PLC
pointer begins at 0, indicating that there is no new data for FactorySQL to log. When the PLC wishes to log data, it performs an encue operation. This consists of:
- copying the current value of temp, level, and date to temp[pointer], level[pointer], and date[pointer].
- set trigger=1
- if pointer <= 48 increment it
- otherwise, set values[49] and shift all values up from 48 to 0 value[x]=value[x+1].
A decue operation occurs whenever trigger falls from 1 to 0. (FactorySQL just did a read). If pointer > 0, do the following:
- shift all values up from pointer to zero. Ie if pointer is 25, shift up values[25 to 0]. It shouldn’t hurt anything if you shift all registers.
- decrement pointer
Setting up FSQL
- The FactorySQL will have the following (read-only) OPC items: temp[0], level[0], date[0], and trigger.
- The group will be set to execute on a trigger, trigger on trigger, only execute once on a trigger, and reset trigger after execution. Trigger condition is “is>0 reset 0” (default).
- Create an action item, logdata, that is an SQL Query. Uncheck “Execute every update interval”. The query should be an INSERT query that logs historical data into your table. It might look something like this:
INSERT INTO history (t_stamp, temp, level) VALUES ({t_stamp},{temp},{level})
Conceptually:
- The PLC array stores the last x values with date/time.
- The PLC tells FSQL when to log the data.
- The PLC caches up data even when FSQL isn’t immediately reading it
- FSQL logs the data and tells the PLC by clearing the trigger bit
- The PLC removes the logged data by shifting all the stored values.
Please keep in mind that this is an idea of an approach, not a tutorial. You will need to address things like initial conditions, the pointer being off, or a PC reboot. You will also need to get the date/string formatting correct. This approach will use an entire array of PLC memory that wouldn’t be needed with the simple drag/drop FSQL logging. My recommendation is to use a copy of FSQL at each site for this type of setup.