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. In this section:
Table of Contents | ||
---|---|---|
|
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 easy-to-access location:
Code Block | ||
---|---|---|
| ||
#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.
Code Block |
---|
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:
No Format |
---|
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:
Code Block |
---|
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.