kdobsontn

Periodic Database Connection Check

8 posts in this topic

Posted (edited)

I would like to get some feedback or alternate suggestions in terms of monitoring a database connection.

The attachment here shows the current programming for the DB_Connect function block which requires the Execute input to constantly be ON.  From what I can tell, if the connection is dropped - let's just say the Ethernet cable is unplugged - this input will still be on, and according to the function block, still connected.

The Get1minClk is in place to continuously try establish this connection once powered on, but I cannot find any way to periodically check this connection without dropping it and reestablishing it.  My understanding is the _DBC_Status.Run only checks that the PLC service is running properly, but is there a way to monitor a heartbeat of the connection TO the database?  The only way I've found thus far is to drop the connection and reconnect.  But this would be detrimental to the system as it would possibly be reconnecting to the database while trying to write to out to it and hold up the show. 

As it stands in the second image, the Ethernet cable is unplugged, but the connection shows no errors.

Thanks and happy Friday!

-K

database_Connect.png

 

database_Connect_2.png

Edited by kdobsontn

Share this post


Link to post
Share on other sites

I ran into this same issue a couple of years ago. In my case, we didn't end up changing anything, as the data in the SQL database had to also be manually recorded anyway (due to trade requirements for weighing scales), and they get a daily report with the data from the previous day. So if the report was blank, then we knew someone had pulled a plug somewhere (or IT decided to mess with the firewalls.....again.....the joys of a remote SQL server).

 

At the time, I had considered setting up the PLC to read some data from the SQL database, and have it do that every couple of minutes. I would assume that if the connection dropped, then this would fault the read, and then some code could be setup to trigger something that would warn the operators that SQL was down. I never ended up doing this, but maybe this idea might work for you?

1 person likes this

Share this post


Link to post
Share on other sites

One way to do this is to look for data spooling. If you are reading/writing at fairly frequent intervals you can monitor the presence of spooling. If you are reading/writing very infrequently then you could setup a simple read from the database every few seconds.

Capture.PNG

1 person likes this

Share this post


Link to post
Share on other sites
22 hours ago, photovoltaic said:

One way to do this is to look for data spooling. If you are reading/writing at fairly frequent intervals you can monitor the presence of spooling. If you are reading/writing very infrequently then you could setup a simple read from the database every few seconds.

Capture.PNG

I thought about doing something like this, but I don't think the PLC-DB connection settings has spooling enabled (easy enough to change).

When in production, it's pretty frequent intervals (cycle time of maybe a minute or so), but the goal is to not let the machine run if there is no database connection - I don't want to run a part and finish the sequence only to find out the cable wasn't plugged in.  This may be the way to go in my case, but I do find it hard to believe there's no heartbeat monitor on something so simple.

Share this post


Link to post
Share on other sites
5 hours ago, kdobsontn said:

I thought about doing something like this, but I don't think the PLC-DB connection settings has spooling enabled (easy enough to change).

When in production, it's pretty frequent intervals (cycle time of maybe a minute or so), but the goal is to not let the machine run if there is no database connection - I don't want to run a part and finish the sequence only to find out the cable wasn't plugged in.  This may be the way to go in my case, but I do find it hard to believe there's no heartbeat monitor on something so simple.

Understandable. Perhaps you could include a simple read and spool count before you start as a permissive to proceed. If you don't want to use spooling you could look at the FailedCnt value which will increment if an instruction fails.

Share this post


Link to post
Share on other sites
19 hours ago, photovoltaic said:

Understandable. Perhaps you could include a simple read and spool count before you start as a permissive to proceed. If you don't want to use spooling you could look at the FailedCnt value which will increment if an instruction fails.

I can read the documentation here shortly, but does the spool count only happen when you try to execute a query or will that start once it looses connection? 

Share this post


Link to post
Share on other sites
1 hour ago, kdobsontn said:

I can read the documentation here shortly, but does the spool count only happen when you try to execute a query or will that start once it looses connection? 

It will count on a failed read or write. After playing around with my SQL server a bit I think the FailedCnt would be a better method

Share this post


Link to post
Share on other sites

Another thought that might work is to close the DB connection as the end of each cycle. So at the start of the cycle, the DB_Connect function block runs, creating the connection. The cycle runs its course. Then at the end it triggers a DB_Close function block to close the connection. This way, the connection is established each time the cycle is run.

This would be very situation dependant, for example I can't efficiently use that method on my own projects, as the PLC runs an entire plant with multiple different processes all sending SQL data, but if the PLC is controlling a machine doing repetitive cycles, it might work fine.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now