Query Database

The Query Database action submits an SQL query to a database and loops through the results. The SQL should be specified using an expression. At each iteration of the result loop, the values of the current row in the result set can be assigned to variables.

Properties

The Query Database action can be configured using the following properties:

Database:
Choose which database this action should submit its query to by using the drop-down list of databases available to Design Studio.
SQL Query:
This field must contain a valid SQL query in the form of an expression. The value of this expression is submitted to the chosen database. The "Edit" popup dialog allows the SQL query to be tested, showing a sample of the output.
Variables Map:
Specify the mapping from result columns to variables. Click the plus sign to add a new mapping and the minus sign to remove an existing one. A mapping consists of a column name and a variable name. The column name must match the name of a column returned by the SQL Query, and the variable name is chosen from a list of existing variables. Note, that the type of the column should match the type of the chosen variable. Otherwise, an error may be generated during execution. That is, trying to store a text column in an integer variable will cause an error.
Retrieve While Looping:
If this is enabled, result rows are retrieved from the database only as they are needed by the loop, iteration by iteration. See the note below on execution.
First Row in Design Mode:
Used only in Design Mode in Design Studio, this is the number of the first iteration or query result row that will be accessible (counting from 1). See the note below on execution.
Rows to Use in Design Mode:
Used only in Design Mode in Design Studio, this specifies the maximum number of result rows to make available for iteration. See the note below on execution.

Notes

Depending on whether Retrieve While Looping is disabled or enabled, retrieval of the result rows is done in two different ways:

In Debug Mode in Design Studio, retrieving while looping implies that the database connection will be held open while execution is stopped at a breakpoint or during single-stepping. If the database has a timeout for inactive connections, you may see a database error when you continue execution of the robot after a long pause.

In Design Mode in Design Studio, result rows will always be retrieved before the loop starts, thus Retrieve While Looping is effectively disabled. This is done to make it possible to switch between different iterations interactively. In order to limit the amount of memory used for this, the First Row in Design Mode and Rows to Use in Design Mode together specify a subset of result rows to load. For example, if

then the loop will iterate over result rows 301 to 400 (provided the SQL query returns so many rows).