SQL and PMI questions

I have been reading over these news groups and have come across some information that I would like clarification on.

  1. In one group it was mentioned that it is possible to detect if the communications is lost between your two programs and a remote SQL connection. Could you elaborate on how this would be done?

  2. Referencing number 1, lets say the communications is lost and your software has detected it, I am assuming that the best approach would be to have the data logged to the local MySQL database then when communications is re-established push this data over to the main SQL server. Would this be a correct assumption?

  3. Is it more difficult to detect the loss of communications or the re-establisment of communication to the SQL server?

  4. Do you have any examples of best line approaches for doing this?

Now onto the database connections issues:

  1. In the databases that Factory SQL creates there is usually a key column with the _NDX ending to the name. Is this created by the Factory SQL program?

  2. Can this column be used as the key in looking up or retrieving information from a database?

I ask these question because I need a good steady key column for two of my databases. At first I had thought of using a number being sent from the PLC, but this proved to be a problem if communications to the PLC was lost. So my next attempt was to use the ID number comming right from KepServer, however this has proven to be a slight problem in that it is a string not an integer. If it were an integer it would be real easy. So I thought well why not try using the _NDX column but I first needed to know where that information was comming from and who created it. Or if you have a better approach I would greatly appreciate hearing any insight.

Now about screen numbering and securing.

Lets say one starts a new project. They get partway into the project and realize that the default startup screen they created is not the one they want to use, they would rather use another screen as the default startup screen. Can this be changed rather easily? I did read in one of these groups that it could be changed but if you made any changes to your screens the software would default back to the origional choosen one. I am unsure the dating of the post so I dont know if this is something that has been fixed or is being worked on or what so I thought I would ask for an update.

Thank you and have a great day.

Hi-

To sum up the first few questions: Connections to the DB are managed pretty autonomously by both products. That is, if they go down, there’s nothing you need to do to get them reconnected. Both products check bad connections on a timer, around 10 seconds.

In terms of logging and connection loss- FactorySQL includes built-in data caching. So, for most of your purposes, factorysql will handle this automatically. When the connection is lost, data will be logged to an internal database, and then entered into the main db when it’s back up- time sync’d and all.

So, with that out of the way, is it still necessary to detect that the db connection is down? There’s probably a few things you could do, especially in PMI, but it might not be worth the effort.

Now, about the ndx column:
Yes, this column is precisely what you’re looking for. Each table that FSQL creates has “primary key index column”, by default with the name {tablename}_ndx. Essentially, what this means is that every row will have it’s own value (generated by the DB), and it’s “indexed”, so it’s very quick to query against. You definitely can (and should) use this any time you need to keep track of/query rows.

Finally, about the default/startup windows- yes, this is very easy. Simply right-click on any window and choose “open on startup”. On the other window, right click and choose “don’t open on startup”. Note that you can have multiple windows set to start on startup.

Hope this helps,

Colby, thanks for the information. I do have one more question about this Index column which you say FSQL creates.

Lets say I have some of my folders and groups defined. I have the index numbers of 1 for machine 1, 2 for machine 2 and 3 for machine 3 but then I jump upto machine 26 because right now we are in the begining phase of this project. Now the funny thing is I have the data comming back for machines 1, 2 and 3 but it will not put in the index a 26. Do I need to create the database first with the static index numbers of 1 thru 99 or should the software create a index 26 in the database?

[quote=“Colby.Clegg”]Hi-

To sum up the first few questions: Connections to the DB are managed pretty autonomously by both products. That is, if they go down, there’s nothing you need to do to get them reconnected. Both products check bad connections on a timer, around 10 seconds.

In terms of logging and connection loss- FactorySQL includes built-in data caching. So, for most of your purposes, factorysql will handle this automatically. When the connection is lost, data will be logged to an internal database, and then entered into the main db when it’s back up- time sync’d and all.

So, with that out of the way, is it still necessary to detect that the db connection is down? There’s probably a few things you could do, especially in PMI, but it might not be worth the effort.

Now, about the ndx column:
Yes, this column is precisely what you’re looking for. Each table that FSQL creates has “primary key index column”, by default with the name {tablename}_ndx. Essentially, what this means is that every row will have it’s own value (generated by the DB), and it’s “indexed”, so it’s very quick to query against. You definitely can (and should) use this any time you need to keep track of/query rows.

Finally, about the default/startup windows- yes, this is very easy. Simply right-click on any window and choose “open on startup”. On the other window, right click and choose “don’t open on startup”. Note that you can have multiple windows set to start on startup.

Hope this helps,[/quote]

The index that we’re referring to (tablename_ndx) is an autoincrementing primary key that uses the database to keep track of the numbers. In the case of keeping your own machine numbers you would want to add your own column to the table that FSQL creates. It will create the row for you if you specify WHERE “{your_row}={your_value}” in the groups custom WHERE clause. I’ll give you an example.

A common application with multiple templated machines is to have an FSQL group for each machine. Add a “machine_number” column to the table. Each FactorySQL group would have a custom “WHERE clause”, for example WHERE “machine_number = 26” for machine/group 26. The catch is that the item names (columns in the table) should match up. It’s OK if certain machines have extra values - just make sure to show it properly on the HMI side. The really nice benefit of this approach is that a machine is represented by a row. You can easily set up a FactoryPMI screen that is one window that could be used for all 26 machines. The entire screen’s data is based on a query that reads all the selected machines info with a “WHERE machine_number = {my_selected_machine}”, where {my_selected_machine} is a FactoryPMI dynamic property that the user sets via: buttons, a drop down list, etc.

This approach allows you to only create one screen for all similar machines. Adding a machine is simply a matter of adding a new FactorySQL group. You can even populate the users list of choices in a dropdown based on the rows that are in that table. You can see a similar example in this FactoryPMI quick start video.

Yes I have watched the video which is where I came up with the idea. However a small minor glitch occurs if a machine is taken offline. It reports back all NULL information and starts adding to the database instead of updating the database. What I am trying to accomplish is finding something that does not change if communications are lost and using this as my key index field.

The idea that I had come up with was to use the _DeviceID, this is a value that is set in KepServer and even when communications with the PLC is lost it still maintains its information. The problem with this tag is the fact that it comes across as an ASCII not as an integer.

If I use the machine_num field it works quite well, that is because this number come across as an integer. But like I said if communications is lost then this integer reports back as a NULL and it adds another row of information in the database. This is why I want to use something that does not change, a fixed value sort to say.

For some reason though when I used the _DeviceID tag it did not record the data in the database. Was it because it was ASCII? Dont know. All I do know is when I used it it always left that field blank. SO I had nothing to compare things with.

I hope this makes sense. You see sometime a machine is removed from the production floor to either go out and be retrofitted or rewired. So if that machine is gone I dont want it adding anything to the database.

[quote=“nathan”]The index that we’re referring to (tablename_ndx) is an autoincrementing primary key that uses the database to keep track of the numbers. In the case of keeping your own machine numbers you would want to add your own column to the table that FSQL creates. It will create the row for you if you specify “WHERE {your_row}={your_value}” in the groups custom WHERE clause. I’ll give you an example.

A common application with multiple templated machines is to have an FSQL group for each machine. Add a “machine_number” column to the table. Each FactorySQL group would have a custom “WHERE clause”, for example “WHERE machine_number = 26” for machine/group 26. The catch is that the item names (columns in the table) should match up. It’s OK if certain machines have extra values - just make sure to show it properly on the HMI side. The really nice benefit of this approach is that a machine is represented by a row. You can easily set up a FactoryPMI screen that is one window that could be used for all 26 machines. The entire screen’s data is based on a query that reads all the selected machines info with a “WHERE machine_number = {my_selected_machine}”, where {my_selected_machine} is a FactoryPMI dynamic property that the user sets via: buttons, a drop down list, etc.

This approach allows you to only create one screen for all similar machines. Adding a machine is simply a matter of adding a new FactorySQL group. You can even populate the users list of choices in a dropdown based on the rows that are in that table. You can see a similar example in this FactoryPMI quick start video.[/quote]

I think your FactorySQL group setup can probably be simplified. You should be able to take a machine down without creating problems. To address your questions:

  1. Make sure that the group isn’t set to “Insert new record” - that’s how you set up historical data logging. You should set it to “update/select custom record” then in the WHERE field type your condition. Keep in mind that this condition applies with respect to the database. You do not need Kepware/PLC values to make this work. The FactorySQL group “knows” which machine you’re referring to by OPC tag paths. It determines which database row belongs to it with the WHERE clause.

  2. I think that Kepware is behaving properly by reporting NULL values when a machine is taken offline. You will see a different value in the “OPC Quality Code”. Check “store OPC Quality code” in the group property. Then use that value in FactoryPMI to show the user that the machine is down and disable it.

  3. I don’t know how FactorySQL could be adding to the database instead of updating it based on NULL values. Groups strictly either keep INSERTING records or UPDATING the same one, based on that group property. If the value is NULL it will write that in either case.

Ok let me simplify things a bit.

  1. In FSQL I do have the where condition set up. SO that is correct. I used the same type of scenario as shown in the Quick start video.

However as I mentioned when a connection is lost KepServer reports back the word NULL in each of the fields, not 0 (zero), it reports back the word NULL. When it does this something weird usually happens. And that weird thing is I get error messages becuase it wrote the word NULL under the machine ID number. SO now each time this group trys to run and references the key of machine ID you software throws and error message. Why, because the machine number has been over written and is now a NULL and it can not find that keyed number. Then when the machine comes back online and starts reporting information back it adds in a new row of information. Why? Because there is currently not a 26 or whatever number I am using in the database.

An idea I came up with would be make a database thru SQL not thru FSQL dont address the machine ID number in any of the FSQL fields. That way maybe the machine number will remain static and unchanged. What do you think?

[quote=“nathan”]I think your FactorySQL group setup can probably be simplified. You should be able to take a machine down without creating problems. To address your questions:

  1. Make sure that the group isn’t set to “Insert new record” - that’s how you set up historical data logging. You should set it to “update/select custom record” then in the WHERE field type your condition. Keep in mind that this condition applies with respect to the database. You do not need Kepware/PLC values to make this work. The FactorySQL group “knows” which machine you’re referring to by OPC tag paths. It determines which database row belongs to it with the WHERE clause.

  2. I think that Kepware is behaving properly by reporting NULL values when a machine is taken offline. You will see a different value in the “OPC Quality Code”. Check “store OPC Quality code” in the group property. Then use that value in FactoryPMI to show the user that the machine is down and disable it.

  3. I don’t know how FactorySQL could be adding to the database instead of updating it based on NULL values. Groups strictly either keep INSERTING records or UPDATING the same one, based on that group property. If the value is NULL it will write that in either case.[/quote]

I think the easiest thing to do is literally use static where clauses such as:

machine_num=1

and

machine_num=26

The column “machine_num” does NOT have to be anywhere in your group. When you start the group, FactorySQL will ask if you want to create the column, if it doesn’t exist. Next, factorysql will ask to create a row that matches that condition. After that, it will only ever write to that row, and it won’t matter if the OPC connection is up or down.

Hope that helps,