In this section:

Introduction

The Test Data Assistant (TDA) SQL Connector is a command line utility for importing data from a local database into a SQL data set inside TDA. You can also use the connector to export new or altered data sets from TDA directly into your database.

Supported Databases

The TDA SQL Connector can import data from the following types of databases:

• Microsoft SQL Server
• MySQL 
• HyperSQL
• Oracle

Prerequisites

The connector reads settings, such as the JDBC driver settings for your database and URL of the Continuous Testing Platform, stored in a .properties file. Create a plain text file with the following configurations and save it in an easy-to-access location:

#CTP connection properties
ctp_url=http://localhost:8080/em
ctp_password=626079fd67
ctp_username=admin
#CTP server ID for repository to be created in
serverid=2
#JDBC properties
jdbc_url=jdbc:hsqldb:hsql://emdemo.parasoft.com:9001/parabank
db_username=sa
db_password=
#Data set in TDA to download from
dataset_name=<name of data set downloaded from TDA to database>
#Oracle tablespace
tablespace=<e.g., EM_DATA>

Contact your database administrator if you do not know your JDBC information.

You will also need the JDBC driver jar file for your database. Many JDBC drivers are readily available for download from the database vendor's website.

You can get the server ID from the URL in Test Data Assistant.

Encoding Your Password

You can run the connector jar with the -encodepass flag to create an encoded password that you can use in the .properties configuration file. Encoding your password is optional, but doing so adds a layer of security to your import.

java -jar "parasoft-tda-sql-connector.jar" -encodepass <password>

Usage

Run the SQL Connector on the command line to use the tool.

Importing

Execute the connector jar file with the upload command to import data from the SQL database into a data repository in TDA: 

java -cp "parasoft-tda-sql-connector.jar;<jdbc-driver.jar>" upload --settings <path-to-properties-file> --repositoryname <name-of-the-data-set-repository>

The --repositoryname flag is optional. If you do not specify a repository name, the repository will be named "repository" in Test Data Assistant. Binary long objects (BLOBs) are uploaded to TDA as base64-encoded strings.

Viewing the Imported Data Set

A new repository containing a SQL data set will appear in Test Data Assistant. It will take the name of the imported database. It will include a single SELECT statement for each table.

The TDA SQL Connector automatically sets the data constraints for the imported data. Data constraints are pieces of metadata, such as data types and references, that enable you to generate new data using the data modeling feature in TDA. In the following example, a data type constraint for the "BALANCE" column was automatically set to "Number." The constraint was also configured to be optional (set to true), meaning that the BALANCE field can be empty. See Editing Data Constraints for additional information about data constraints.

In most workflows, SQL data sets are captured when Virtualize is used to record interactions with the database. When TDA stores the data, default names are assigned to the SQL template used to query the data and the result set returned by the template. You can manually change the display name of the SQL template in the data modeling interface. See SQL Data Sets for additional information. When data is imported with the TDA SQL Connector, the SQL template and result set display names are automatically set to the database table and column names, respectively. If a record references another object in the data set, the display name set on import will be rendered in Reference field in the data settings. In the following example, the "ID" column in the "CUSTOMER" table (result set) of the "PUBLIC" data set is being referenced.

To locate the referenced column in the result set, follow the dotted reference line.

See Working with SQL Data Sets for information on interacting with the imported data set.

Exporting

Execute the connector jar file with the download command to export data from TDA into your SQL database:  

java -cp "parasoft-tda-sql-connector.jar;<jdbc-driver.jar>" download --settings <path-to-properties-file> --repositoryname <name-of-the-data-set-repository>

Specify the name of the data set that you want to download in your .properties file (see Prerequisites). You can also add an option to specify the tablespace in the .properties file for Oracle databases. The --repositoryname flag is optional when importing data, but it is required for exporting data.