I’m trying to set up a DB tag and must be doing something fundamentally wrong.
I’ve created a new DB tag, DBLocalTime, of type DateTime and Expression/SQL Binding of a SQL Query. The query I’ve specified is simply “SELECT GETDATE()”. The query source is “Default”.
After creating the tag, a red “x” appears over the tag icon in the SQLTags Browser and the tag has a Quality of “Evaluation Error”.
I’m thinking I have not properly set up the SQLTags Provider for the “Default” query source or some similar property.
I tried adding a Realtime SQLTags Provider from the Ignition web configuration screen that references the MS SQL Server database I want to use. I was thinking that once I added it, I might be able to choose a different query source for the DB tag, but the drop-down list is always disabled.
I know I missing something fundamental here so any help is greatly appreciated.
Adding a new provider is way more than what you want to do- if you have a tag, it’s in a provider, and you should just go set the datasource on it (go to realtime providers and click “edit” on it).
There’s also the possibility that it’s something else. Go look at the console to see specifically what the error is (after logging into the gateway, go to “Console” on the left hand side). You can also look in the “wrapper.log” file in the install directory. (Side note: we know it shouldn’t be so hard to see what the tag’s error is- and we’ve actually added a new “Tag diagnostics” window in 7.2)
Finally, make sure your tag’s data type is set to “DateTime”. It could be that you’re selecting the value, but it can’t convert it to the current type of the tag.
I checked the console and here's the error message:
[quote]com.inductiveautomation.ignition.gateway.web.pages.config.systemconsole.LogViewer$SerializableLoggingEvent$ClonedThrowable: Cannot find database connection - name cannot be null.
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.findForName(DatasourceManagerImpl.java:486)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:109)
com.inductiveautomation.ignition.gateway.sqltags.execution.tags.ExpressionTag$QueryExecutor.getConnection(ExpressionTag.java:333)
com.inductiveautomation.ignition.gateway.sqltags.execution.tags.ExpressionTag$QueryExecutor.internalExecute(ExpressionTag.java:356)
com.inductiveautomation.ignition.gateway.sqltags.execution.tags.ExpressionTag$Executor.execute(ExpressionTag.java:229)
com.inductiveautomation.ignition.gateway.sqltags.execution.tags.ExpressionTag.internalEvaluate(ExpressionTag.java:177)
com.inductiveautomation.ignition.gateway.sqltags.execution.tags.AbstractExecutableTag.evaluate(AbstractExecutableTag.java:282)
com.inductiveautomation.ignition.gateway.sqltags.model.BasicScanClassInstance.evaluateTags(BasicScanClassInstance.java:192)
com.inductiveautomation.ignition.gateway.sqltags.model.BasicScanClassInstance.runTagEvaluation(BasicScanClassInstance.java:155)
com.inductiveautomation.ignition.gateway.sqltags.model.SimpleExecutableScanClass.run(SimpleExecutableScanClass.java:590)
com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:552)
java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source)
java.util.concurrent.FutureTask.runAndReset(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)
(Event 4 of 127) < Previous Next >[/quote]
This seems to be consistent with my assumption that the source is not properly specified.
I'm unsure where I should set the data source. Is it within designer or on the gateway? In either case, can you be more specific?
Log into the gateway, then go to SQLTags>Realtime. Next to the provider you’re using (normally “default”), click “edit”. On that screen you can specify which connection to use for sql queries in tags in that provider.
I don’t think another ‘tag provider’ is really what you’re looking for. A tag provider is something that executes tags- and they’re global to the system. So, usually you would only have multiple tag providers if you had multiple types of providers that you wanted to use.
For example, every Ignition comes with a default “internal” provider- one that stores tags inside of Ignition. But many people want to store there tags in an external database- maybe they want to make them accessible to multiple Ignitions, or maybe they’re integrating in to an old FactorySQL/PMI setup, etc. In this case, they would make another provider- a “DB Provider”.
Now, what it seems like you’re getting at is that you want to query multiple databases from inside of your DB Query Tags. You’ve setup a default datasource for the tag provider, and this is the one being used. You would think it would be easy to use a different data connection for a query. Unfortunately, for some reason right now the tag doesn’t allow you to pick one. I really don’t know why, it probably should (and you even see that there’s a drop down there to support it).
Still, all hope isn’t lost. There are actually several database related expression functions available, and these let you specify a connection name. So, instead of running your query as before, you could switch it to “Expression Mode” and run this:
runScalarQuery("SELECT GETDATE()")
and from a different DB:
runScalarQuery("SELECT GETDATE()","otherdb")
The other useful function is “runUpdateQuery”. I believe that these functions were accidentally left out of the Ignition documentation (something I think has been rectified for 7.2), but they’re the same as what was available in FactorySQL, so you can refer to this page from that documentation.
Please forgive me if I am not using the right terminology or I’m confusing concepts here but I’m still a little confused and hope you can get me straightened out.
For “Internal” providers, where are tags and their attributes stored? Server memory, database, local file? What do you mean when you say tags are stored internally to Ignition?
Must the “default” realtime SQLTags provider have the “Default Database” configured to an existing instance of a database? What is happening, if anything, when the “Default Database” for it is set to “- none -”, as it is initially? Is the “default” realtime SQLTags provider a database provider even though it’s labeled an “Internal Provider”? (I’m thinking it must if I had to set the “Default Database”.) Why is the “Default Database” called the default? Can one have a non-default database (i.e. query against different databases) for a provider?
I assuming that the purpose of a realtime SQLTags provider is to be a source for SQLTags. So if I create a new DB tag and set it’s binding to a SQL query, shouldn’t I be able to select a provider to query against from any of the “Database Providers” I’ve configured? Where am I going wrong here? Where else may I expect to utilize a realtime SQLTags provider?
By “types” of realtime SQLTags providers, I’m assuming you mean I can have any number of “Database Driving”, “Database”, and “Internal” providers, as these are the options I’m given when adding a new provider on the gateway. Would I ever need or want multiple “Internal” providers? What’s the difference between a “Database Driving” and a “Database” provider?
Is it possible to write to a DB tag? I would assume so since I can specify read only or read/write access when I create the tag. If so, how and when are values typically written from an Ignition client to the DB tag?
I’m confused regarding “runScalarQuery”. Isn’t it only available in scripts, not expressions? I don’t see how I can use it directly in an expression. I can see where one might utilize “runScalarQuery” with the advanced runScript expression function. Am I wrong?
Sorry for having so many questions. I’m really trying to get my hands around SQLTags.
They're stored in the Ignition internal database, like your windows, groups, settings, etc.
No, it's not require- but then things like what you're trying to do won't work. The default database for an internal provider means that when something needs to access the database, and it doesn't specify a name, this is the one it goes to.
Yes, as I mentioned in the last post, this should be possible, but currently isn't. The reason is because back in FactorySQL/FactoryPMI days, the tags weren't aware of the different database configurations available, so DB Tags set to query mode only operated against the database that contained the tags. With an internal provider, you don't have a database- thus we've added the "default database" option. I think shortly, though, we'll improve the system and make this possible.
No, what I was trying to say is that there is in fact an expression function by that name. It's not in the drop down list (<-fixed for 7.2, though). There is a similar function in scripting, and your correct that runScript could be used for that, but that's not what I'm talking about. You could literally copy the code block I posted and paste it into an expression and it would work.
All in all, I think I just realized a big point of confusion for you- the misleading name "DB Tag". It's really not all that accurate, as only thing "DB" about it is that it can be set to query mode and run a query. It can also be set to expression mode, in which case it has nothing to do with the database, and it can be set to neither query or expression ("static mode") in which case it's just a static variable. On a side note, this static mode is the only mode in which you can write to it.
Even the name "SQLTags" is a little misleading now that we have the internal provider. But, we didn't think it was a good idea to change it when we made Ignition, so that's how it is.
Thanks Colby. I think it just clicked. Sorry to be so slow on the uptake.
So, to summarize:
If I have a SQLTag configured for a SQL query, it’s going to hit against whatever database is configured as the default. Currently, there is no way to specify another database to run the query against.
If I have a SQLTag configured for an expression, the expression is going to get evaluated and the result assigned to the value of the tag. The expression, however, could execute the “runScalarQuery(query, [connection])” expression language function to hit against the default database, when no database [connection] parameter is given, or against whatever database connection is specified with the optional [connection] parameter.
If I have a SQLTag configured for an OPC value, the tag’s value will get updated by the OPC server.
If I have a SQLTag that is not an OPC tag nor is configured for a SQL query or expression, it’s a static tag. It is stored in the same way that windows, settings, etc. are stored. It is also the only type of SQLTag to which a value can be written.