Canopy™ SQL Provider

The SQL Canopy Provider connects to Oracle, MySQL and SQL databases. It can create, update, and delete rows, and run assertions against individual rows or by running a query.

The SQL provider offers the following fixture and assertion types:

• SQL Add
• SQL Backup / Restore
• SQL Delete
• SQL Query
• SQL Row
• SQL Truncate
• SQL Update

Additional Functionality

The SQL provider includes the following extended capability:

Schema Scan – attribute names are collected by the schema scan. These enable dropdown lists in the user interface to simplify fixture and assertion creation.

Data Image – Presents a tabular picture of data that can be used as a source for iterative cases. May also be used with Data Image Query to produce a data set from an arbitrary SQL query

SqlScalar – Execute a scalar query to read a single value from the targeted database

Connection Settings

• Connection String – Connection string format is specific to type of database. Default is MS SQL Server. For Oracle connections the entire connection string is required, as it uses the Managed .NET Driver rather than the full Oracle Client
• Database Type – MS SQL Server, MySQL, or Oracle .NET Driver
• Table Name – Although assertions can be made with arbitrary queries, adds and updates occur against a specific table or view
• Key field(s) – Unique key for table. Comma separate for multiple columns.
• Data Query – Optional SQL query for generating a data image to use with the Load Provider

SQL Add

SQL Add inserts a single row, or updates an existing row if it already exists.

• Key Value – The Key Value field may be used to provide the unique ID for testing if the account already exists, and to populate that column. If the provider has a compound key the key value must be specified in the Fields collection instead
• Fields – A list of attribute values to populate the columns of the row

SQL Update

SQL Update modifies the values of an existing row.

• Key Value – The Key Value field may be used to provide the unique ID to find. If the provider has a compound key the key value must be specified in the Fields collection instead
• Fields – A list of attribute values to populate the columns of the row

SQL Backup / Restore

Performs a full database backup or database restore. This fixture is only available with the MS SQL database type. 

• Operation – Backup or Restore
• File Directory – Location to place the backup in. Must be a folder or share accessible to the SQL service
• SQL Agent Account – Optional service account for SQL Server, used to grant and revoke file permissions on the directory if needed
• Service Dependency – Optional service name to stop and restart before and after the backup
• Dependency Server Name – Server that the service dependency executes on.

SQL Delete

Remove a row in the SQL table

• Key Value – The unique identifier of the row to remove
• Fields – Optional, especially with a compound key it may be necessary to provide multiple keys and values to perform a delete

SQL Truncate

Purges the entire contents of the SQL table. Useful for cleanup and resetting.

SQL Row

Assert against the contents of a single row in the table. 

• Key Value – Specify the value for a single key field (compound keys must be enumerated in Fields
• Not Exists – invert the assertion
• Fields – List of value assertions to perform. May be direct value comparison, regular expression or rule based
• Result Memos – Generate memoized values using the contents of the row

SQL Query

Execute a SQL scalar query and treat the result as true or false.

• SQL Query – the actual query to execute
• Fields – List of query parameters (without preceding '@') to pass to the query