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.
Apart from the common properties, the following property tabs are provided for an SQL application:
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:
SELECT COUNT( * ) FROM PROJECTIf the query returns one row having more than one column, then it will return a Map of that result.
Figure: Expected result selection
The following parameters are defined by default. They may not be used in your model.
select * from projects where license = :#param
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
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
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
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
The following example use cases explain how the SQL Application Overlay works.
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');
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
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
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.
In the Configuration tab, enter the following SQL query:
select * from projects
Figure: SQL query
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
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
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:
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
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
Please note the following points: