RunSqlScriptHandler

Description

With the ActionHandler RunSqlScriptHandler database scripts (e.g. SQL queries) can be executed directly from the process model. A query result can then be displayed, for example in form.

This is particularly useful if the process participants need database queries to process the tasks. It uses native SQL, so the user can create, update and delete databases and tables.

Furthermore, process data can be directly pushed to the database or database entries can be made available in the form. The ActionHandler is especially useful for transaction-heavy processes that rely on critical data. A complete example of the RunSqlScriptHandler in action can be found below.

Class

com.dooris.bpm.actionhandler.RunSqlScriptHandler

Parameter

Parameter name	Default value	Description	Valid examples

Parameter name	Default value	Description	Valid examples
`databaseEngine`	`oracle`	Determines to which database it should connect. Accepted values are mysql, oracle and mssql.
`host` mandatory		this is for the database connection. The parameter host contains the host's URL, on which the database is running. Yet not the whole URL which leads to the database should be given, ports etc are given with other parameters and system builds the request URL following this scheme: In case of a MySQL database: `“jdbc:mysql:” + host + port + “/” + database“` In case of a Oracle database: `“jdbc:oracle:thin:@” + host + port + ”:“ + database”` In case of a MsSQL database:	`system.yourcompany.com`
`port` mandatory		It contains the port through which the database is accessible.	`17102`
`database` mandatory		The name of the database that shall be reached is stored here.	`EmployeeDb`
`user` mandatory		It contains the username used for authentication.
`pass` mandatory		It contains the matching password used for authentication.
`query` mandatory		The query parameter contains the SQL query that is to be executed on the database. If process variables should be put into the query, they can be used with '${variable}'. The RunSqlScriptHandler executes native SQL. This allows the entire spectrum of SQL commands to be processed via the handler (e.g. create, update or drop databases and tables).	SELECT * FROM Employee WHERE Sallary > 50000 SELECT * FROM Employee WHERE Sallary > '${variable}'
`responseVariabl` `e` mandatory		The responseVariable stores the name of the process variable that will contain the result of the executed SQL query. This makes it possible to use the query result within system, e.g in the form. it will show you a json document.

Example

The following example illustrates a way in which a SQL script can be executed directly from a process. The parameters are recorded via form and passed on to the RunSqlScriptHandler as process variables.

The process model could look like this.

The required parameters are recorded in the Run SQL script activity. At the end of the activity, the script activity Call REST starts a REST call using the HttpRestHandler.

If this is successful, the actual SQL query is executed in the Call DB script activity. If an error occurs, the error is written to a log file.

Form

The form is only used to record the required parameters. In this example, all parameters are provided as process variables for illustration purposes.

In other cases, it may make more sense to just query the user, password and SQL query using form. It should also be noted that the Server and Number parameters are required for the REST call but not for the DB call. The result of the DB call can be displayed in the Response text field.

As indicated above, the RunSqlScriptHandler will set native SQL. Databases and tables can be created, edited and deleted using the query. In addition to reading out data, process data can also be entered into the database.