Using Indusoft VbSctript to insert a line into MySql once every five seconds

I have been searching for a way to insert a line into a MySql database once every five seconds.

I was using Advanced HMI and the timer tick function which worked perfect.

Now with the VbScript, i can get the data to insert, but it keeps looping and inserting continuously getting a duplicate primary key error.

So I have two questions:

Is there a function like timer tick that will send the insert function only once?

If I use a timer in the PLC or the second tag in Indusoft, how do I get the insert to only fire once?

thanks in advance....

If you have the table setup correctly you shouldn't get primary key errors. Select auto increment, let the database handle this.

As far as your script, might want to show what you have so others can help.

I have not done this is VBScript but have done it using Trends. We created triggers using Scheduler for 60 second, 10 second.
Event:Change Trigger:second Tag:$seconds10 Expression:NOT $seconds10 Disable:Mod($second,10)<>0.
You should be able to do the same and use it in VBscript to trigger at a the required time interval.

Simple, but inefficient, solution: [ON DUPLICATE KEY UPDATE] or [INSERT IGNORE] or [REPLACE]. Cf. here. Inefficiency comes from making possibly many SQL calls during each fifth second; assumes this is MySQL.

I would guess that the problem is that OP is looking for a one-shot, requiring VBScript to remember the last timestamp inserted, but each VBScript execution is triggered ab initio, so there is no way for VBScript to remember anything. So perhaps the trigger should be a one-shot in the PLC, which means the "every five seconds" calculation would happen in the PLC, not in VBScript.


P.S. another option would be for the VBScript to write the timestamp either to Web Studio or the PLC; that would allow the code that detects every fifth second to remain in VBScript; the downside is extra communication during that second, but if it happens within the Web Studio it might be cheap.

How are you calling the VBScript code? Is it something like

• RunVBScript("EveryFiveSeconds()")?

I wonder if something like

• RunVBScript("$LastTimestamp=EveryFiveSconds($LastTimeStamp)")

would work?

It should not be this difficult.

Here's a < 2 minute video showing how to do this.

https://youtu.be/0WL-V1Ig3QM

ndzied1 said:

Here's a < 2 minute video showing how to do this.

https://youtu.be/0WL-V1Ig3QM

Click to expand...

I'll shut up now.

drbitboy said:

I'll shut up now.

Click to expand...

It's hard to compete with a Youtube video

Picture = 1000 words
Youtube video =??

That was perfect thanks again Norm

I will have to send you a red cape...

I noticed there is some "drift" on the time stamps (see pic)
I have tried to add zero milliseconds to the timing of the scheduler, but no luck....
Is there any way to tighten up the insert time?

thanks again

If you need that tight of control, you should consider to use the plc. Use a fifo and unload when you have data.

lunenburger said:

Is there any way to tighten up the insert time?

Click to expand...

Caveats

• there will always be drift, real if not in the timestamp; this is not astronomy or spacecraft navigation, and devices' internal clocks are not that accurate.
• If the data are sent from the PLC/HMI but the timestamp is made by the MySQL server (CURRENT_TIME() in the MySQL statement), then there will be drift between the clocks.

Yes, but first we need to know how the timing is implemented.

For example, if a self-resetting PLC timer has a preset time of 300s, then there will be some fraction of a scan time (a few ms) after the 300s has expired but the program has not yet checked the timer. One way to deal with this would be to determine the mean overage and compensate for it in the preset (e.g. use a preset of 299,998ms). This would still drift, but less so.

Another way would be to set the timer preset to 400,000ms and never reset the timer, but when it exceeds 299,999ms subtract 300,000ms from the accumulator and send the data to MySQL.

A third approach would be to get the whole seconds from the device's real-time clock, and send the data when the calculation of [that seconds value modulo 5] is 0.

A fourth approach would be to use the internal, free-running counter that is the device's internal clock. For example, some devices have a 64-bit counter that counts microseconds. Every time that crosses a 5,000,000-count boundary would be the trigger to look for.

Those last two approaches have the advantage of generating a drift-free timestamp value, while hiding any actual drift, if the source clock is automatically adjusted from an NTP server.

Here is some testing I did with some of the methods above.

It's funny I have the exact same MySql database and data using AdvancedHMI and a CompactLogix PLC.
I use the timer tick function with a 5 second time, it will run for ten hours with no drift at all.

For this project I had to go with a S7-1200 PLC, so I couldn't use AdvancedHMI
Hence the learning curve I am having with the Indusoft and VbScript....

I thought there was a driver for Siemens and ahmi?