SQL Select – Source Reference
The SQL Select <SQLSelect> element is used to issue a select statement against a database in the environment where the Total Test Scenario is executed. The name of the database and the establishment of a connection are handled by the selected environment in the execution context. The SQL Select statement only specifies the SQL.
When inserting an SQL Select, you must specify the SQL statement and an ID. The ID is necessary so returned rows can be accessed from a Data Reference element later in the test scenario.
An output column can also be used to format a returned timestamp to a specific format. Inputs are only used if you have WHERE clauses and want to create dynamic SQL that depends on values previously in the test scenario.
Syntax
The syntax of the SQLSelect element is
<Statement><!-- The SQL statement--></Statement>
<Input name="FIRSTNAME" type="STRING">Hello</Input>
<Output name="<column name>" format=î<timeformat>î/>
…
</SQLSelect>
- The ID attribute is required.
- The SQL statement itself supports variable substitution and values from earlier in the test Scenario can be referenced with ${<id.path>}
- Input elements are required when there is a WHERE clause. There must be as many Input elements as there are ? signs in the sql statement.
- Output elements are optional and may have a format attribute specifying how to convert a timestamp.
Example
Example on selecting two columns from an employee table
<Statement>SELECT RIDENT, FIRSTNAME FROM XAT.XAEMPLOYEE
</Statement>
<Output name="RIDENT" />
<Output name="FIRSTNAME" />
</SQLSelect>
An example with a where clause and the possibility to create dynamic SQL. The Input element could contain a Data Reference or Expression element pointing to other data instead of containing the hardcoded Hello value.
<Statement>SELECT * FROM XAT.XAEMPLOYEE WHERE NAME LIKE ?
</Statement>
<Input name="FIRSTNAME" type="STRING">Hello</Input>
</SQLSelect>