DB Step

The Database Step (DB Step) element is located in the Activity drawer of the process editor palette.

Element Details

With this element you can execute SQL commands on the database server. You can access all the databases that are defined in the Database.

Inscription

Name Tab

The Name Tab is included in the mask of all process elements and contains the name and a description of the element.

DB Tab

Here you define which SQL command you want to execute on which database. Depending on the kind of SQL command you are supported during the construction of your command.

DB Tab

Kind of Query

Choose the kind of query you like to perform. Axon.ivy offers dedicated UI support for the most common query types such as Select (Read Query), Insert (Write Query), Update (Update Query) and Delete (Delete Query).

If you require some non-standard SQL or if you want to issue a complex SQL statement that is not covered by the Query Definition mask then you may also select Any Query and write pure SQL instead. Expanding of process attributes will also work in the Any Query mode.

Warning

The use of the Any Query option can lead to SQL injection vulnerabilities if not used carefully.

E.g. if a String variable is passed into a query then an attacker could provide a valid partial SQL statement which is then executed in the context of the query.

Assume the following SQL statement is configured as an SQL query: SELECT * FROM Subscriber WHERE Name LIKE 'in.searchText%'. Now if an attacker manages to pass a valid SQL statement into the in.searchText variable then a so called SQL injection takes place, which can result in a complete data breach on the configured database. Process designers are responsible to only pass sanitized data into an SQL query. In some cases it might be better to access the Database with JPA/Hibernate or prepared statements using JDBC. For more information see: SQL Injection Prevention Cheat Sheet

Database

Choose the database on which the command is executed. The database must be configured in the Database. Depending on the active environment the right connection properties of the database will be used.

Warning

Please note that the DB2 database is currently not fully supported by the DB Step. The only query kind that is suitable for DB2 connections is Any Query. For all other query kinds the Query Definition mask is currently not working correctly (e.g. query fields can not be edited / defined).

There is also an IvySystemDatabase datasource which points to the current System Database. Normally you would prefer your own database to split valuable customer data from the system data.

Warning

Do not manipulate system database tables prefixed with IWA_ within the IvySystemDatabase this could lead to unexpected runtime behavior.

Query Definition

Depending on the type of query you can compose your command with almost no knowledge about databases and SQL.

Table

The name of the database table to read from, insert into, update in or delete from.

Fields

The fields of the database table to read from, insert values into or update values in.

Condition

A condition that filters the rows of the table to read, update or delete.

Sort

Defines the fields after which the rows that are read from the database are sorted.

Lot size

Defines how many rows are read from the database. Enter 0 or leave it empty for no limitation.

Start index

Defines the number of the row that is the first row in the read recordset out of the overall rows which match the condition.

Quote IvyScript variables

IvyScript variables in the SQL query are quoted depending on the data type of the value of the variable. For example string values are quoted with single quotes (e.g. hello -> ‘hello’). Sometime you do not want that the values are quoted because the variable do not contain a single value but a part of an SQL query (e.g. “id=123 AND name=ivy”). Therefore you can disable the quoting with this check box.

Error

Is thrown whenever errors during the execution of the database command occur. The error can be handled by a catching Error Start or by an Error Boundary Event.

Tip

SQL experts can review the generated SQL command by clicking on the Show generated SQL or by choosing Any other query in the query combo box.

Data Cache Tab

On this tab you can configure the settings for data cache access or invalidation. See Data Cache Tab for a more detailed description.

Output Tab

On Output Tab you can configure the output of the element (i.e. the data that leaves the element).