The Database Step (DB Step) element is located in the Activity drawer of the process editor palette.
With this element you can execute SQL commands on the database server. You can access all the databases that are defined in the Database.
The Name Tab is included in the mask of all process elements and contains the name and a description of the element.
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.
- 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
Update(Update Query) and
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 Queryand write pure SQL instead. Expanding of process attributes will also work in the Any Query mode.
The use of the Any Query option can lead to SQL injection vulnerabilities if not used carefully.
E.g. if a
Stringvariable 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.searchTextvariable 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
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.
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
IvySystemDatabasedatasource which points to the current System Database. Normally you would prefer your own database to split valuable customer data from the system data.
Do not manipulate system database tables prefixed with
IvySystemDatabasethis 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.
The name of the database table to read from, insert into, update in or delete from.
The fields of the database table to read from, insert values into or update values in.
A condition that filters the rows of the table to read, update or delete.
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.
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.
On Output Tab you can configure the output of the element (i.e. the data that leaves the element).