1) Introduction
Although E3 already has objects like queries that simplify manipulation of database records, we can improve its performance creating subroutines directly on the database using T-SQL language, and those subroutines perform tasks on the server in an effective way and high performance.
2) Queries in E3
By default, we use E3 queries to access records on a database table, which is a simple and practical object to be used, and there are cases where we use ADO connections via script; however, it is not necessary to use this method in E3 when we are dealing with databases such as SQL Server, Access or ORACLE.
The E3 query works the following way:
You point to one (or more) table(s), from which you select some (or all) fields and the query will perform a SELECT on the table and return the results. You can use SQL commands like DELETE, INSERT and UPDATE by editing the SQL tab of the query or even using SQL functions and applying filters with the WHERE clause.
When network traffic is not a problem, using queries is the best option and the most recommended one.
3) Using stored procedures
A Stored Procedure is a set of commands to which you give a name. This set is stored on the database (Server) and might be called at any time by the database management system (DBMS) or by an E3 application.
You can use Stored Procedures when the network information traffic is huge or the application has lower performance because processing is mostly due to data manipulation between the application and the database.
When you create a Stored Procedure, you can pass input and output parameters to it, which will contain information required by a SELECT, for instance. These input parameters are passed the same way you pass variables to a query filter.
Since Stored Procedures are analysed and stored on data server memory after first run, its execution is faster than using SQL instructions, because these ones are analysed each time.
They also offer a benefit relative to the amount of data that must be sent to and from SQL Server, sometimes being necessary a few bytes to call a stored procedure containing thousands of bytes per instructions. The effect of this savings is significantly noticed when multiple users are executing repetitive tasks.
4) Application (Database)
An example application was created to illustrate how to use Stored Procedures to insert, read and delete records on a database.
Basically the application allows data maintenance on the Engine table, where the user has permission to insert, delete and check engine data on his plant. We will use SQL Server 2005 Express to create the table and the Stored Procedures.
Initially we should start SQL Server and connect to the local server with a user and password created when installing SQL Server on the local machine.
Figure 1: SQL Server connection
With SQL Server open, we will create a new database named Devices and an Engine table inside it. This table will contain the columns Manufacturer, Area, Machine, Power, Current, and Tension. The fields will be of type nchar(10), nchar(10), nchar(10), float, float and float, respectively, as in the following figure:
Figure 2: Creating Engines table
Inside the Devices database we will access the Programmability folder. Right-clicking on Stored Procedures folder, select New Stored Procedure… option.
Figure 3: Creating the Stored Procedure
On the right side of the window there will be an editing area where we will create Stored Procedures and we will erase the whole source code that is inserted by default.
Then insert the following source code:
@MManufacturer nchar(10),
@MPower float,
@MCurrent float,
@MTension float,
@MArea nchar(10),
@MMachine nchar(10)
as
INSERT INTO Engines
(Manufacturer,Power,Current,Tension,Area,Machine)
VALUES
(@MManufacturer,@MPower,@MCurrrent,@MTension,@MArea,@MMachine)
With this source code we will create a Procedure that is going to insert engine data with the CREATE PROCEDURE command, that has a name as a parameter which will be called procInsertEngine.
Next, we will declare input variables that will receive values sent by the Elipse application. These input variables must start with an @ symbol followed by its data type. After defining the SQL instruction to be executed, an insertion in this case, we use the INSERT INTO command followed by the name of the table, then the column names and after the VALUES command we write down the variables, on the specified order. At the end, click on [Parse] button next to the [Execute!] button.
Figure 4: procInsertEngine Stored Procedure code
Now we create a new Procedure the same way as the previous one; this one, however, will select data from Engines table using a filter by manufacturer, as in the next figure:
Figure 5: procSelectEngine Stored Procedure code
Now we are going to create a Procedure that will delete data on the Engines table, like the next figure:
Figure 6: procDeleteEngine Stored Procedure code
5) Application (E3)
Now that we have created and configured the Stored Procedures on the database, we must understand how to call them on E3.
Create a new project (Standard application) and insert a Database object, setting it to access Devices database, where we configured those Stored Procedures and where there is the Engines table.
Figure 7: Database connection
Insert three queries on the application and name them as QueryInsert, QueryDelete and QuerySelect, where the Insert and Delete queries will remain on the data folder (server) and the Select query will remain on the Initial screen.
Figure 8: Including queries on the project
Let’s start by the Select query: right-click on it and select Configure… option.
Go to SQL tab and enable direct editing of SQL code by typing the following line of code:
Figure 9: Including SQL code
This SQL command will launch (via Exec call) a procedure (name of the Procedure) by passing a variable as an input parameter (‘<%Man%>‘).
Repeat the preceding steps for Insert and Delete queries, changing only the SQL command. For QueryInsert use the command:
For the QueryDelete:
Exec procDeleteEngine ‘<%Man%>‘,’<%Area%>‘,’<%Eng%>‘
NOTE: For in-server queries, change CursorLocation property to 1 – clClient.
After configuring queries, let’s create an interface on the screen where the user can see the devices already registered, insert new devices and also delete them.
Figure 10: Application interface
Insert an E3Browser and configure it to use QuerySelect.
Figure 11: E3Browser configuration
Insert a Setpoint on the screen and a button to fetch data, create a script on the Click() event of the button and type the following script:
Figure 12: Command button script
Now create a button to insert new data; this button will open a second screen where the user will type the engine data and confirm them, inserting that data on Devices table.
Figure 13: Screen to include engines
Once done with the screen, create a script on [OK] button that will execute QueryInsert:
Figure 14: Code to include an engine
Finally, we will create a button on the initial screen to delete records from Engines table; we will use the following script on Click() event of the [Delete] button:
Figure 15: Code to exclude an engine
6) Conclusion
The task of manipulating data on a database becomes really effective, because the code is centralized, just like in libraries, and when executed it is enhanced by SQL itself, what improves application performance.
Attachments:
