In a supervisory system, we work with different variables and information that need to be stored for future reference or for future reference in searches and comparisons, in addition to issuing reports of follow up of production, use and operation of machines, devices, and other equipment.
The Elipse E3 system allows creating a file with this information by managing the storage of historics, formulas, and alarms in databases. In order to facilitate the access to these data by other applications and systems, E3 supports several databases in the market, like Access (MDB format), SQL Server, and Oracle too.
Next, we will see how to set up in E3 the access to Oracle database for the storage of this information.
2) Prerequisites
The use of Oracle as server of database for E3 is only possible through the installation of an Oracle server or client in the machine in which the E3 server will run. In this installation, it should be mandatorily present the support to OCI (Oracle Call Interface) and OLEDB Provider for Oracle, necessary for the communication of E3 with Oracle. In order to install Oracle, consult the documentation of the product.
3) Accessing the Oracle DB from within E3
In order to communicate with a database in the E3, we use the Database object. To insert this object in the E3, right click the mouse over the name of the project and choose option Insert–Database.
Figure 1 – Inserting a Database to E3
In the E3 Database Server, it will be placed the name of the connection of access to Oracle (defined in Oracle itself). If the Oracle server is running locally, the connection can be blank, as E3 will automatically look for data. If not, it is necessary to indicate the name of connection. In fields User and Password, the user and password set up at Oracle will be requested.
Figure 2 – Settings of Oracle Server
|
PROPERTY |
DESCRIPTION |
| ConnectTimeOut | Returns the maximum time a call to the database can take. If the time is over, the operation is canceled. |
| EnableSynchronization | Records data simultaneously in two databases, in a way as to allow more safety for data. |
| ReconnectDelay | Determines the time out between trials of reconnection with the Database. |
| SourceType | Sets or returns the type of database server. |
4) Access to Database via scripts
The scripts are modules of programming language in which it is possible to create code lines that allow more flexibility to associate actions to specific events. Each item of an E3 project has a list of events that are previously associated. Thus, the user will be able to create programs that will be executed if an event occurs.
The E3 uses the VB Script (Visual Basic Script) in its scripts, and with them it is able to instance any ActiveX object registered in the system. For obtaining access to Databases, the ActiveX that is mostly used is the ADO, which can be easily manipulated in E3 scripts.
First, it is necessary the creation of an ADO connection that can be done with the script below:
DBConnection.Open “Provider=MSDAORA;
DataSource=conexaoOracle;User
ID=scott;Password=tiger;”
After the creation of the connection, it is possible to execute a SQL command directly over the connection, as shown below:
“UPDATE Tabela SET name = ‘Alex’ WHERE id = 10”
It is also possible to visualize all records wanted, returned in the form of a recordset (query), as it is shown below:
Recordset.Source = “Table”
Recordset.ActiveConnection = DBConnection
Recordset.CursorType = 1 ‘adOpenKeyset
Recordset.LockType = 3 ‘adLockOptimistic
Recordset.Open
From this moment on, it is possible to navigate through the table by using commands MoveNext and MovePrev, as well as a series of other commands made available by the ADO. It is also possible to use a SQL query in order to manipulate specific parts of the table, through property Source.
5) Technologies used by E3 to access the Oracle database
E3 basically uses two technologies to access the Oracle database: ADO (ActiveX Data Object) and OCI (Oracle Call Interface).
ADO is the technology used by the E3Browser and E3Chart to recover data stored in the database. ADO is also widely used in E3 scripts for every kind of operation with the database. It is a group of COM objects created to access information of SGBD by using OLE DB. This technology becomes available when we install the OLE DB Provider for Oracle (MSDAORA). It accesses the native interface of Oracle, OCI.
The OCI is used by the DB Server to implement the services of database used by Historics, Formulas, and Alarms. Through the OCI, we directly access the functions of Oracle, optimizing the process to a maximum. It supplies a standard library of access to the database, and functions of data recovery under the form of a DLL or LIB, which can be connected to an application during running time.
6) How the access to Oracle is accomplished by the DB Server
The Database Server of E3 uses the OCI (Oracle Call Interface) to communicate with the Oracle server, i.e., uses the native form of the database to have a gain in performance in the communication with the DB.
A good example of how the communication of the DB Server of E3 with Oracle works is the Historic. Initially, data are acquired by the historic, which immediately send them under the form of requisitions to the DB. These requisitions can be for the creation of a table, creation of indexes and keys, addition of data, etc. The DB Server of E3 has a process that is especially separated to receive them. Depending on the type of operation to be accomplished, data is formatted and stored in temporary files or memory.
At the same time, the DB has another process, which is responsible for the recovery of requisitions and further sending to the Oracle database under the form of SQL commands, by using the OCI.
