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: