Query Database

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

Database

Select 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.

Timeout

Specify the timeout for submitting SQL queries in seconds. Select a variable or a value option to define the preferred timeout. The value must be greater than zero.

The timeout is enforced through DBMS and if it is reached, the Query Database action stops executing with an error message specific to the database.

Various DBMS types and JDBC drivers may handle timeouts differently. For example, not every system allows a timeout to trip in the middle of certain statements.

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.

Note: 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

• First Row in Design Mode = 301
• Rows to Use in Design Mode = 100

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