Getting started with the Stored Procedure unit

From Wiki

Jump to: navigation, search

Contents

[edit]

Introduction

The Stored Procedure Unit is an operation unit that allows to call a procedure stored in a database. The procedure must be previously created in your database.

To check if there are user-defined procedures in your database you can use the Refresh option on the Database node in the Outline View. In order to force the Refresh command to load the metadata for the procedures you have to set some options in the Properties View on the Database node. First of all you have to check the Read Procedure option. This option allows you to see the complete list of database procedures, which means that you can see the user-defined and the system-defined procedures. If you want to filter the list and retrieve for example only the user-defined ones, you have to set the Schema Mask property, typing the schema, or the pipe separated schemas list, to which procedures belong to. Note that this property affects also the list of retrieved tables corresponding to the selected schemas. The figures on the right show an example of stored procedures retrieved from a PostreSQL database.

Each time you want to use a Stored Procedure unit in your Web project you have to set some mandatory properties for the unit.

  • Database. The database to which the procedure to invoke belongs to. You can choose the database from a list showing the databases defined in the Web Project.
  • Procedure. The name of the procedure to invoke. The name must be the a valid stored procedure name that you can see in the metadata. You can choose one of the stored procedures, retrieved by the refresh, from the dropdown or you can manually edit the name of the stored procedure.
  • Result Type. The type of the result provided by the procedure. If the result is a simple type value (e.g. integer, string) choose the "Plain" option. If the result type is a simple type value, but the invoked procedure is a MySQL function, choose "Plain (MySQL Function). If the result is a set of elements (e.g. a list of products) choose the "Record set" option.

Moreover you have to define the inputs and outputs of the Stored Procedure unit. Right click on the unit and choose the Add Parameter command. You have to add as many inputs as the number of input parameters required by the procedure and as many outputs as the number of the procedure results. For each parameter you have to set the properties:

  • Name. A user-defined name for the parameter.
  • Type. The type of the parameter. You can choose from a list of predefined types.
  • Direction. Specifies whether the parameter is an input or an output for the Stored Procedure unit.
  • Position. The position of the parameter (starting from 1).

This article describes how to use the Stored Procedure unit to call procedures stored in four different database types

  1. PostreSQL
  2. SQL Server
  3. Oracle
  4. MySQL
[edit]

How to invoke a PostgreSQL stored procedure

You can use a new empty Web Project to work on this example. First of all add a new Database node and connect to a new PostgreSQL database. In order to export all the tables related to the user model Synchronize the database. Then create a new procedure in this database. Suppose you want to define a procedure that sums two numbers. The stored procedure will include two input parameters ($1 and $2) and a plain output parameter. The SQL code can be as follows: 

CREATE OR REPLACE FUNCTION SUM(INTEGER, INTEGER)
RETURNS INTEGER AS 'SELECT ($1 + $2) AS RESULT;' LANGUAGE SQL;

You can manually add the stored procedure from your database administration program, or you can download this SQL script file and execute it directly from WebRatio. To execute the file, right click on the Database node from the Outline View and choose the Execute SQL Script... action.

Now you have to create a simple hypertext in order to invoke this stored procedure and to see the result. The following figure shows a Web model example.

To create this model you have to:

  • Add a Site View to your Web project.
  • Add a Page to the Site View. The page is automatically set as the Home page.
  • Add an Entry unit to the page. The Entry unit has three fields. The first two fields are used to provide the input numbers to the Stored Procedure unit. The third field is used to show the result of the stored procedure, therefore it must be preloaded. All the fields must have Integer as type.
  • Add a Stored Procedure unit and configure it in order to call the procedure defined before:
    1. The Database property must be the database used in the Web project.
    2. The Procedure property of the unit must be set to "SUM".
    3. The Result Type property must be set to Plain.
  • Add the parameters to the Stored Procedure unit.
    1. The first parameter is associated with the $1 input parameter. Its properties are: Type = Integer, Direction = Input, and Position = 1.
    2. The second parameter is associated with the $2 input parameter Its properties are: Type = Integer, Direction = Input, and Position = 2.
    3. The third parameter is associated with the output parameter. Its properties are: Type = Integer, Direction = Output, and Position = 1.
  • Connect the Entry unit and the Stored Procedure unit with a normal link named "Invoke". In the coupling dialog, assign each field to an input parameter of the stored procedure unit.
  • Connect the Stored Procedure unit and the Entry Unit with an OK link. In the coupling dialog, assign the result of the Stored Procedure unit to the preloaded field of the Entry unit.

N.B. The value of the Position property of input parameters in the unit match the positions of the associated input parameters in the procedure declaration. The unique plain output parameter, instead, has Position = 1.


This second example shows you how to call a procedure with Result Type "Record set". In order to work on this example you can use the Acme project. If you do not have the Acme project in your workspace you can easily download it from the Help -> WebRatio Samples menu. You have to create a new database PostgreSQL and connect your Web Project to that database. Download and execute the Acme_PostgreSQL_Data.sql in order to create tables, views and data used by the Web Project. Then create a store procedure like the following: 

CREATE OR REPLACE FUNCTION FINDPRODUCTS(INTEGER, VARCHAR)
RETURNS SETOF VARCHAR AS '
SELECT name FROM product
WHERE price > $1 AND price < $2
' LANGUAGE SQL;

You can manually add the store procedure from your database administration program, or you can download the SQL script file and execute it directly from WebRatio. To execute the file, right click on the Database node from the Outline View and choose the Execute SQL Script... action.

Let's create a simple hypertext like the following in order to invoke this stored procedure and to see the result.

  • Add a Site View to your Web project.
  • Add a Page to the Site View. The page is automatically set as the Home page.
  • Add an Entry unit to the page. This Entry unit has two fields. The first one represents the minimum price and the second one represents the maximum price of the products to find.
  • Add a Stored Procedure unit and configure it in order to call the procedure defined before:
    1. The Database property must be the database used in the Web project.
    2. The Procedure property of the unit must be set to "findproducts".
    3. The Result Type property must be set to Record Set.
  • Add the parameters to the Stored Procedure unit.
    1. The first parameter is associated with the $1 input parameter. Its properties are: Type = Integer, Direction = Input, and Position = 1.
    2. The second parameter is associated with the $2 input parameter Its properties are: Type = Integer, Direction = Input, and Position = 2.
    3. The third parameter is associated with the "name" output parameter. Its properties are: Type = String, Direction = Output, and Position = 1.
  • Connect the Entry unit and the Stored Procedure unit with a normal link named "Invoke". In the coupling dialog, assign each field to an input parameter of the stored procedure unit.
  • Add an Index unit with Product as entity. Add an Attribute Condition to this unit in which you check that the name of the product corresponds to a name in the passed list. Select the Display attributes of the unit in order to see the result of the stored procedure.
  • Connect the Stored Procedure unit and the Index Unit with an OK link. In the coupling dialog, assign the result of the Stored Procedure unit to the Attribute Condition of the Index unit.

N.B. The value of the Position property of input parameters in the unit match the positions of the associated input parameters in the procedure declaration. The unique plain output parameter, instead, has Position = 1.

[edit]

How to invoke a SQL Server stored procedure

You can use a new empty Web project to work on this example. First of all add a new Database node and connect to a new SQLServer database. In order to export all the tables related to the user model Synchronize the database. Create a new procedure in this database. The stored procedure will include two input parameters (@in1 and @in2) and two plain output parameters (@out1 and @out2). The SQL code can be as follows: 

CREATE PROCEDURE TRANSFORM (@in1 int,
@in2 varchar(50),
@out1 int OUTPUT,
@out2 varchar(60) OUTPUT)
AS SELECT
@out1 = 2 * @in1,
@out2 = 'Hello, ' + @in2;

This stored procedure is different from the previous ones, because it has two different outputs, a number and a string. For this reason, the stored procedure is declared using a different SQL code structure, in which all parameters (inputs and outputs) are declared at the beginning.

You can add this store procedure manually from your database administration program, or you can download the SQL script file and execute it directly from WebRatio. To execute, right click on the Database node from the Outline View and choose the Execute SQL Script.. option.

Now you have to create a simple hypertext in order to invoke this stored procedure and to see the result. The following figure shows a Web Model example.

To create this model you have to:

  • Add a Site View to your Web project.
  • Add a Page to the Site View. The page is automatically set as the Home page.
  • Add an Entry unit to the page. This Entry unit has four fields.
    1. The "Number" field, in which the user types a number that will be used by the stored procedure
    2. The "Name" field, in which the user types his name.
    3. The "Result" field, which must be preloaded, in which the user sees the result of the operation
    4. The "Hello" field, which must be preloaded, in which the user sees the "Hello" string created by the operation
  • Add a Stored Procedure unit and configure it in order to call the stored procedure specified before.
    1. The Database property must be the database used in the Web Project.
    2. The Procedure property of the unit must be set to "transform".
    3. The Result Type property must be set to Plain.
  • Add the parameters to the Stored Procedure unit.
    1. The first parameter is associated with the "@in1" input parameter. Its properties are: Type = Integer, Direction = Input, and Position = 1.
    2. The second parameter is associated with the "@in2" input parameter Its properties are: Type = String, Direction = Input, and Position = 2.
    3. The third parameter is associated with the "@out1" output parameter. Its properties are: Type = Integer, Direction = Output, and Position = 3.
    4. The fourth parameter is associated with the "@out2" output parameter. Its property are: Type = String, Direction = Output, and Position = 4.
  • Connect the Entry unit and the Stored Procedure unit with a normal link named "Invoke". In the coupling dialog, assign the two not preloaded fields to the input parameters of the stored procedure unit.
  • Connect the Stored Procedure unit and the Entry Unit with an OK link. In the coupling dialog, assign the result of the Stored Procedure unit to the preloaded fields of the Entry unit.

N.B. It is important to note that the value of the Position property of input and output parameters in the unit match the positions of the associated parameters in the procedure declaration.


This second example shows you how to call a procedure with Result Type "Record set". In order to work on this example you can use the Acme project. If you do not have the Acme project in your workspace you can easily download it from the Help -> WebRatio Samples. You have to create a new database SQL Server and connect your Web project to that database. Download and execute the Acme_SQLServer_Data.sql in order to create tables, views and data used by the Web project. Then create a store procedure like the following: 

CREATE PROCEDURE FINDPRODUCTS (@inprice float,
@inkeyword varchar(50))
AS
SELECT name, price FROM product
WHERE price < @inprice AND description LIKE '%' + @inkeyword + '%' ;

You can manually add the store procedure from your database administration program, or you can download SQL script file and execute it directly from WebRatio. To execute the file you have to right click on the Database node from the Outline View and choose the Execute SQL Script.. option.

Let's create a simple hypertext like the following in order to invoke this stored procedure and to see the result.

  • Add a Site View to your Web project.
  • Add a Page to the Site View. The page is automatically set as the Home page.
  • Add an Entry unit to the page. This Entry unit has two fields. The first one represents the minimum price and the second one represents the keyword of the products to find.
  • Add a Stored Procedure unit and configure it in order to call the stored procedure specified before.
    1. The Database property must be the database used in the Web Project.
    2. The Procedure property of the unit must be set to "findproducts".
    3. The Result Type property must be set to Record Set.
  • Add the parameters to the Stored Procedure unit.
    1. The first parameter is associated with the "@inprice" input parameter. Its properties are: Type = Integer, Direction = Input, and Position = 1.
    2. The second parameter is associated with the "@inkeyword" input parameter Its properties are: Type = String, Direction = Input, and Position = 2.
    3. The third parameter is associated with the "name" output parameter. Its properties are: Type = String, Direction = Output, and Position = 1.
    4. The fourth parameter is associated with the "price" output parameter. Its property are: Type = Float, Direction = Output, and Position = 2.
  • Connect the Entry unit and the Stored Procedure unit with a normal link named "Invoke". In the coupling dialog, assign each field to an input parameter of the stored procedure unit.
  • Add an Index unit on the Product entity. Add an Attribute Condition to this unit in which you check that the name of the product corresponds to a name in the passed list. Add a second Attribute Condition that verifies that the price of the product is contained into the prices list. Select the Display attributes of the unit in order to see the result of the stored procedure.
  • Connect the Stored Procedure unit and the Index Unit with an OK link. In the coupling dialog, assign the result of the Stored Procedure unit to the Attribute Conditions of the Index unit.
[edit]

How to invoke a Oracle stored procedure

You can use a new empty Web Project to work on this example. First of all add a new Database node and connect to a new Oracle database. In order to export all the tables related to the user model syncronize the database. Then create a new procedure in this database. The stored procedure will include two input parameters (in1 and in2) and two plain output parameters (out1,out2). The SQL code can be something like the following: 

CREATE OR REPLACE PROCEDURE TRANSFORM (
in1 in number,
in2 in varchar2,
out1 out number,
out2 out varchar2)
IS
BEGIN
out1 := 2 * in1;
out2 := 'Hello, ' || in2;
END;

You must manually add this store procedure from your database administration program, because the Execute SQL Script... action does not properly work since Oracle needs the ";" character to end a line of the script. WebRatio uses the ";" character to identify the end of a SQL instruction, so there is a conflict.

Now you have to create a simple hypertext in order to invoke this stored procedure and to see the result. You can copy and paste the model implemented in the previous section for the SQLServer database. The model is the same. If you want to do it from scracth follow this guidelines:

To create this model you have to:

  • Add a Site View to your Web project.
  • Add a Page to the Site View. The Page is automatically set as the Home page.
  • Add an Entry unit to the page. This Entry unit has four fields.
    1. The "Number" field, in which the user types a number that will be used by the stored procedure
    2. The "Name" field, in which the user types his name.
    3. The "Result" field, which must be preloaded, in which the user sees the result of the operation
    4. The "Hello" field, which must be preloaded, in which the user sees the "Hello" string created by the operation
  • Add a Stored Procedure unit and configure it in order to call the stored procedure specified before.
    1. The Database property must be the database used in the Web project.
    2. The Procedure property of the unit must be set to "transform".
    3. The Result Type property must be set to Plain.
  • Add the parameters to the Stored Procedure unit.
    1. The first parameter is associated with the "in1" input parameter. Its properties are: Type = Integer, Direction = Input, and Position = 1.
    2. The second parameter is associated with the "in2" input parameter Its properties are: Type = String, Direction = Input, and Position = 2.
    3. The third parameter is associated with the "out1" output parameter. Its properties are: Type = Integer, Direction = Output, and Position = 3.
    4. The fourth parameter is associated with the "out2" output parameter. Its properties are: Type = String, Direction = Output, and Position = 4.
  • Connect the Entry unit and the Stored Procedure unit with a normal link named "Invoke". In the coupling dialog, assign the two not preloaded fields to the input parameters of the stored procedure unit.
  • Connect the Stored Procedure unit and the Entry Unit with an OK link. In the coupling dialog, assign the result of the Stored Procedure unit to the preloaded fields of the Entry unit.

N.B. It is important to note that the value of the Position property of input and output parameters in the unit match the positions of the associated parameters in the procedure declaration.

[edit]

MySQL stored procedures and functions

When the database is MySQL, you have to pay attention to the settings of the Stored Procedure Unit. In fact the call to a stored procedure is quite different from the call of a function. The main differences are:

  • The Position of the input and outputs parameters.
  • The Result Type of the stored procedure unit.

Let's see an example of procedure and an example of function

[edit]

How to invoke a MySQL stored procedure

You can use a new empty Web Project to work on this example. First of all add a new Database node and connect to a new MySQL database. In order to export all the tables related to the user model syncronize the database. Then create a new procedure in this database. The stored procedure will include two input parameters (minprice, maxprice) and a plain output parameters (total). The SQL code can be something like the following: 

CREATE PROCEDURE COUNTPRODUCTS (IN minprice float, IN maxprice float, OUT total integer)
BEGIN
     set total = (SELECT count(*) FROM `product`
                  WHERE `price` >= minprice AND `price` <= maxprice);
END

You must manually add this store procedure from your database administration program, because the Execute SQL Script... action does not properly work since MySQL needs the ";" character to end a line of the script. WebRatio uses the ";" character to identify the end of an SQL instruction, so there is a conflict.

Now you have to create a simple hypertext in order to invoke this stored procedure and to see the result.

To create this model you have to:

  • Add a Site View to your Web project.
  • Add a Page to the Site View. The Page is automatically set as the Home page.
  • Add an Entry unit to the page. This Entry unit has two fields.
    1. The "Min Price" field, in which the user types a number that will be used by the stored procedure
    2. The "Max Price" field, in which the user types his name.
  • Add a Stored Procedure unit and configure it in order to call the stored procedure specified before.
    1. The Database property must be the database used in the Web project.
    2. The Procedure property of the unit must be set to "COUNTPRODUCTS".
    3. The Result Type property must be set to Plain.
  • Add the parameters to the Stored Procedure unit.
    1. The first parameter is associated with the "minPrice" input parameter. Its properties are: Type = Float, Direction = Input, and Position = 1.
    2. The second parameter is associated with the "maxPrice" input parameter Its properties are: Type = Float, Direction = Input, and Position = 2.
    3. The third parameter is associated with the "total" output parameter. Its properties are: Type = Integer, Direction = Output, and Position = 3.
  • Connect the Entry unit and the Stored Procedure unit with a normal link named "Invoke". In the coupling dialog, assign the two fields to the input parameters of the stored procedure unit.
  • Add a Multi Message Unit to show the result of the stored procedure.
  • Connect the Stored Procedure unit and the Multi Message Unit with an OK link. In the coupling dialog, assign the result of the Stored Procedure unit to the shown messages.

N.B. It is important to note that the value of the Position property of input and output parameters in the unit match the positions of the associated parameters in the procedure declaration.

[edit]

How to invoke a MySQL stored function

You can use a new empty Web Project to work on this example. First of all add a new Database node and connect to a new MySQL database. In order to export all the tables related to the user model syncronize the database. Then create a new procedure in this database. The stored procedure will include two input parameters (minprice, maxprice) and a plain output parameters (total). The SQL code can be something like the following: 

CREATE FUNCTION COUNTPRODUCTSFUNCTION(minPrice float, maxPrice float)
RETURNS integer
BEGIN
     DECLARE total integer;
     SELECT count(*) into total FROM `product`
         WHERE `price` >= minPrice AND  `price` <= maxprice;
     RETURN total;
END

You must manually add this store procedure from your database administration program, because the Execute SQL Script... action does not properly work since MySQL needs the ";" character to end a line of the script. WebRatio uses the ";" character to identify the end of an SQL instruction, so there is a conflict.

Now you have to create a simple hypertext in order to invoke this stored procedure and to see the result.

To create this model you have to:

  • Add a Site View to your Web project.
  • Add a Page to the Site View. The Page is automatically set as the Home page.
  • Add an Entry unit to the page. This Entry unit has two fields.
    1. The "Min Price" field, in which the user types a number that will be used by the stored procedure
    2. The "Max Price" field, in which the user types his name.
  • Add a Stored Procedure unit and configure it in order to call the stored procedure specified before.
    1. The Database property must be the database used in the Web project.
    2. The Procedure property of the unit must be set to "COUNTPRODUCTSFUNCTION".
    3. The Result Type property must be set to Plain (MySQL Function).
  • Add the parameters to the Stored Procedure unit.
    1. The first parameter is associated with the "minPrice" input parameter. Its properties are: Type = Float, Direction = Input, and Position = 2.
    2. The second parameter is associated with the "maxPrice" input parameter Its properties are: Type = Float, Direction = Input, and Position = 3.
    3. The third parameter is associated with the "total" output parameter. Its properties are: Type = Integer, Direction = Output, and Position = 1.


The position property depends on the mysql-connector-java.jar version. If the version of your jar is 3.1.12 the positions to be set are
  • minPrice = 1
  • maxPrice = 2
  • total = 1


  • Connect the Entry unit and the Stored Procedure unit with a normal link named "Invoke". In the coupling dialog, assign the two fields to the input parameters of the stored procedure unit.
  • Add a Multi Message Unit to show the result of the stored procedure.
  • Connect the Stored Procedure unit and the Multi Message Unit with an OK link. In the coupling dialog, assign the result of the Stored Procedure unit to the shown messages.

N.B. It is important to note that the value of the Position property of input and output parameters is different from the positions in the previous example. Moreover the Result Type is set to Plain (MySQL Function).

[edit]

Download the Sample Project

Click on the following link to download a sample project containing all the resources to test this article.

Acme Stored Procedure Project

Retrieved from "http://wiki.webratio.com/index.php/Getting_started_with_the_Stored_Procedure_unit"
Personal tools