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.
The Query Database action can be configured using the following properties:
- 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.
Depending on whether Retrieve While Looping is disabled
or enabled, retrieval of the result rows is done in two different ways:
- Disabled: The result rows are all retrieved and saved in memory before the
first iteration is executed. Thus, the database connection will be reserved for the
shortest possible length of time, and the results will not be affected by any
steps that are part of the loop (for example,
Store In Database steps).
On the other hand, available memory puts a limit to the number of result rows that
can be handled without error. (This was the only option available until release 8.3).
- Enabled: The results rows are retrieved from the database one at a time
as they are needed for executing each iteration of the loop.
Thus, the step will be able to handle very large numbers of result
rows but will hold the database connection open until all iterations of the loop have
finished execution. As a side effect, the results may be affected if you make changes
to the database tables referenced by the SQL query while the loop executes.
However, many factors work together to determine whether the changes will in fact be
visible in any particular situation.
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).
- First Row in Design Mode = 301
- Rows to Use in Design Mode = 100