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 |
---|---|---|---|
|
| Determines to which database it should connect. Accepted values are mysql, oracle and mssql. |
|
|
| 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:
|
|
|
| It contains the port through which the database is accessible. |
|
|
| The name of the database that shall be reached is stored here. |
|
|
| It contains the username used for authentication. |
|
|
| It contains the matching password used for authentication. |
|
|
| The query parameter contains the SQL query that is to be executed on the database. 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). |
|
|
| 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.
© TIM Solutions GmbH | AGB | Datenschutz | Impressum