Stored Procedure Application Overlay

This chapter explains the use of the Stored Procedure Application Overlay in the IPP Modeling perspective. It's provided as a technology preview.

The Stored Procedure Application Overlay allows the process designer to:

Pre-requisites

To explain how the Stored Procedure Application Overlay works, the examples below are using a RDBMS having:

Example 1: Select all

This example shows how to use a Stored Procedure application to invoke a procedure having a select request on the projects table. The query result is assigned to an SDT and displayed on the screen.

In this example, the GetProjectsSP activity is using a Stored Procedure Application.

Stored Procedure application

To create a new Stored Procedure application, select Stored Procedure Invocation menu item from the Applications pop-up menu accessible from the Process Model tree:

Structured data types

Since the stored procedure will return a list of projects, you have first to define the Project structured data type having attributes similar to the table columns. The Projects structured data type has 0 or more Project.

Parameters tab

In the Parameters tab, you have to define the input/output parameters, those parameters can be primitive or structured data.

Configuration tab

In the Configuration tab, enter the stored procedure query:

Data source tab

In the Data Source tab, enter the data source connection details (host, port, user, etc.):

Once the process deployed and executed, the stored procedure result should be displayed as follow:

Notes

Example 2: Stored procedure with IN/OUT parameters

The purpose of this example is to show how to invoke a stored procedures with in/out parameters. In this case, the user enter two numbers, then the calculate stored procedure is used to add and multiply their values.

The in/out parameters are defined in the Stored Procedure Application parameter tab

In the Configuration tab, the stored procedure is invoked as below:

When running the process, if we enter "2" and "12" as parameters, the result will be as follow:

Example 3: Stored procedure using Oracle Cursor

The following example show how to invoke an Oracle stored procedure using a cursor. For this purpose, the following procedure is used.

create or replace
PROCEDURE GETALLPROJECTS_CURSOR 
(projects out SYS_REFCURSOR) as
BEGIN 
open projects for
  select * from projects; 
END;

The procedure opens a query using a SYS_REFCURSOR output parameter to return the records in the projects table. The following steps describe how to build a model to invoke the stored procedure.

Example 4: Stored procedure with User defined type

This section explains how to invoke an Oracle stored procedure that returns a user defined type. For this purpose, the following example is used.

User defined type

-- user defined type
create or replace
TYPE member_typ
AS
  OBJECT
  (
    ID NUMBER(6),
    PROJECT  VARCHAR2(50),
    LICENSE VARCHAR2(15) );

Stored procedure

--procedure with user defined type as OUT param
create or replace 
PROCEDURE getProjects(projectInId IN NUMBER, projectOut OUT member_typ)
AS
vID NUMBER(6);
vPROJECT  VARCHAR2(50);
vLICENSE VARCHAR2(15);
BEGIN
    SELECT p.ID, p.PROJECT, p.LICENSE  INTO
      vID,
      vPROJECT,
      vLICENSE
    FROM SYSTEM.projects p where id = projectInId;
    projectOut := member_typ(vID,vPROJECT,vLICENSE);
END getProjects;

The procedure selects the projects table based on the the id column and return the output using the defined type.

The following steps describe how to build a model to invoke the stored procedure.

Dependencies

To use the Stored Procedure application, add the following dependency to the pom.xml file.

<dependency>
  <groupId>com.infinity.integration</groupId>
  <artifactId>stored-procedure-connector</artifactId>
  <version>9.2.0</version>
</dependency>

Add the action-mapping.properties file to the resources folder.

#interface SimpleService
doService=doService
	
#interface UpdateService
query=doInquiryService
update=doUpdateService

#interface DaoService
{
create=create
read=read
update=update
delete=delete
find=find

#interface TransactionService
add=addMultiple
remove=removeTransaction
change=changeTransaction
delete=deleteTransaction
find=find