Database

This section includes built-in actions that connect to and run queries on database management systems (DBMSs) or Microsoft Excel data sources.

Overview

The set of actions in this section allows you to connect to a database, run SQL commands on it, retrieve any records or other values produced by those commands, and disconnect from the database.

In order to query a database, you must be familiar with SQL commands. A variety of good references and tutorials on SQL are available, including this SQL Tutorial from W3Schools.

The following built-in actions are provided. They are listed here in the typical order of their workflow.

1. create connection string: Generate a connection string for a specified database on a given host.
2. connect database: Open a database connection using a connection string.
3. use database: Specify a database against which the ensuing SQL action lines are to be applied.
4. execute sql: Execute an SQL command against the currently in-use database; optionally, store returned data into one or more data sets.
5. disconnect database: Close the database connection.

Prerequisites

• Supported databases:

  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2012
  • MySQL 5.6.20
  • Microsoft Excel 2007, 2010, and 2013
  • Oracle Database:
    • Oracle Database Enterprise Edition
      • 12c Release 2
      • 11g Release 2
    • Oracle Database Express Edition
      • 11g Release 2
  Note:
  • SQL Server and MySQL are standard relational database management systems (DBMSs) that enjoy widespread use. While MS Excel is not a DBMS per se, its data is also structured in a relational (table) form, allowing for it to also be queried by means of SQL commands.
  • In principle, while only the DBMSs listed are explicitly supported, any SQL-conversant DBMS with ODBC-compliant drivers for the platform of the given test system should also behave well with TestArchitect. For assistance, contact TestArchitect Support at [email protected].

• Connecting to and querying a database from a test requires that the correct ODBC driver be used. The correct driver must be specific to both the database application and the architecture (64-bit) of the TestArchitect Controller application that uses it. The following utilities may be used to determine whether the correct driver is already installed:

  • Windows: Open the ODBC Data Source Administrator dialog box. A list of installed ODBC drivers is available under the Drivers tab. (Learn more.)
    
    

  • macOS and Linux: If you do not already have a utility that lists installed ODBC drivers, one good option is the open source project unixODBC.

• Should you need to obtain the ODBC driver installer for your particular data source, following the appropriate link below:

  • Microsoft SQL Server:

    • On Windows: Read here.
    • On Linux: Read here.
    • On macOS: Microsoft does not provide a SQL Server ODBC driver for the macOS machine. However, a good open source alternative, FreeTDS, is available. Note that, additional configurations are also required (learn more).

  • MySQL Server: Read here.

    Note:
    On macOS, additional configurations are required (learn more).

  • Excel: The ODBC driver for Microsoft Excel is automatically installed with Microsoft Office. If Microsoft Office is not currently installed on your test machine, you can download and install the correct driver from the appropriate link below:

    • ODBC driver for Excel 2010 (64-bit): http://www.microsoft.com/en-us/download/details.aspx?id=13255

create connection string

connect database

use database

execute sql

disconnect database