Getting started with the Stored Procedure unit

by Michela Frigerio
14,219 views Published on Oct 24, 2011
Applies to: All versions
Table of contents

Introduction

The Stored Procedure Operation is an operation 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 Procedures 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 following figures show an example of stored procedures retrieved from a PostgreSQL database.

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

  • 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 Operation. Right-click on the Operation and choose the "Add" and then "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 Operation.
  • Position. The position of the parameter (starting from 1).

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

  1. PostgreSQL
  2. SQL Server
  3. Oracle
  4. MySQL

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 use the SumPostgreSQL.sql script file contained in the SQL folder of the attached project and execute it directly from WebRatio. To execute the file, right-click on the Database node from the Outline View, choose the Execute SQL... action and then select the script file.

Now you have to create a IFML model in order to invoke this stored procedure and to see the result. The following figure shows an 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 a Form Component to the page. The Form Component has three Fields. The first two Fields are used to provide the input numbers to the Stored Procedure Operation. 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 an Action and create an Action Definition to call the stored procedure.
    • Add a Stored Procedure Operation in the Action Definition 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 operation must be set to "sum".
      3. The "Result Type" property must be set to Plain.
    • Add the parameters to the Stored Procedure Operation.
      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 Input Port and the Stored Procedure Operation with an OK Flow. To create and bind the input port parameters with the input parameters of the Stored Procedure Operation, right-click on the Input Port and choose the "Input Port Wizard..." command. In the dialog select the OK Flow and then select all input parameters.
    • Add an OK Port.
    • Connect the Stored Procedure Operation and the OK Port with an OK Flow. To create and bind the output parameters of the Stored Procedure operation with the output port parameters, right-click on the OK Port and choose the "Output Port Wizard..." command. In the dialog select the OK Flow and then select all output parameters.
  • Connect the Form Component and the Action with a Normal Navigation Flow named "Invoke". In the binding dialog, assign each field to an input parameter of the Action.
  • Connect the Action and the Form Component with an OK Flow. In the binding dialog, assign the result of the Action to the preloaded field of the Form Component.

N.B. The value of the "Position" property of input parameters in the operation 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 Stored Procedure" Project. If you do not have the "Acme Stored Procedure" Project in your workspace you can easily download it from the article. You have to create a new database PostgreSQL and connect your Web Project to that database. You can execute the PrepareDB_PostgreSQL.sql file, contained in the DBScripts folder of the project in order to create tables, views and data used by the Web Project. Then create a stored 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 stored procedure from your database administration program, or you can use the FindProductsPostgreSQL.sql script file contained in the SQL folder of the attached project and execute it directly from WebRatio. To execute the file, right-click on the Database node from the Outline View, choose the "Execute SQL..." action and then select the script file.

Let's create a IFML model 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 a Form Component to the page. This Form Component has two Fields. The first one represents the minimum price and the second one represents the maximum price of the products to find.
  • Add an Action and create an Action Definition to call the stored procedure.
    • Add a Stored Procedure Operation in the Action Definition 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 operation must be set to "findproducts".
      3. The "Result Type" property must be set to Record Set.
    • Add the parameters to the Stored Procedure Operation.
      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 Input Port and the Stored Procedure Operation with an OK Flow. To create and bind the input port parameters with the input parameters of the Stored Procedure Operation, right-click on the Input Port and choose the "Input Port Wizard..." command. In the dialog select the OK Flow and then select all input parameters.
    • Add an OK Port.
    • Connect the Stored Procedure Operation and the OK Port with an OK Flow. To create and bind the output parameters of the Stored Procedure Operation with the output port parameters, right-click on the OK Port and choose the Output Port Wizard... command. In the dialog select the OK Flow and then select all output parameters.
  • Connect the Form Component and the Action with a Normal Navigation Flow named "Invoke". In the binding dialog, assign each field to an input parameter of the Action Component.
  • Add a Simple List Component with "Product" as Entity. Add an Attribute Condition to this Component in which you check that the name of the product corresponds to a name in the passed list. Select the Display Attributes of the Component in order to see the result of the stored procedure.
  • Connect the Action and the Simple List Component with an OK Flow. In the binding dialog, assign the result of the Action to the Attribute Condition of the Simple List Component.

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

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 stored procedure manually from your database administration program, or you can use the TransformSQLServer.sql script file contained in the SQL folder of the attached project and execute it directly from WebRatio. To execute, right click on the Database node from the Outline View, choose the "Execute SQL.." option and then select the script file.

Now you have to create a IFML model in order to invoke this stored procedure and to see the result. The following figure shows an 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 a Form Component to the page. This Form component 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 an Action and create an Action Definition to call the stored procedure.
    • Add a Stored Procedure Operation in the Action Definition 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 operation must be set to "transform".
      3. The "Result Type" property must be set to Plain.
    • Add the parameters to the Stored Procedure Operation.
      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 Input Port and the Stored Procedure Operation with an OK Flow. To create and bind the input port parameters with the input parameters of the Stored Procedure Operation, right-click on the Input Port and choose the "Input Port Wizard..." command. In the dialog select the OK Flow and then select all input parameters.
    • Add an OK Port.
    • Connect the Stored Procedure Operation and the OK Port with an OK Flow. To create and bind the output parameters of the Stored Procedure Operation with the output port parameters, right-click on the OK Port and choose the "Output Port Wizard..." command. In the dialog select the OK Flow and then select all output parameters.
  • Connect the Form Component and the Action with a Normal Navigation Flow named "Invoke". In the binding dialog, assign the two not preloaded fields to the input parameters of the Action.
  • Connect the Action and the Form Component with an OK Flow. In the binding dialog, assign the result of the Action to the preloaded fields of the Form Component.

N.B. It is important to note that the value of the "Position" property of operation and output parameters in the operation 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 Stored Procedure project. If you do not have the Acme Stored Procedure project in your workspace you can easily download it from the article. You have to create a new database SQL Server and connect your Web Project to that database. Execute the PrepareDB_SQLServer.sql file, contained in the DBScripts folder of the project 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 stored procedure from your database administration program, or you can select the FindProductsSQLServer.sql script file contained in the SQL folder of the project and execute it directly from WebRatio. To execute the file you have to right click on the Database node from the Outline View, choose the "Execute SQL.." option and select the script file.

Let's create a IFML model 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 a Form Component to the page. This Form Component has two Fields. The first one represents the minimum price and the second one represents the keyword of the products to find.
  • Add an Action and create an Action Definition to call the stored procedure.
    • Add a Stored Procedure Operation in the Action Definition 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 operation must be set to "findproducts".
      3. The "Result Type" property must be set to Record Set.
    • Add the parameters to the Stored Procedure Operation.
      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 Input Port and the Stored Procedure Operation with an OK Flow. To create and bind the input port parameters with the input parameters of the Stored Procedure Operation, right-click on the Input Port and choose the "Input Port Wizard..." command. In the dialog select the OK Flow and then select all input parameters.
    • Add an OK Port.
    • Connect the Stored Procedure Operation and the OK Port with an OK Flow. To create and bind the output parameters of the Stored Procedure Operation with the output port parameters, right-click on the OK Port and choose the "Output Port Wizard..." command. In the dialog select the OK Flow and then select all output parameters.
  • Connect the Form Component and the Action with a Normal Navigation Flow named "Invoke". In the binding dialog, assign each field to an input parameter of the Action.
  • Add an Simple List Component on the "Product" Entity. Add an Attribute Condition to this Component 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 Component in order to see the result of the stored procedure.
  • Connect the Action and the Simple List Component with an OK Flow. In the binding dialog, assign the result of the Action to the Attribute Conditions of the Simple List Component.

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 stored procedure from your database administration program, because the Execute SQL... 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 IFML model 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 a Form Component to the page. This Form Component 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 an Action and create an Action Definition to call the stored procedure.
    • Add a Stored Procedure Operation in the Action Definition 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 operation must be set to "transform".
      3. The "Result Type" property must be set to Plain.
    • Add the parameters to the Stored Procedure Operation.
      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 Input Port and the Stored Procedure Operation with an OK Flow. To create and bind the input port parameters with the input parameters of the Stored Procedure Operation, right-click on the Input Port and choose the "Input Port Wizard..." command. In the dialog select the OK Flow and then select all input parameters.
    • Add an OK Port.
    • Connect the Stored Procedure Operation and the OK Port with an OK Flow. To create and bind the output parameters of the Stored Procedure Operation with the output port parameters, right-click on the OK Port and choose the "Output Port Wizard..." command. In the dialog select the OK Flow and then select all output parameters.
  • Connect the Form Component and the Action with a Normal Navigation Flow named "Invoke". In the binding dialog, assign the two not preloaded fields to the input parameters of the Action.
  • Connect the Action and the Form Component with an OK Flow. In the binding dialog, assign the result of the Action to the preloaded fields of the Form Component.

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

MySQL stored procedures and functions

When the database is MySQL, you have to pay attention to the settings of the Stored Procedure Operation. 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 operation.

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

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... 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 IFML model 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 a Form Component to the page. This Form Component 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 an Action and create an Action Definition to call the stored procedure.
    • Add a Stored Procedure Operation in the Action Definition 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 operation must be set to "COUNTPRODUCTS".
      3. The "Result Type" property must be set to Plain.
    • Add the parameters to the Stored Procedure Operation.
      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 Input Port and the Stored Procedure Operation with an OK Flow. To create and bind the input port parameters with the input parameters of the Stored Procedure Operation, right-click on the Input Port and choose the "Input Port Wizard..." command. In the dialog select the OK Flow and then select all input parameters.
    • Add an OK Port.
    • Connect the Stored Procedure Operation and the OK Port with an OK Flow. To create and bind the output parameters of the Stored Procedure Operation with the output port parameters, right-click on the OK Port and choose the "Output Port Wizard..." command. In the dialog select the OK Flow and then select all output parameters.
  • Connect the Form Component and the Action with a Normal Navigation Flow named "Invoke". In the binding dialog, assign the two fields to the input parameters of the Action.
  • Add a Message Component to show the result of the stored procedure.
  • Connect the Action and the Message Component with an OK Flow. In the binding dialog, assign the result of the Action 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 operation match the positions of the associated parameters in the procedure declaration.

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... 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 IFML model 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 a Form Component to the page. This Form component 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 an Action and create an Action Definition to call the stored procedure.
    • Add a Stored Procedure Operation in the Action Definition 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 operation must be set to "COUNTPRODUCTSFUNCTION".
      3. The "Result Type" property must be set to Plain (MySQL Function).
    • Add the parameters to the Stored Procedure Operation.
      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 Input Port and the Stored Procedure Operation with an OK Flow. To create and bind the input port parameters with the input parameters of the Stored Procedure Operation, right-click on the Input Port and choose the "Input Port Wizard..." command. In the dialog select the OK Flow and then select all input parameters.
    • Add an OK Port.
    • Connect the Stored Procedure Operation and the OK Port with an OK Flow. To create and bind the output parameters of the Stored Procedure Operation with the output port parameters, right-click on the OK Port and choose the "Output Port Wizard..." command. In the dialog select the OK Flow and then select all output parameters.
  • Connect the Form Component and the Action with a Normal Navigation Flow named "Invoke". In the binding dialog, assign the two fields to the input parameters of the Action.
  • Add a Message Component to show the result of the stored procedure.
  • Connect the Action and the Message Component with an OK Flow. In the binding dialog, assign the result of the Action 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).