Steer databases

To steer your databases, you have to create Database Engine 3.0 TestCases. To do so, you can either use Modules from the Standard subset or from the Tosca Data Integrity subset.

Use the Standard subset

The Standard subset contains all Modules that you need to create a database TestCase.

You can find these Modules in Tricentis Tosca under Standard modules->TBox XEngines->Database:

• TBox DB Open Connection

• TBox DB Current Connection

• TBox DB Run SQL Statement

• TBox DB Close Connection

• TBox DB Expert module

Use Tosca Data Integrity

If you use Tosca Data Integrity, Tricentis recommends that you use the DI DB Expert Module from the Tosca Data Integrity Modules And Samples.tsu subset.

This Module has the same base functionality, but allows you to use the connections you have defined in the Tosca DI Connection Manager.

Structure of database TestCases

Database TestCases require the following base structure to be functional from start to finish:

1. Open a connection to a database.

2. Run an operation on your database. You perform these operations through SQL statements.

3. Close the connection.

The Database Engine 3.0 doesn't support mixed TestCases. If you open a connection with the Database Engine 3.0 Module, you must also perform your operations and close the connection with Database Engine 3.0 Modules.

Use Modules to create your TestCase

You can create the steps of your database TestCase in one of the following ways:

• Use the TBox DB Expert module which contains all steps in one Module.

• Use three separate Modules: TBox DB Open Connection, TBox DB Run SQL Statement, and TBox DB Close Connection.

For information on how to create TestSteps from Modules, see chapter "Create TestSteps from Modules".

In this example, you use the TBox DB Expert module to perform the following actions: Establish a connection to the database DB_Test with the user credentials of the user sa. Save the connection under the name TestDatabase. Check the database table Customers. Save the street number in the first data row of the column Street Number to a Buffer named StreetNumberBuffer. Close the connection. Use the TBox DB Expert module in a TestCase

Export the result of an SQL Statement

You can export the result of your SQL Statement into a comma separated CSV file.

You can do so for TestSteps the you created from the following Modules:

• TBox DB Expert

• TBox Run SQL Statement

• DI DB Expert

• DB Expert module (DI Agent)

To configure the export, follow the steps below:

1. In your TestCase, scroll to Result Table.

2. Enter the following command into the Value column: {EXPORTTOCSV[<file path>]}

3. Replace <file path> with the absolute path to your CSV file.

   If the file doesn't exist, the Database Engine 3.0 creates it. If a file with the same name already exists, the Database Engine 3.0 overwrites the existing file.

4. Use the default ActionMode Select.

   The export also works with Input but none of the other ActionModes.

5. To export the result table, run your TestCase.

In this example, Tosca exports the result table of your SQL Statement into the file Customers.csv located at C:\Temp. Export to C:\Temp\Customers.csv

Load SQLite extensions

SQLite extensions are useful if your tests require functionality that SQLite doesn't support out of the box. For example, if you want to use regular expressions (REGEX) in your queries.

You can load SQLite extensions in TestCases that you create with the TBox DB Expert or the DI DB Expert Module.

As an extension, you can use your own external DLL file or the SQLite.Interop.dll library that is installed with Tricentis Tosca.

To do so, you must enable extension loading and specify the extension to load in Settings->TBox->Engines->Database.

To load the SQLite extension when you run your TestCase, you must use a connection that uses a SQLite driver.

• If you use a database connection in your TestCase, you must select SQLite in the Driver TestStep.

• If you use a DI connection in your TestCase, you must select SQLite as Connection type in the Tosca DI Connection Manager.

Example

In this example, you run an SQL statement that contains REGEX. To do so, you perform the following steps:

• In your TestCase, you specify a Connection string that points to the SQLite caching database.

• As Driver, you select SQLite from the drop-down menu.

The TestCase now loads the extension whenever it connects to SQLite.