Accessing a database via E3Query.

1) Introduction

Although widely used in supervisory systems, proprietary databases, which can only be used by the software generating the data, are not the most flexible solution in this case.

An alternative in this case is using commercial databases (be they file managers or database managers). Among these are MS-Access, MS-SQL Server, and Oracle. However, access technologies to these programs have evolved a great deal for the past years. Some of these technologies are discussed below.

ODBC Interface

The use of ODBC interface, which has been available in Windows systems since version 3.1, presents a few limitations:

• Instability: in addition to causing instability in the process, the machine’s process can be consumed by the amount of data necessary to simply return a query with external results.
• Settings: to use ODBC, you need to register each source at Windows’ Control Panel, which requires running processes that are external to it, which consequently makes it more difficult to maintain and more prone to errors.

Accessing the Database via Scripts

Another way to create queries to databases is via programming. Elipse E3, for example, supports VBScript, which displays functions that specifically access commercial databases. Although simple, these functions can present a few difficulties that can create flexibility problems when the application needs changes. For example, you will need the complete path of an MDB file to use this file’s connection. If the installation in an application moves to another machine, access to the database may be lost. However, even with these risks, this is one of the more popular ways to connect to a database, especially when:

• The same connection performs different queries.
• Existing tables or records are updated.
• There are complex or pre-formatted queries.
• There are stored procedures available in relational databases.

An example of query to a database via:

Set cnnOracle = CreateObject(“ADODB.Connection”)
cnnOracle.ConnectionString = MedidorConfig.Item(“Dados”).Item(“DBConnString”).Value

Access via scripts is not recommended for typical uses in industrial automation, such as periodic queries to a system’s alarms table, because running access scripts continuously introduces undesired performance and stability variables. For such cases, Elipse Software has developed in E3 an object responsible for creating and using these queries: E3Query.

E3Query is a graphic editor for queries, similar to the one used by Microsoft to access MS-Access and MS-SQL Server (MS SQL Query Analyser). With this tool, queries can be used automatically in the database, thus eliminating any needs for creating and maintaining scripts.

2) E3 and Database

Elipse E3 currently supports three database systems:

• MS Access
• MS SQL Server
• Oracle

For further details on each of these databases’ features and differences, as well as suggestions on how to choose between them, you can refer to Introduction to Databases in E3.

For this article’s purposes, all you need to know is that the database type is transparent to E3 Query, because these settings are done in the DBServer. Therefore, we will work here with MS Access, which is the simplest type of database supported by E3.

IMPORTANT: when moving to a new database type, you may need to rebuild the queries. For this reason, we recommend not developing the project with a temporary database to be corrected later, because this will cause extra work when finalizing the project.

3) E3 Query

E3 Query is the wizard for creating default queries in E3. The objects that queries intensively, such as E3 Browser and E3 Reports, have a built-in E3 Query, as can be seen in Figure 1.

To start working with E3 Query, right-click it and insert a Query object, which will access an available database. To open its configuration window, double click it (Figure 2).

After setting up the database from where the data will be extracted, the list of available tables is displayed. With this information, the SQL query with the data from the DB will be automatically assembled by E3.

After selecting the interests table (Figure 3), the query’s construction interface will be displayed (Figure 4).

On this screen, the following areas are presented:

• Fields: in this item, you will see the Add Table button, which queries multiple tables.
• Variables: used for setting up the parameters of the filters created for the query.
• View: displays Execute Query button, which queries the database.
• SQL: direct edition in the SQL is recommended for clients knowledgeable in this language.

On Fields tab, the tables opened for the new query are displayed. You can choose “*” item, which selects all fields, or each field individually, which is more recommended. In the example being created in this article, only one table is opened. The fields ending in _Quality are generated automatically by E3 historics.

Figure 5 illustrates a complete query developed in E3 and shows the use of other areas, such as selected columns with sorting and grouping options, among others. One of the more important options is creating filters in each field, which can be used via scripts, as seen later in this article.

The area where the SQL command is developed for selected data (SQL tab) works only for the query, unless SQL direct edition is enabled.

The View tab brings the result of the query performed via Execute Query button, in spreadsheet format.

After setting up the basic query, a query with filter per date will be performed. To do so, we will use the same query already configured. The procedure is the same for any selected field, but the historic query is usually performed per period, be they production data records or past events records. Other filters are also very common.

To edit filters per timestamp in E3 Query, go to the line displaying the field name, access Filter item, and right-click the […] sign (similarly to E3’s links). This will cause the filter’s configuration interface to be opened (Figure 6).

On this window, set up each criteria field (such as “higher than” and “lower than”) similarly to the variables that will be used to apply the fields. In this case, use InitialDate and FinalDate variables. Notice that (as seen in Figure 6) the variables’ names must be between <% and %> symbols, so that they can be understood as parameters. To be able to work with dates, you will need to pass the arguments between two # signs. For other variables, don’t use #.

After each filter is set up, you can test again the query’s results. To do so, finish editing the filter by clicking OK button. Go to Variables tab and set up the values for each filter to be used (Figure 7).

The last step to confirming whether the query was created properly is go to View tab and click Execute Query button, checking the list of returned data to see if they meet the expectations. Change initial and final dates to check if the query’s results are modified. Additionally,  Figure 8 displays the query created with the filters defined at the wizard.

Using E3 Query filters via scripts

Setting up the query and checking whether it is correct are only two of the possible uses for E3 Query. Its use at run time and the changes to the values of the filters defined for the query can be seen in the script below. The Query.SetVariableValue function is responsible for parameterizing the query according to the values passed via script.

Requery() function enables new query parameters to be used, since a new query is made by complying to these values.

Using a query’s Record Sets via scripts

Another possible use of E3 Query is working with Record Sets. After a query is created, you can capture the corresponding record set and work with records line per line, execute queries, etc.

On “RS = TelaInicial.E3Browser1.Consulta1.GetADORecordset()” command line, the RS variable takes on all the features of a record set, as seen in the beginning of the section Accessing the Database via Scripts. This resource’s advantage is that once the changes are applied to the DB, the scripts accessing this database no longer need any further settings.