The "Run an SQL procedure" action

Applies to version: 2021.1.x and above; author: Konrad Wojtycza

Introduction

The Run an SQL procedure action allows you to invoke SQL procedures from the WEBCON BPS form level, and to prepare the universal SQL queries with variables. This functionality is limited only by the capabilities of the SQL query language. It is possible to invoke SQL procedures using data in the WEBCON BPS form and enter/update data in the database.

Test workflows

This article uses two workflows to showcase running SQL procedures: UK Jobs Change (used in the Data operations article) and Update UK Jobs Change (configured for this article). The configuration and operation of these workflows are described below.

Data input workflow
The following screenshots show a fragment of the modified form containing the fields for entering data into the EMSI_JobChange_UK table.

Fig. 1. The fragment of the UK Job Change workflow form

After following the "Add data" path, the data completed on the form will be saved to the table in the database and will be displayed on the table preview visible on the form.

Fig. 2. The fragment of the UK Job Change workflow form - a table presenting data

Data update workflow
The Update UK Jobs Change workflow form contains the "Data" group where you must select the entry that should be updated. The entry is selected based on the "Industry" and "SIC_1" choice fields corresponding to the columns in the data table with the same names. It is possible to update data in the following fields – make changes and click "Update."

Fig. 3. The fragment of the UK Jobs Change Update workflow form - selecting an entry to be updated

Fig. 4. The fragment of the UK Jobs Change Update workflow form - entering updated data

Configuration of the action

You can configure the "Run an SQL procedure" action in the Actions tab in Designer Studio. For more information, see Introduction to actions in WEBCON BPS. The preparation of this action requires knowledge of SQL.

The WEBCON BPS account must have the privileges to edit the database in which data is stored, as well as to the table into which the data will be inserted.  

Fig. 5. The service account identifier can be found in the System settings -> Services configuration

Fig. 6. The privileges of the webcon/svc.bps service account allows for full management of the database content

Adding an entry to the table in the database

The "Run an SQL procedure" was configured at the "Insert data to table" step, on the "Add data" path.

Fig. 7. The configuration of data entry action - Insert data in the table

In the advanced configuration of this action, create the query – the SQL procedure invoked by the above action on the database. It allows you to enter data from the form into the database. The variables from the form were added to the form using the expression editor.

It is possible to enter subsequent entries in the EMSI_JobChange_UK table without the need to have direct access to the database.

Fig. 8. The procedure for entering data from a form into the EMSI_JobChange_UK table.

Fig. 9. The expression editor

Updating an entry to the table in the database

The ' Run an SQL procedure' action was configured at the "Update data in table" step, on the "Update" path. The action configuration, as before, includes the query - the SQL procedure invoked by the above action on the database.

Fig. 10. The configuration of the action that updates data

The presented SQL procedure allows you to update the selected entry. The query uses the values selected on the form in the "Data" group and the values entered in the editable fields.

Fig. 11. The procedure for updating data in the table EMSI_JobChange_UK

Summary

The correctly configured "Run an SQL procedure" action allows an end-user to access database resources and to edit them without the need to know the SQL language. However, please note that this edition is limited by the action configuration.
Due to the abovementioned possibilities, the access to the processes using the "Run an SQL procedure" action should be limited, as this action allows interference with the data stored in the database.
The use of this action with API provided by WEBCON BPS allows you to save data from external databases such as ERP systems or a non-standard organizational structure. Downloading data from external systems can be organized in time and take place cyclically (e.g. at night). This functionality can be helpful if there are connection performance problems during working hours.