Configure an ODBC Server

To create an ODBC connection to your application (including REST and Java ODBC), at least one server must be configured as an ODBC server.1

The steps are much the same as those to set up VTScada Thin Client Server and Realm, with a few small exceptions.

The Max Clients setting has no effect in this context. That setting is relevant to VIC and MIC connections, rather than ODBC clients.

A security certificate is optional but strongly recommended. Internet Security (TLS, X.509, SSL)

  1. Ensure that your application is secured and that the account you will use for queries has the Remote Data Access privilege.
    (While testing the connection, it may be helpful to have the Thin Client privilege. Once configured, we recommend the use of an account that has few or no privileges other than Remote Data Access. The Tag Parameter View privilege may be useful in some instances.)
  2. In the VTScada Application Manager (VAM), select the tool labeled Internet Setup. The VTScada Thin Client/Server Setup dialog will open.

Adding a realm in the Thin Client/Server Setup dialog

  1. Select the Add button to open the Add Realm window where you will provide a name and a connection protocol for your server.

The Add Realm dialog

  1. Provide a name.

The name should clearly indicate what the server is. As a suggestion: by including the letters "ODBC" you will make the name easier to recognize later when configuring the ODBC connection. If using Security Realms, your choice will be limited to realms created for that feature.

    Do not name any realm, "Rest" or "SQLQuery". Doing so will interfere with remote access to VTScada data.

  1. Configure the Protocol and Port Number

For the purposes of the ODBC Interface you must enable the HTTP protocol. The Port Number will normally be 443, the standard HTTPS port, unless your network administrator directs otherwise.

  1. Select the "Secure" option, if using.
  2. Click, "OK" to save the new Realm
  3. Click the second Add button in the Thin Client Window to select which application will be available to this server.

Adding an application to a realm

SoapServicesRealmName is no longer used. If your technology relies on SOAP services, request a copy of the VTScada version 11.3 ODBC driver. As of version 12, we now use REST.

Optional Step:

(This is not required for queries. It is used only for convenience when using an external program that presents these summary tables as if they existed as real tables. You may query any TPP time frame without defining any value for SQLQueryTableTPPs)

You can take advantage of VTScada's ability to group data records by time interval. By adding the application property, SQLQueryTableTPPs, you can retrieve tag data from specific time intervals. The format of the statement is:

SQLQueryTableTPPs    =    <time frame specifier>

where the time frame specifier takes the form of a digit and a letter. The letter indicates the units in which time interval is measured (hours: h , weeks: w, etc.) and the digit specifies the number of units of each interval that should pass between each record.

 Note that you can have only one TPP statement in a settings file, but that statement can have multiple values, separated by semi-colons and no spaces.

Two examples follow:  

; two hours...
SQLQueryTableTPPs    =    2H

...or, using the Add Property dialog and configuring two TPP values:

Adding TPP values for aggregated reports


The available time interval units are:

  • MS - milliseconds
  • S - seconds (this is the default; the S may be omitted)
  • M - minutes
  • H - hours
  • D - days
  • W - weeks
  • Y - years

Connect to the ODBC Server

Any computer that has a network connection to the ODBC Server may connect. There is no need for VTScada to be installed on that computer, but the Trihedral-supplied ODBC Driver must be. On each computer that is to connect to the server, run VTScadaODBCDriverInstall.exe. This will install the ODBC driver needed to connect to the server.

Connection parameters must be supplied to the program that will run the query. This can be done using a data source name (DSN) configured using the Microsoft ODBC Data Source Administrator, or you may use an ODBC connection string.

If using an ODBC connection string, it will take the following generalized form. Note that none of the parameters are encrypted or hidden.

DRIVER=VTScada ODBC Driver;SERVER=Your_Server_Address;REALM=Your_Realm;UID=Account_Name;PWD=Password

If using a DSN, open the Microsoft ODBC Administrator utility on your computer. There will be two such utilities: one for 32-bit connections and one for 64-bit connections. A search for "ODBC" from the Windows Start button should find both. Choose based on the program you will be using to generate the queries. VTScada can work with either.

Open the System DSN tab of the Administrator utility and create a new connection, selecting the VTScada ODBC Driver.

Examples:

  • A configured DSN. The password cannot be viewed.

Example of a new DSN in the ODBC Admin tool

 

In the following examples, square brackets indicate text for you to replace. They are not part of the connection string.

  • Connection string for a secured application:
DRIVER=VTScada ODBC Driver;SERVER=123.456.123.456;REALM=[RealmName];UID=[Username];PWD=[Password]
  • Connection string for a realm configured to use a port other than the default 80, or to use a security certificate:
DRIVER=VTScada ODBC Driver;SERVER=[ServerAddress];REALM=[RealmName];UID=[Username];PWD=[Password];PORT=443;SSL=YES

Connection string that includes a five-minute timeout:

DRIVER=VTScada ODBC Driver;SERVER=[ServerAddress];REALM=[RealmName];QUERYTIMEOUT=300;UID=[UserName];PWD=[Password]

Notes:

  • The DSN name you enter should be as descriptive as possible. Be aware that long names might not save properly.
  • For the VTScada Server field, enter the fully qualified domain name of the machine running the VTScada application.
  • The port should match what was configured in VTScada for the Realm.
  • The SSL box is selected only if the VTScada Thin Client Server has been configured to use a security certificate. (Strongly recommended.)
  • Selecting 'Use Backup Servers' causes a list of VTScada Thin Client Servers to be retrieved from the VTScada ODBC server and stored, any time a successful connection is made. If future attempts to connect to this server fail, the list of VTScada Thin Client Servers will be tried one-by-one for a connection
  • Connection Timeout. Initially blank, defaults to 10 seconds. The connection timeout is how long the driver waits for a response from the target VTScada server. If the user application is making many concurrent queries then if the actual number of attempted concurrent connections through the driver instance exceeds the configured amount, then a new connection will be stalled until a connection "slot" becomes available. In such a case the user might need to increase the timeout or max. number of concurrent connections.
  • Queries that take longer than one minute to complete will time-out. If you have queries that need more time, you can set a value larger than 60 here.
  • Use the Max. concurrent connections option to adjust the limit of concurrent connections. If the application is running multiple long-running queries then increasing this number might reduce timeouts at a cost of increased resource usage on both the driver host and the target VTScada server. Simply increasing this number because a client is making lots of expensive queries will cause performance issues on the target VTScada server.

If security realms are enabled (NameSpaceDelimiter is set), then the username for ODBC queries should be configured for the realm. For example, if you have a security realm named "ODBC" and a NameSpaceDelimiter of ":", then you need a VTScada Thin Client Server realm named "ODBC" and a user account within the matching security realm, "ODBC:username".
If querying tag parameters, the account must have the Tag Parameter privilege.

  • The Test Connection button should succeed if the application is running.

Troubleshooting:

If there is no connection, confirm that all the following are in place:

  • Server and Realm configuration completed on the VTScada server.
  • Security is active and the user account has the VTScada Thin Client privilege.
  • The querying workstation is able to connect to the server.
  • If using a DSN, you are using 32-bit or 64-bit as required by your querying program.
  • The connection string or DSN uses the correct names and addresses as configured on the Server.