m (Elizabeth moved page 5.3:Scenario-builder-reference-guide/DBExecute to 5.3:Scenario-builder-reference-guide/ScenarioBlocks/DBExecute)
Revision as of 22:53, 14 March 2019
The DB Execute scenario block provides a way for a scenario to execute SQL statements on a specified database. This block can be used to share data between scenarios.
The DB Execute block may take one of the following conditional exits: Failed or No Data.
An error occurred during SQL statement execution. No error details are provided.
The SELECT statement successfully executed but did not return any records.
Title text is the name of the instance of the block. Enter a name in the text field and click the Update button at the bottom of the Edit pane. The new name of the block appears in the flowchart.
DB connection represents the desired database connection. See the other settings for details.
This is the name of the database connection. This name is shown in the DB Execute block connection selection menu. If no options are shown, click Manage DB connections to add new DB connections.
JDBC driver and connection string
Specify the JDBC driver and connection string that will be used to access this database. Note that templates are provided for some widely used DBMS systems.
Database user name and password
Specify database access credentials.
The database connection selector allows the following operations:
- Add a new connection (Add New button).
- Edit and save the selected connection (Save button).
- Delete the selected connection (Delete button).
- Select the connection to be used in the DB Execute block (Select button).
- Clear the connection selected for the DB Execute block (Select None button).
- Close the window without changing the DB Execute block connection (Close button).
This is the SQL statement to be executed. SQL statements may use scenario variables. For example: SELECT id, name FROM customers WHERE phone=’$(item.from)’
For SELECT statements, the name of the retrieved recordset should be specified. This allows scenarios to have more than one recordset and to choose the recordset to iterate on.
- The columns of the first retrieved record (if any) are stored in the scenario variables <recordset_name>.<column_name> (e.g., RS.id).
- The number of returned records is stored in variable <recordset_name>.__count__ (e.g., RS.__count__). Note the double underscores in front and after count; they are used to reduce the chance of confusing the name of this variable with a column name in a recordset.
- To iterate through the recordset, use the Get Next Record block.
- The number of records in the retrieved recordset is limited to 25.
The database connection is selected from a list of connections. Click the Manage DB connections button. The pop-up window will display all database connections defined in this scenario. For each connection, the following data should be defined: