execute sql
Execute an SQL command against the database currently in use.
| Name | Description | Type | Modifier |
|---|---|---|---|
| command | SQL query or queries. | String | None |
result, result2, result3,…, resultN | (Optional) Names to be assigned to data sets that will hold the results of the query or queries (See ). (There is no limit to the number of allowed result arguments that may be specified) | String | None |
Results of queries
Example - Case 1: Running an SQL command to get a single result
The following example retrieves the CName field from the CTable table of the CustDB database for a CID of 102 , then checks the returned value against the expected value, Roberto Dylando.
Action lines
variable driver host database name others
create connection string db_conn_str SQL Server pc271 cusDB Trusted Connection=Yes
 
connection string name
connect database #db_conn_str Customers
 
name
use database Customers
 
command result
execute sql select CName from CTable where CID='102' CustomerName_ds
 
name filter
use data set CustomerName_ds
 
value expected
check value #CName Roberto Dylando
 
repeat for data set
 
name
disconnect database Customers
Result
Example - Case 2: Running an SQL command to get multiple results
In this example, the execute sql action is used to execute a stored procedure, rather than a direct SQL query. The stored procedure then executes two SQL queries, returning two result tables, each of which is capture to a data set. The contents of the data sets are then written out to two text files.
The stored procedure looks like this:
The stored procedure looks like this:
Action lines
variable driver host
create connection string db_conn_str SQL Server pc271
 
port database name username
>>> custDB
 
password others
>>> Trusted_Connection=Yes
 
connection string name
connect database #db_conn_str Sales
 
name
use database Sales
 
command result result2
execute sql exec dbo.SalesByYearAndCategory '7/1/1996', '7/31/1996', 'Beverages', '1998' Shipments_ds Sales_ds
 
file name text type
set text file d:\July_Shipments.txt ShippedDate OrderID Subtotal Year new
 
name filter
use data set /Shipments_ds
 
file name text type
set text file d:\July_Shipments.txt #"\n" & ShippedDate & " " & OrderID & " " & Subtotal & " " & Year append
 
repeat for data set
 
file name text type
set text file d:\1998_Beverages_sales.txt TotalPurchased ProductName new
 
name filter
use data set /Sales_ds
 
file name text type
set text file d:\1998_Beverages_sales.txt #"\n" & TotalPurchased & " " & ProductName append
 
repeat for data set
 
name
disconnect database Sales
Result
The resulting text files appear as follows:
July_shipments.txt
1998_Beverage_sales.txt
- The command argument may contain multiple SQL queries, delimited by semicolons.
- Note that different SQL commands can generate different types of results. (For instance, an SQL Select query returns a table; row-based commands, such as Insert, Update, and Delete return scalar values; while commands such as Create and Use return null values). In each case, however, as long as a corresponding resultN argument is specified, the result is stored to a TestArchitect data set.
- Execution of this action may result in the return of multiple tables, due either to the presence of multiple queries in the command string, or to the invoking of stored procedures, or both.
- In the case of multiple queries, each result table is numbered according to the order of execution, and returned to the corresponding result argument, beginning with result, followed by result2, result3, and so on. For example, the first query in the command string, a select query returning two tables, returns its results to the datasets in result and result2, while the next query, an insert query, returns its results to the dataset in result3.
- In addition to the result, result2, result3, etc., arguments listed above, you can opt to use argument names result0 and result1 to specify data sets. However, data sets specified under these headers, like that of result, receive the first returned table of the query string. (Hence, if your action line happens to use all three of the argument headers result, result0 and result1, each specifying a different data set name, execution of the action produces three data sets with identical contents)
- Within the returned data sets, the column header names are derived from the column names of the corresponding result tables. (Refer to Data sets for more information on working with data sets and data set values)
- In general, the creation of a data sets requires that column headers be specified for each column of data. It is possible, however, for an SQL query to return a result table that lacks column names. In such a case, TestArchitect automatically assigns the column headers column 1 , column 2, etc., to the receiving data set.
- In order to gain access to a value from an output parameter of a stored procedure, select it in the command string. The value may then be obtained from the data set specified by the corresponding result argument.
- This action supports the <ignore> modifier. If the string
<ignore>is present as the value of any of the arguments, or any argument contains an expression that evaluates to<ignore>, the action is skipped during execution.
Tel:
Email: