User Tools
de
enTable of Contents
RunSqlScriptHandler
Description
The RunSqlScriptHandler executes Sql scripts from within a process. It uses native SQL, so you can create, update and delete databases and table. Furthermore process data can be directly pushed to the database or database entries can be made available in the smartform. The handler is especially useful for transaction-heavy processes that rely on critical data. A complete example of the RunSqlScriptHandler in action can be found below.
Action class
com.dooris.bpm.actionhandler.RunSqlScriptHandler
Event Type
any
Action Name
any
Mandatory Fields
empty
Parameter
databaseEngine(default:oracle)
Determines to which database it should connect. Accepted values are mysql, oracle and mssql.
host
The parameter host contains the host's URL, on which the database is running (e.g. host=get.taskinmotion.de;).
Yet not the whole URL which leads to the database should be given, ports etc are given with other parameters and TIM 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: ““jdbc:sqlserver:” + host + port + ”;DatabaseName=“ + database”
port
Contains the port through which the databse is accessable (e.g. port=17102;).
database
database contains the name of the database (e.g. database=EmployeeDb;).
user
Contains the username used for authentication.
pass
Contains the matching password used for authentication
query
The query parameter contains the Sql query that is executed on the database (e.g. query=SELECT * FROM Employee WHERE Sallary > 50000;).
If processariables should be put into the query, they can be used with '${variable}' (e.g. query=SELECT * FROM Employee WHERE Sallary > '${variable}';).
responseVariable
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 TIM, e.g in the smartform.
Example
The following example illustrates how the Sql scripts can be executed within a process. The necessary parameters are collected in the smartform and subsequently passed to the RunSqlScriptHandler. Below is the corresponding process model.
In the Run Sql script activity the necessary parameters are captured via the smartform. Subsequently the script activity Call REST executes a REST call with the HttpRestHandler. Is the REST call successful, the actual Sql query is executed in the Call DB acitivity. In case of an error the error is captured in a log file.
The RunSqlScriptHandler
The RunSqlScriptHandler sits on the Call DB activity and is waiting for the necessary parameters. These are captured through the smartform during the Run Sql script activity.
The Smartform
In this example the smartform helps to capture the RunSqlScriptHandler's parameters. For demonstration purposes every parameter is captured through the smartform. In a production process this might not be necessary. A more suitable approach might be to capture the username, password and the Sql query. The result of the DB call will be displayed in the Response textarea. Also notice that the parameters server and number belong to the REST call not the DB call.
