Learning FactorySQL

I’m trying to get FactorySQL to work so I can learn how to make it do something. I made a couple of DB SQLTags in Designer, and a Group in the FactorySQL frontend, and trying to make an Action Item or two to write something to those tags. I can write to the tags in my FactoryPMI project, and I can see their values in the sqlt_core table in SQL Server Management Studio. But now the tags won’t show up in the Frontend and I keep getting various roadblocks that make no sense. Is there a tutorial somewhere that shows how to update a db tag from a Group? I was thinking to treat it just like an OPC tag, but that doesn’t seem to work.

The SQLTags system and the FactorySQL Groups/Items configuration are pretty much completely separate. You don’t update a DB SQLTag from a FactorySQL Group- you update it from a FactoryPMI screen. (Disclaimer - because SQLTags’ values reside in the database, you could update it from an action item, but you’d have to know what you’re doing)

Lets take a step back - what are you trying to accomplish?

Ah, you’re kind of looking at it from the wrong perspective. More specifically, you’re trying to bridge 2 fairly independent worlds: SQLTags and standard FactorySQL.

First off, SQLTags stands on its own in FactoryPMI, although FactorySQL is the driving force. Put a different way, the whole point of SQLTags is that you don’t have to go into FSQL at all. Therefore, the tags don’t show up in, nor are the usable, from FactorySQL.

The main question is what you want to accomplish. You said you created a DB tag, and wanted to write to it. What do you want to write? If you were trying to create an action item to run an expression or query, you would simply set up the DB tag to do that, under the “Expression/SQL” section on the tag configuration window in PMI. If you wanted to link it to an OPC point, you would simply create an OPC tag. Again, all configuration for SQLTags is done in the FactoryPMI designer.

Now, why would you go into FactorySQL? The FactorySQL frontend lets you configure groups with items. These are much more free form, and correspond to actual tables and columns that you define. In this way, you can do things like “create a table of tanks, with columns temperature, level and tank id, and each row represents a different tank”. That way you’re storing the data in a way that makes a lot of sense for the database. In PMI, instead of using SQLTags, you would be interacting with these table to accomplish what you want. Want to see the level of the tank? Bind a label’s text property to a polling query that selects it. What to write a setpoint? On a button’s action event, run a query that updates the correct row/column in the table.

I hope this is all clear. Essentially SQLTags is supposed to accomplish a few things: 1) Hide the database, 2) Provide better feedback/response (when you write a value, you get a solid response of when it executes). 3) Allow configuration in one place. Otherwise, you’re simply working with tables, rows and columns in the database.

Please feel free to reply with any further questions, or more specifics as to what exactly you’d like to accomplish.


OK, here’s what I want it to do. I have two boolean OPC tags and a DINT tag. When both booleans go true, I want to add the contents of the DINT to a DB SQLTag “accumulator”.

I accomplished this via a standard group with a one-shot trigger on the two booleans. It reads the DINT, adds it to the DB SQLTag and writes that to sqlt_core intvalue and updates the valuechange field for the DB SQLTag. All this works fine as verified by looking at the contents of the sqlt_core table.

Unfortunately, the problem is the DB SQL tag does not display the updated value in the application. In this case, I can open the table and see the value is now 22, and valuechange shows the current datetime, but the application still displays the 0 from 20 minutes ago. Even if I quit the application and relaunch it, it still shows 0. What do I need to do to make the application see that the value has changed and to update the screen?

Well, what you’re doing should work. To update a DB tag via the database (which you normally wouldn’t do, but in this case to respond to a trigger, it is a good idea), all you’d need to do is update the intvalue and the valuechange. For instance, a query like this would do the trick:

UPDATE sqlt_core SET intvalue=22, valuechange=now() WHERE name='test'

Hope this helps,

Yes, I can run that query and it will work. The value will update in the application window.

The problem comes when I try to do that query in a FactorySQL group, it doesn’t work. The value updates in the table and I can see that intvalue changes and valuechange changes.

Here’s an update on this. I had two separate items in the group with the Store result to DB field option enabled for intvalue and valuechange items. I removed them, inserted a new item with my own UPDATE query updating both fields (similar to yours above), with Store result to DB field disabled. Now it works. I don’t understand why, but it is working. The entire Action tab for the group is useless though.

Yeah, doing it in a query is the way to go. It’s true that the settings for the group would not be very relevant, aside from the update rate and perhaps a trigger.

Usually in situations like this you would just tell the group to point to a dummy table, called ‘dummy’ or whatever. It won’t actually do anything to the table, but right now each group needs a table name, so that keeps it away from other ones that are in use.

I can’t really say off hand why it didn’t work the other way, though there are a few possibilities. All said and done though, the way you’re doing it now is better. It’s probably better that you don’t have groups pointed directly to the sqlt_core table, as it’s very easy to accidentally add columns and rows that may have adverse effects.