Database

Summary

This document details different ways to create connections between ADISRA SmartView and the desired relational database(s).

There are different ways to create a connection between ADISRA SmartView and the databases, so it is important to establish why each connection will be needed and how it is going to be used.
Examples:

  • If the user just wants to store tag values in the database, the user will only need to create a global database connection in the top ribbon and use that connection in the Tag History document.
  • If the user wants to store the alarm history, it will be a similar solution.  The user will need to create a global database connection and use that connection in the Alarm History document.
  • If the user needs to execute queries such as “create table”, “select”, “update” or “delete”, the user may use the global database connection and the system function library or the user can write the entire connection using their own script. In this second example, the user will not need the global database connection.

Please review the following sections and feel free to use one of the solutions in an
application.

Configure Global Database Connection

Create new connection

The Global Database Connection is part of the project settings.  It allows the user to create a database connection through a connection wizard and use that connection in their application.  To configure a new global database connection, please follow the steps below:

  • In the ADISRA SmartView ribbon, go to “DataBase Connections” and click the “DataBase Connections” button, the “DataBase Connections” window will open.
  • To add a new database connection, click the “+” button shown in the red box below:
  • In this example, we will configure a PostgreSQL connection, select PostgreSQL then click OK:
  • The “Connection Properties” window will appear, the user should type in the database information that the user wants to connect.  The image below is just an example:
  • Click the “Test Connection” button, if all the information is correct, it will display the following dialog:
  • If the test connection succeeded, please click OK.
  • If the test is not successful, then an error message will be displayed with the error description.  The error message may state a driver installation is missing or connection information does not match.
  • The Advanced button displays custom configurations for the selected database driver.  The database being used will determine if the additional information is required.
  • Click OK on the “Connection Properties” window, then name the connection and select if the tables will or will not be created with the default suffix.  The default suffix is the name of the project:
  • If the user wants to change the suffix, uncheck the Default Suffix check box and enter the new suffix:
  • See the example of two tables created, the first one with a custom suffix, and the second one with the default suffix which is the name of the project.  In this example the project name is “databaseproject”:
  • The user should see the connection created:
  • Different DataBases will have different options in the “Connection Properties” window.  The user should configure it according to the Database configuration. 
Note: When configuring or renaming connections to an Access database file or a Microsoft SQL Server database file there will be an extra option called “Relative Path”, if unchecked ADISRA SmartView will look for the file in the absolute path of the hard drive.  For example “C:\Users\Documents\AccesDB.accdb”.  It is important to understand if the user executes the project in another machine, it will not be able to find the file.  If the option “Relative Path” is checked, ADISRA SmartView will look for the file in the relative path from the project path.  Selecting the “Relative Path” option will make it easier to export the project to another machine.

Remove connection

To remove a database connection, follow the steps below:

  • In the ADISRA SmartView ribbon, go to “DataBase Connections” and click the “DataBase Connections” button, the window “DataBase Connections” will open.
  • Select the connection the user wants to remove and click the “-” button shown in the red box below.
  • Confirm the user wants to remove the connection item.

Edit connection

To edit a database connection, follow the steps below:

  • In the ADISRA SmartView ribbon, go to “DataBase Connections” and
    click the “DataBase Connections” button. The window “DataBase
    Connections” will open.
  • Select the connection the user wants to edit and click the “pencil symbol”
    button shown in the red box below.
  • It will open the “Connection Properties” window so the user can edit the
    connection.

Rename connection

To rename a database connection, follow the steps below:

  • In the ADISRA SmartView ribbon, go to “DataBase Connections” and click the “DataBase Connections” button. The window “DataBase Connections” will open.
  • Select the connection the user wants to rename and click the “Rename Connection” button shown in the red box below.
  • The “Rename Connection” window will open and the user can rename the connection. Select “OK” to complete the renaming.

Import and Export Database Connections

To import and export configured database connections, follow the steps below:

  • In the ADISRA SmartView ribbon, go to “DataBase Connections” and click the “DataBase Connections” button. The window “DataBase Connections” will open.

Export Database Connections

  • Click the “Export Connections” button shown in the red box below.
  • When exporting the connections, a new dialog box will display.  The user has the option to select the folder and insert a name for the exported file.  Click the “Save” button and the file will be generated.

Import Database Connections

  • Click the “Import Connections” button shown in the red box below.
  • After clicking the “Import Connections” button, a dialog box will display allowing the user to select a csv file previously generated by ADISRA SmartView.
  • Select the exported file, name the file and click the “Open” button to import the file into the current project.  The “Import Connection” functionality allows the user to import a previously exported file.

Global Database Connections

The “Global Database Connection” option, highlighted in the red box above, can be used to store Tag Values and Alarms in the database.  The option can be used to run queries using the SVDatabase System function Library.

To use the configured global database connections, follow the steps below.

Using Database Connections with Alarm History

As soon as the runtime is started, the alarm history table will be created by ADISRA SmartView with all the needed columns.  Each alarm history document will generate one table in the database.  The database history table created will be one table per document with all tags configured in it.

By contrast, in Tag’s History option, each the tags inside the document will generate a new table.

  • The alarm history can be saved into a configured database.  Open the alarm history document the user wants to configure on the “Save Type” combo box, select “Database” and on the “Connection Item” combo box, select a configured “Database Connection”.
  • As soon as the runtime is executed, the alarms configured in the Alarm History document will be saved to the database.  The values saved to the database can be loaded by the Alarm object as shown in the example below.
  • The following image shows the database table created.  The table is created per document configured.  So it will contain each tag configured in the document and its relevant alarm information.

Using Database Connections with Tag History

As soon as the runtime is started, the tag history table will be created by ADISRA SmartView with all the needed columns.  It will be one table per tag.

  • The Tag History can be saved into a configured database.  Open the Tag History document the user wants to configure.  Inside the “History Type” area in the “Type” combo box, select “Database”; and in the “Connection Item” combo box, that will appear after selecting the “Type” as “Database”, select a configured “Database Connection”.
  • As soon as the runtime is executed, the tags configured in the Tag History document will be saved to the database.  The values saved to the database can be loaded by the Trend (History) object as shown in the example below.
  • The following image shows the database table created.  It contains the tag value, quality and timestamp.

Using Database Connections with Events

It is possible to save the Events into the database. Please follow the steps below to configure it.

  • The Events can be saved into a configured database. Select Events in the Settings ribbon; in the “Save Type” combo box, select “Database” and in the “Connection Item” combo box, select a configured “Database Connection”.
  • Below is an example of the table created in the Database:

Using Database Connections with Database Driver

The user can also configure a Database Connection similar to a driver communication. In this section, the user will learn to link a tag with a database record.

  • With the Database document in ADISRA SmartView, the user can configure a communication between one or more tags to a selected Database.
  • Create a Database document by left clicking the “Divers” node and right clicking the “Database” option inside the “New Folder” as shown in the red box below.
  • Left click the newly created “Database” folder and then right click the “New Document” option. A database window opens.
  • In the “Connection Item” box, select a configured connection and in the “Actions” box, select the directional flow of the data.
     
  • In the “Queries” area click the “New” button, name the query and click “OK”.
  • In the “Table” combo box, select the configured table from the database.  In the “Key Field”, “Value Field”, “Time Stamp Field” and “Quality Field” boxes, select the columns configured from the table previously selected.
  • Inside the “Tags List” area, double click on the cell in the Tag column (as shown by the red box in the image below), then click the “…” button; this action will open the “TagBrowser” window for the user to choose a tag.
  • Inside the Tags List area, double click on the cell in the Item column (as shown by the red box in the image below), then click the “…” button; this action will open the “Database Item Browser” window for the user to choose an item from the database to be associated with the selected tag.  Select a “Query” created and a “Key” from the database table, click “OK” to save the document.

Using SVDBConnection Functions

How to query a database using the “Global Database Connection” and the the “SVDBConnection” that is located within the “Systems Function Library”.

The following examples will help the user understand how the global database connection can be used with the “SVDBConnection” located within the “System Functions Library” to query the database.

A separate chapter will describe different ways to use the “SVDBConnection” to query the database without using the “Global Database Connection”. Using the “Database Connection” approach is a simpler more direct approach to database query.

NOTE: The highlighted functions above contain an input parameter called “connParameter” which is the name of the global database connection.

Using SVDBConnection.Select() function

  • The example below shows a script that will read values from a table in the database and write these values on a Datatype Tag reflects what can be done with the () function
  • The Data Type configured:
  • The Tag configured:
  • The table in the database:
  • The Script configured. The script will run a select query using the “Global Database Connection – PostgresSQL001” and all the records retrieved will be saved to the dynamic array tag. If an exception occurs, the result will be null and the script logs an error.

NOTE: The first parameter of SVDBConnection.Select is the global database name, In this example, “PostgresSQL001”.

Using SVDBConnection.Insert() function

  • The example below shows how to create a script that will insert a new table entry in the database.
  • Below shows the table in the database before the script is executed.
  • The script configured:

NOTE: The first parameter of SVDBConnection.Insert is the global database name, In this example, “PostgresSQL001”.

  • The table below shows records in the database after the script is executed:

Using SVDBConnection.Update() function

  • The example below shows how to create a script that will alter values in a table in the database.
  • Below shows the table in the database before the script is executed:
  • The script configured:

NOTE: The first parameter of SVDBConnection.Update is the global database name, In this example, “PostgresSQL001”.

  • The table in the database after the script is executed:

Using SVDBConnection.Delete() function

  • The example below shows a script that will delete lines from the table in the database.
  • The table in the database before the script is executed:
  • The script configured:

NOTE: The first parameter of SVDBConnection.Delete is the global database name, In this example, “PostgresSQL001”.

  • The table in the database after the script is executed:

Script Database Connection

In this chapter the user will be shown different ways to query the database without using the “Global Database Connection”.

Using .NET Data Provider

The scripts can be written inside a button, user function, service, and trigger.  Depending on the database, a different class object will be used.
The examples below can only be used to connect to a PostgreSQL database.

  • The following example creates a table called inside the PostgreSQL database.
  • The above example is a “UserFunction”.  The first parameter of this function is used to create the table name and the second parameter, in case it is true, will drop the table before its created.
  • The next code inserts values into a PostgreSQL table.

Using SVDBConnection

The user may also use the “SVDBConnection” within the System Function Library to run queries.  It is similar to the example in the “Using SVDBConnection Functions” section, but in this current example the user will need to provide the connection string and the provider.
Please take a look at the example below.  It will select values from a Microsoft SQL Server table and add those values to a dynamic array tag.  This is an alternative database query approach.

  • The SHIFTS data type contains the following tags:
  • The following script was inserted in a Service. As it was already detailed, it will connect to a Microsoft SQL Server table, select all the values and create new entries into the dynamic array tag.
  • The dynamic array tag to be associated with a MultiTagViewer object will display the values from the database during runtime.
  • Let’s run the application to check the values selected.