SQL Application Overlay

This chapter will explain the use of the SQL Application Overlay in the IPP Modeling perspective.

SQL Application Overlay gives the process designer the ability to manipulate RDBMS data by providing SQL statement.

Setting SQL Application Properties

Apart from the common properties, the following property tabs are provided for an SQL application:

Parameters

In the SQL Application Parameters tab an arbitrary set of Input and Output Parameters can be created. These parameters can be of type primitive or structured data.

In the Parameter Data drop-down list structured data types from the current model or other models can be selected.

The query Result Set Data can be a primitive or a structured data variable from the current or from another model.


Figure: Result set data selection

In the Expected Result drop-down list you can select one of the following options:


Figure: Expected result selection

The following parameters are defined by default. They may not be used in your model.

Configuration Tab

In the Configuration tab, enter the SQL query. In case you are working in the Integrator mode, the following checkboxes are available:


Figure: Configuration tab

Data source Tab

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


Figure: Data Source connection details

Major RDBMS systems (Mysql, Oracle, Postgres) are supported. For other databases, it's possible to enter the URL and the driver as follow:


Figure: Data source details for other databases

Customizing the data source bean name

In case you are working in Integrator mode, you have the option to customize the data source bean name. An additional field Bean Name is provided where you can change the name of the bean.


Figure: Data Source details including field for bean name

Using a configuration variable for a data source password

You have the option to use a configuration variable for the data source password. For this purpose select the Use Configuration Variable for Password checkbox.


Figure: Select Use Configuration Variable for Password

A configuration variable is created with the name entered in the Password field. Enter a password value in the model Configuration Variables tab.


Figure: Enter password value for the configuration variable

Examples

The following example use cases explain how the SQL Application Overlay works.

Pre-requisites

The examples used in this document are using a RDBMS having a table projects with some records defined as follows:

create table projects (id integer primary key, project varchar(10), license varchar(5));
insert into projects values (1, 'Camel', 'ASF');
insert into projects values (2, 'AMQ', 'ASF');
insert into projects values (3, 'Linux', 'XXX');

Example 1: Performing a Select Request on the Projects Table

The following example demonstrates how to use an SQL application type to perform a select request on the projects table. Then the query result is displayed.

In the following process the SelectAll activity is implementing an SQL Application.


Figure: Example process for a select request

Creating the SQL application

To build a new SQL application, select the SQL Invocation menu item from the Applications pop-up menu accessible from the process model outline tree:


Figure: Creating an SQL application

Defining the structured data types

Since the SQL query will return a list of projects, you first have to define the Project structured data type having attributes similar to the SQL table.


Figure: Structured data type Project

The Projects structured data type has 0 or more Project.

Entering the SQL query in the Configuration tab

In the Configuration tab, enter the following SQL query:

select * from projects


Figure: SQL query

Entering the data source connection details

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


Figure: Data Source connection details

Once the model is deployed and you execute the process, the SQL query result should be displayed as in the below screenshot:


Figure: SQL query result

Example 2: Performing a Select Request with Parameter

In the following example, we enter a license value. This value will be used to query projects having such a license.


Figure: Example process for using a parameter

For our SQL application we add an input parameter license of data type primitive in the Parameters tab. Additionally we select Projects as Result Set Data and set Expected Result to List.


Figure: Parameter definitions of the SQL application

The input parameter is passed to the SQL query by using the :#param_name style as shown below:


Figure: Configuration

When running the process, we enter e.g. "ASF" as license parameter:


Figure: Enter parameter value for license

Only the corresponding projects which have this value for their license record will be displayed:


Figure: Output for returned objects

Using Delete, Insert or Update commands

Other examples can be easily done using delete, insert or update sql command.

DELETE FROM projects WHERE id = :#id
INSERT INTO projects (id, license, project) VALUES (:#id, :#license, :#project)
UPDATE projects SET license =: #license WHERE id = :#id

Notes and Restrictions

Please note the following points: