Elipse Knowledgebase :: Using Oracle databases from the E3

Knowledgebase Home

Search the Knowledgebase

Browse by Category

Knowledgebase Home > English > Elipse E3 > DataBases

Using Oracle databases from the E3

1) Introduction

In a supervisory system, we work with different variables and information, which in a given moment we need to store for future reference or to make searches and comparisons, besides issuing reports of follow up of production, use and operation of machines, devices, and other equipment.

The E3 allows the creation of 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", and then, "Database".

Figure 1 - Inserting a Database to E3

When inserting the database in the system, the options referring to the type of data server are enabled. The DB Server is the responsible by the management and storage of information of the objects that use this service. Existing types are: Oracle, Access, and SQL Server Data Servers. These parameters are defined through property SourceType. For the Oracle server, choose option "stOracle".

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, it is requested the user and password according to the setting of Oracle.

Figure 2 - Settings of Oracle Server

In case you need, set up the properties of the Database:

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:

Set DBConnection = CreateObject(“ADODB.Connection”)
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:

DBConnection.Execute
“UPDATE Tabela SET nome = ‘João’ 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:

Set Recordset = CreateObject("ADODB.Recordset")
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.