General Discussions
SQL Query Driver

Can I get some help in defining a SQL Driver template for pulling values from another VTScada system?

ODBC driver is installed, user account with remote data permissions created, realm created, DSN defined, connection verified.

There is no option in the database type list for VTScada. What should be chosen here?

I made a probably incorrect assumption that a query should follow the format described in the SQL query documentation here

SELECT '^A' , Timestamp FROM History ORDER BY Timestamp DESC LIMIT 1

Where address of the I/O tag attached to the SQL Query Driver is

RemoteContext\Sub Context\Interesting Part:Value

This, of course, is not working. I just want the latest value for tags. Some help with this "Instantly Intuitive"™ task would be appreciated.

Can I get some help in defining a SQL Driver template for pulling values from another VTScada system? ODBC driver is installed, user account with remote data permissions created, realm created, DSN defined, connection verified. There is no option in the database type list for VTScada. What should be chosen here? I made a probably incorrect assumption that a query should follow the format described in the SQL query documentation [here](https://www.vtscada.com/help/Content/D_LogAndReport/Dev_SQLQueryExamples.htm?tocpath=Use%20VTScada%7CLog%252C%20Note%252C%20and%20Report%7CSQL%20Queries%7C_____3) ```` SELECT '^A' , Timestamp FROM History ORDER BY Timestamp DESC LIMIT 1 ```` Where address of the I/O tag attached to the SQL Query Driver is ```` RemoteContext\Sub Context\Interesting Part:Value ```` This, of course, is not working. I just want the latest value for tags. Some help with this "Instantly Intuitive"™ task would be appreciated.

I've included some references below, but to get the current value, along with other tag properties, you can skip the history table, which is looking for a date range and use something similar to:

https://myServer/myRealm/REST/SQLQuery?query=SELECT%20Name,%20ShortName,%20Description,%20Area,%20Value%20FROM%20Parms_AllTypes

Where %20 is simply used to show a space if you were to try this in a web browser (don't since myServer/myRealm does not exist smile )

Also, in the query above, you can use tag types, areas, or reference a SQL View Tag to limit your results to those you are looking for.

The ODBC driver is for a client machine and receives ODBC but queries VTScada using the REST Interface.

SQL View Tag: https://www.vtscada.com/help/Content/D_Tags/D_SQLView.htm

REST Interface: https://www.vtscada.com/help/Content/D_LogAndReport/D_RESTQueries.htm?tocpath=Use%20VTScada%7CLog%252C%20Note%252C%20and%20Report%7CSQL%20Queries%7C_____4

SQL Queries Examples: https://www.vtscada.com/help/Content/D_LogAndReport/Dev_SQLQueryExamples.htm?tocpath=Use%20VTScada%7CLog%252C%20Note%252C%20and%20Report%7CSQL%20Queries%7C_____3

I've included some references below, but to get the current value, along with other tag properties, you can skip the history table, which is looking for a date range and use something similar to: https://myServer/myRealm/REST/SQLQuery?query=SELECT%20Name,%20ShortName,%20Description,%20Area,%20Value%20FROM%20Parms_AllTypes Where %20 is simply used to show a space if you were to try this in a web browser (don't since myServer/myRealm does not exist :) ) Also, in the query above, you can use tag types, areas, or reference a SQL View Tag to limit your results to those you are looking for. The ODBC driver is for a client machine and receives ODBC but queries VTScada using the REST Interface. SQL View Tag: https://www.vtscada.com/help/Content/D_Tags/D_SQLView.htm REST Interface: https://www.vtscada.com/help/Content/D_LogAndReport/D_RESTQueries.htm?tocpath=Use%20VTScada%7CLog%252C%20Note%252C%20and%20Report%7CSQL%20Queries%7C_____4 SQL Queries Examples: https://www.vtscada.com/help/Content/D_LogAndReport/Dev_SQLQueryExamples.htm?tocpath=Use%20VTScada%7CLog%252C%20Note%252C%20and%20Report%7CSQL%20Queries%7C_____3

Trihedral Engineering Ltd.

Dave,

Thanks for the response, but I've read all of your links previously. I cannot grant access to tag parameters to the ODBC account to access the Parms_AllTypes table.

Is this not correct?:
https://forum.trihedral.com/index.php?u=/topic/636/use-json-xml-driver-in-vtscada-a-to-read-a-tag-from-vtscada-b/post-1894#post-1894

The query described in my initial post works perfectly fine when using a third party browser. When adapted to a template in the SQL Data Query Driver returns a value of "8".

Since you didn't reference the SQL Query Driver at all, I'm going assume that Graham is incorrect and the SQL Query Driver Tag is not intended to share data between VTScada installations, and cannot be configured to do so.

Thanks for your time.

Dave, Thanks for the response, but I've read all of your links previously. I cannot grant access to tag parameters to the ODBC account to access the Parms_AllTypes table. Is this not correct?: https://forum.trihedral.com/index.php?u=/topic/636/use-json-xml-driver-in-vtscada-a-to-read-a-tag-from-vtscada-b/post-1894#post-1894 The query described in my initial post works perfectly fine when using a third party browser. When adapted to a template in the SQL Data Query Driver returns a value of "8". Since you didn't reference the SQL Query Driver at all, I'm going assume that Graham is incorrect and the SQL Query Driver Tag is not intended to share data between VTScada installations, and cannot be configured to do so. Thanks for your time.
47
2
2
live preview
enter atleast 10 characters
WARNING: You mentioned %MENTIONS%, but they cannot see this message and will not be notified
Saving...
Saved
With selected deselect posts show selected posts
All posts under this topic will be deleted ?
Pending draft ... Click to resume editing
Discard draft