{"id":360,"date":"2019-03-25T17:30:28","date_gmt":"2019-03-25T20:30:28","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/stored-procedures\/"},"modified":"2019-07-05T16:38:30","modified_gmt":"2019-07-05T19:38:30","slug":"stored-procedures","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/","title":{"rendered":"Stored Procedures."},"content":{"rendered":"
\n

1) Introduction<\/b><\/p>\n

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.<\/p>\n

2) Queries in E3<\/b><\/p>\n

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.<\/p>\n

The E3 query works the following way:<\/p>\n

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.<\/p>\n

When network traffic is not a problem, using queries is the best option and the most recommended one.<\/p>\n

3) Using stored procedures<\/b><\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

4) Application (Database)<\/b><\/p>\n

An example application was created to illustrate how to use Stored Procedures to insert, read and delete records on a database.<\/p>\n

Basically the application allows data maintenance on the Engine <\/b>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.<\/p>\n

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.<\/p>\n

\"\"
\nFigure 1<\/b>: SQL Server connection<\/span><\/div>\n

With SQL Server open, we will create a new database named Devices <\/b>and an Engine <\/b>table inside it. This table will contain the columns Manufacturer<\/b>, Area<\/b>, Machine<\/b>, Power<\/b>, Current<\/b>, and Tension<\/b>. The fields will be of type nchar(10), nchar(10), nchar(10), float, float and float, respectively, as in the following figure:<\/p>\n

\"\"
\nFigure 2<\/b>: Creating Engines table
\n<\/span><\/div>\n

Inside the Devices <\/b>database we will access the Programmability <\/b>folder. Right-clicking on Stored Procedures<\/b> folder, select New Stored Procedure…<\/b> option.<\/p>\n

\"\"
\nFigure 3<\/b>: Creating the Stored Procedure
\n<\/span><\/div>\n

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.<\/p>\n

Then insert the following source code:
\n
\n<\/span><\/p>\n

CREATE PROCEDURE<\/span> procInsertEngine<\/span>
\n@MManufacturer nchar<\/span>(10),<\/span>
\n@MPower float<\/span>,<\/span>
\n@MCurrent float<\/span>,<\/span>
\n@MTension float<\/span>,<\/span>
\n@MArea nchar<\/span>(10),<\/span>
\n@MMachine nchar<\/span>(10)<\/span>
\nas<\/span>
\n<\/span>INSERT INTO<\/span> Engines<\/span>
\n(Manufacturer,Power,Current,Tension,Area,Machine)<\/span>
\nVALUES<\/span>
\n(@MManufacturer,@MPower,@MCurrrent,@MTension,@MArea,@MMachine)<\/span><\/div>\n

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<\/b>.<\/p>\n

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.<\/p>\n

\"\"
\nFigure 4:<\/b> procInsertEngine Stored Procedure code<\/span><\/div>\n

Now we create a new Procedure the same way as the previous one; this one, however, will select data from Engines <\/b>table using a filter by manufacturer, as in the next figure:<\/p>\n

\"\"
\nFigure 5<\/b>: procSelectEngine Stored Procedure code<\/span><\/div>\n

Now we are going to create a Procedure that will delete data on the Engines table, like the next figure:<\/p>\n

\"\"
\nFigure 6:<\/b> procDeleteEngine Stored Procedure code<\/span><\/div>\n

5) Application (E3)<\/b><\/p>\n

Now that we have created and configured the Stored Procedures on the database, we must understand how to call them on E3.<\/p>\n

Create a new project (Standard application) and insert a Database object, setting it to access Devices <\/b>database, where we configured those Stored Procedures and where there is the Engines <\/b>table.<\/p>\n

\"\"
\nFigure 7<\/b>: Database connection<\/span><\/div>\n

Insert three queries on the application and name them as QueryInsert<\/b>, QueryDelete <\/b>and QuerySelect<\/b>, where the Insert <\/b>and Delete <\/b>queries will remain on the data folder (server) and the Select <\/b>query will remain on the Initial <\/b>screen.<\/p>\n

\"\"
\nFigure 8<\/b>: Including queries on the project<\/span><\/div>\n

Let’s start by the Select <\/b>query: right-click on it and select Configure… <\/b>option.<\/p>\n

Go to SQL <\/b>tab and enable direct editing of SQL code by typing the following line of code:<\/p>\n

Exec procSelectEngine ‘<%Man%>‘ <\/span><\/div>\n

 <\/p>\n

\"\"
\nFigure 9<\/b>: Including SQL code<\/span><\/div>\n

This SQL command will launch (via Exec call) a procedure (name of the Procedure) by passing a variable as an input parameter (‘<%Man%>‘<\/span>).<\/p>\n

Repeat the preceding steps for Insert <\/b>and Delete <\/b>queries, changing only the SQL command. For QueryInsert use the command:<\/p>\n

Exec procInsertEngine ‘<%Man%>‘,<%Pow%>,<%Cur%>,<%Ten%>,’<%Ar%>‘,’<%Eng%>‘<\/span><\/div>\n

For the QueryDelete:<\/p>\n

Exec procDeleteEngine ‘<%Man%>‘,’<%Area%>‘,’<%Eng%>‘ <\/span><\/p>\n

NOTE<\/b>: For in-server queries, change CursorLocation <\/i>property to 1 – clClient<\/b>.<\/p>\n

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.<\/p>\n

\"\"
\nFigure 10<\/b>: Application interface<\/span><\/div>\n

Insert an E3Browser and configure it to use QuerySelect<\/b>.<\/p>\n

\"\"
\nFigure 11<\/b>: E3Browser configuration<\/span><\/div>\n

Insert a Setpoint on the screen and a button to fetch data, create a script on the Click()<\/i> event of the button and type the following script:<\/p>\n

\"\"
\nFigure 12<\/b>: Command button script
\n<\/span><\/div>\n

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 <\/b>table.<\/p>\n

\"\"
\nFigure 13<\/b>: Screen to include engines<\/span><\/div>\n

Once done with the screen, create a script on [OK] button that will execute QueryInsert<\/b>:<\/p>\n

\"\"
\nFigure 14<\/b>: Code to include an engine<\/span><\/div>\n

Finally, we will create a button on the initial screen to delete records from Engines table; we will use the following script on Click() <\/i>event of the [Delete] button:<\/p>\n

\"\"
\nFigure 15<\/b>: Code to exclude an engine
\n<\/span><\/div>\n

6) Conclusion<\/b><\/p>\n

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.<\/p>\n

Attachments:<\/h3>\n

SampleApplication.zip<\/a><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Although E3 already has objects like queries that simplify manipulation of database records, we can improve its performance creating subroutines…
\nAutor
\nRenato Souza<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0},"categories":[735],"tags":[],"yoast_head":"\nStored Procedures. - Elipse Knowledgebase<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Stored Procedures.\" \/>\n<meta property=\"og:description\" content=\"Although E3 already has objects like queries that simplify manipulation of database records, we can improve its performance creating subroutines... Autor Renato Souza\" \/>\n<meta property=\"og:url\" content=\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/\" \/>\n<meta property=\"og:site_name\" content=\"Elipse Knowledgebase\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/elipsesoftware\" \/>\n<meta property=\"article:published_time\" content=\"2019-03-25T20:30:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-07-05T19:38:30+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID96\/Figure1.PNG\" \/>\n<meta name=\"author\" content=\"Elipse Software\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Elipse Software\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/\"},\"author\":{\"name\":\"Elipse Software\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87\"},\"headline\":\"Stored Procedures.\",\"datePublished\":\"2019-03-25T20:30:28+00:00\",\"dateModified\":\"2019-07-05T19:38:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/\"},\"wordCount\":1263,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"articleSection\":[\"DataBases\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/\",\"url\":\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/\",\"name\":\"[:pt]Stored Procedures.[:] - Elipse Knowledgebase\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#website\"},\"datePublished\":\"2019-03-25T20:30:28+00:00\",\"dateModified\":\"2019-07-05T19:38:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\/\/kb.elipse.com.br\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Stored Procedures.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/kb.elipse.com.br\/#website\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"name\":\"Elipse Knowledgebase\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/kb.elipse.com.br\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\",\"name\":\"Elipse Software\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"sameAs\":[\"http:\/\/www.facebook.com\/elipsesoftware\"],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"contentUrl\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"width\":161,\"height\":58,\"caption\":\"Elipse Software\"},\"image\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87\",\"name\":\"Elipse Software\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g\",\"caption\":\"Elipse Software\"},\"url\":\"https:\/\/kb.elipse.com.br\/en\/author\/webmasterelipse-com-br\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Stored Procedures. - Elipse Knowledgebase","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/","og_locale":"en_US","og_type":"article","og_title":"[:pt]Stored Procedures.[:] - Elipse Knowledgebase","og_description":"[:pt]Although E3 already has objects like queries that simplify manipulation of database records, we can improve its performance creating subroutines... Autor Renato Souza[:]","og_url":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/","og_site_name":"Elipse Knowledgebase","article_publisher":"http:\/\/www.facebook.com\/elipsesoftware","article_published_time":"2019-03-25T20:30:28+00:00","article_modified_time":"2019-07-05T19:38:30+00:00","og_image":[{"url":"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID96\/Figure1.PNG"}],"author":"Elipse Software","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Elipse Software","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#article","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/"},"author":{"name":"Elipse Software","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87"},"headline":"Stored Procedures.","datePublished":"2019-03-25T20:30:28+00:00","dateModified":"2019-07-05T19:38:30+00:00","mainEntityOfPage":{"@id":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/"},"wordCount":1263,"commentCount":0,"publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"articleSection":["DataBases"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/","url":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/","name":"[:pt]Stored Procedures.[:] - Elipse Knowledgebase","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/#website"},"datePublished":"2019-03-25T20:30:28+00:00","dateModified":"2019-07-05T19:38:30+00:00","breadcrumb":{"@id":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/kb.elipse.com.br\/en\/stored-procedures\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/kb.elipse.com.br\/en\/stored-procedures\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/kb.elipse.com.br\/en\/"},{"@type":"ListItem","position":2,"name":"Stored Procedures."}]},{"@type":"WebSite","@id":"https:\/\/kb.elipse.com.br\/#website","url":"https:\/\/kb.elipse.com.br\/","name":"Elipse Knowledgebase","description":"","publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/kb.elipse.com.br\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/kb.elipse.com.br\/#organization","name":"Elipse Software","url":"https:\/\/kb.elipse.com.br\/","sameAs":["http:\/\/www.facebook.com\/elipsesoftware"],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/","url":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","contentUrl":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","width":161,"height":58,"caption":"Elipse Software"},"image":{"@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87","name":"Elipse Software","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g","caption":"Elipse Software"},"url":"https:\/\/kb.elipse.com.br\/en\/author\/webmasterelipse-com-br\/"}]}},"_links":{"self":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/360"}],"collection":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/comments?post=360"}],"version-history":[{"count":4,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/360\/revisions"}],"predecessor-version":[{"id":7618,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/360\/revisions\/7618"}],"wp:attachment":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/media?parent=360"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/categories?post=360"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/tags?post=360"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}