dbCONNECT Installation Guide
It includes information about the following:
- Reviewing Information About This Guide
- Creating Required Directories/Folders
- Verifying that the JDBC Drivers are installed
- Download Worksoft SaaS dbCONNECT
- Installing Worksoft SaaS dbCONNECT
- Configuring Worksoft SaaS dbCONNECT
- Verifying Operability of Worksoft SaaS dbCONNECT
- Installing Worksoft SaaS dbCONNECT Reference Schema
1. Reviewing Information About This Guide
Tasks Described in This Guide
The procedures in this guide describe how to:
- Configure your system to support Worksoft SaaS dbCONNECT. Specifically, this involves installation of JDK/JRE version 1.7 update 131 and above. and the appropriate JDBC drivers.
- Install Worksoft SaaS dbCONNECT on the local file system
- Configure dbCONNECT to connect with your company’s Worksoft SaaS domain and to all the database(s) behind your AUTs
After you successfully install Worksoft SaaS dbCONNECT:
- Test if your JDBC connection works for each of your database sources
- Test if your dbCONNECT is able to communicate with your company’s Worksoft SaaS domain
This guide does not describe how to complete the following tasks:
- Installing the JDBC Drivers
For more information about installing, configuring & operating Worksoft SaaS dbCONNECT, refer to one of the following:
- dbCONNECT Overview
2. Creating Required Directories/Folders
Create directory/folder with the name similar to the following, and specify the correct owner, group, and permissions for it:
- The “Worksoft SaaS” directory/folder. This directory will be referred to as $INSTALL_DIR in the rest of this guide.
3. Verifying that the JDBC Drivers are installed
Worksoft SaaS dbCONNECT component uses the “Type 4” JDBC Drivers to connect to all the database(s) behind your AUT that you want Worksoft SaaS to integrate with.
Worksoft SaaS dbCONNECT does not ship with third-party JDBC drivers. You must download them separately and save them to the appropriate directory on the server you want to set up Worksoft SaaS dbCONNECT.
As mentioned previously, this guide does NOT help you install and test the installation of the JDBC drivers for all the database(s) behind your AUT that you want Worksoft SaaS to integrate with.
However, this section provides a very high-level information of the specific type of JDBC Drivers that dbCONNECT needs, and the websites from where you can download the database-vendor specific JDBC drivers. Obtain the appropriate "Type 4" JDBC drivers for your database from the vendor's website.
To use the Worksoft SaaS dbCONNECT component it is mandatory to have the JDBC drivers of a specific database available within the "lib" directory/folder of Worksoft SaaS dbCONNECT once you download and configure it. (Refer to next section on how to download dbCONNECT and how to configure it). The "lib" directory/folder is present with in the same directory/folder where the executable 'Worksoft SaaS dbCONNECT.jar' exists.
In case you already have Type 4 JDBC drivers installed and configured on the server/machine where you want to install/configure Worksoft SaaS dbCONNECT OR your company has standardized upon the location of the lib folder on the operating system, you can have Worksoft SaaS use the JDBC drivers in the lib folder that is in a different location, but you must change/update the path to that 'lib' directory/folder in the Worksoft SaaS dbCONNECT startup scripts "Start_Worksoft SaaS dbCONNECT.bat" or "Start_Worksoft SaaS dbCONNECT.sh". Refer to the next 2 sections on instructions to start the dbCONNECT Component.
Find below the information related to the JDBC drivers for the 5 widely used databases (Oracle, Microsoft SQLServer, MySQL and PostgreSQL and DB2):
| DBMS | Driver Class | Library Name |
|---|---|---|
| Oracle | oracle.jdbc.OracleDriver | ojdbc7.jar http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html |
| Microsoft SQL Server | com.microsoft.sqlserver.jdbc.SQLServerDriver |
https://msdn.microsoft.com/en-us/library/mt683464(v=sql.110).aspx |
| PostgreSQL | org.postgresql.Driver | postgresql-9.4-1208.jdbc4.jar or later http://jdbc.postgresql.org |
| MySQL | com.mysql.jdbc.Driver | mysql-connector-java-5.1.39-bin.jar or later http://www.mysql.com/downloads/connector/j/ |
| DB2 | com.ibm.db2.jcc.DB2Driver | db2jcc4.jar |
4. Downloading Worksoft SaaS dbCONNECT
You can download Worksoft SaaS dbCONNECT from the Worksoft SaaS Download Center.
Once you login to your Worksoft SaaS domain, click on “More” menu and select “Download Center” option to go to the "Download Center".
When on the "Download Center" page, click on the "Download" button next to the "Worksoft SaaS dbCONNECT component.
Please note that, if you are interested, in experiencing dbCONNECT using a database that is different from the databases behind your applications (AUT), you can create a sample/seed database using our Worksoft SaaS database Reference Schema.
Please refer to the section below titled "Installing Worksoft SaaS dbCONNECT Reference Schema". To know more about the purpose of dbCONNECT Reference Schema, please click here.
Please note, however, that installing Worksoft SaaS dbCONNECT Reference Schema is not mandatory for you to use and reap the benefits of dbCONNECT.
5. Installing Worksoft SaaS dbCONNECT
Move/Copy the dbCONNECT zip file that is downloaded from Worksoft SaaS Download Center to the $INSTALL_DIR.
Unzip the contents of the Zip file downloaded.
You will see a folder/directory called "Worksoft SaaSDBConnect". This directory/folder is referred to henceforth as $DBCONNECT_ROOT.
The table below lists the contents of $DBCONNECT_ROOT folder/directory.
Description
| File Name | |
|---|---|
| Worksoft SaaSDBConnect.jar | An executable jar for Worksoft SaaS dbCONNECT component. |
| Config.properties | The properties file which contains information where the user has to configure the customer domain name, username and access key. |
| Db.json | The configuration file which contains the properties which represent the DB configurations. |
| PasswordEncryptionComponent.jar | The executable jar which allows the passwords to be encrypted. |
| Start_Worksoft SaaSDBConnect.bat | The .bat file for windows which allows the user to start the component in the windows environment. |
| Start_Worksoft SaaSDBConnect.sh | The .sh file for Linux which allows the user to start the component in Linux environment. |
| Shutdown_Worksoft SaaSDBConnect.bat | The .bat file for windows which allows the user to shut down the component in the windows environment. |
| Shutdown_Worksoft SaaSDBConnect.sh | The .sh file for Linux which allows the user to shut down the component in the linux environment. |
| TestSampleDatabaseConnection.java | The sample java application which can be used to check the database connectivity before starting or registering the component. |
| readme.txt | This file contains the basic information related to the component. |
6. Configuring Worksoft SaaS dbCONNECT
6.1 Configuration Files
To start the Worksoft SaaS dbCONNECT component two configuration files are needed. Below section covers the two configuration files in detail.
6.1.1 Config.properties
This file contains the required properties that are needed for dbCONNECT to connect to Worksoft SaaS REST Services.
Below table represents the important properties that you must configure before starting the component.
| Property Name | Description |
|---|---|
| Customer's Worksoft SaaS Domain Specific Properties | |
| eureqa.dbconnect.customer.domainname | Enter your Worksoft SaaS Domain name. |
| eureqa.dbconnect.customer.username | Enter the username of one of the users within your Worksoft SaaS Domain that you want dbCONNECT to use to connect to Worksoft SaaS. |
| eureqa.dbconnect.customer.accesskey | Enter the access key of one assigned to your Worksoft SaaS Domain that you want dbCONNECT to use to connect to Worksoft SaaS. |
| Worksoft SaaS dbCONNECT Specific Core Properties | |
| eureqa.dbconnect.filepath.separator | The file separator you want to use. By default, it is (/). You need not change the value to be different from the default. |
| eureqa.dbconnect.restservice.url | URL for Worksoft SaaS QaCONNECT REST Services. You need not change the value to be different from the default. |
| Worksoft SaaS dbCONNECT Advanced Properties | |
| thread.workerPool | The number of worker threads that are needed to execute the SQL Queries within your database(s). You need not change the value to be different from the default. Default Value given is 5. |
| thread.controller.sleepTime | The temporary pause time for the component. The component will be in sleep mode for the specified number of seconds before it wakes up again. |
| thread.shutdownPort | The port number that will be used to 'gracefully' shutdown dbCONNECT. A daemon thread will keep listening on the port number specified for a shutdown signal. |
| thread.softShutdown | Option to allow for a soft shutdown. If true, the daemon will keep listening for the shutdown signal on the specified port number. |
6.2.2 Db.json
This configuration file contains configuration information related to the databases. Information which is present in the database server IP address, database server username, database server password etc.
In this file, we can specify ‘n’ number of database configuration information as needed. As json file contains key/value pairs, we can have multiple key-value pairs for each database separated by a unique alias name.
Sample JSON content:
{
"postgres_aliasname": {
"dbms": "postgres",
"databaseName": "<Your_Database_Name>",
"databaseServerIP": "<Your_Database_Server_IP>",
"databaseServerPort": "<Your_Database_Server_Port>",
"databaseServiceName": "<Your_Database_Service_Name>",
"username": "<Your_Database_Server_Username>",
"password": "<Your_Database_Server_Password>",
"isPasswordEncrypted": {true|false},
"isWindowsAuthenticationMode": {true|false},
"jdbcDriverClass": "<JDBC_Driver_Class_Name>",
"jdbcConnectionUrl": "<JDBC_Connection_URL",
"initialConnectionPoolSize": 1,
"enableDBProxyMode": {true|false},
"proxyDBDataFolderPath": ""
}
}
In the above json content “postgres_aliasname” represents the alias name which should be mentioned at the time of scheduling a run within Worksoft SaaS application. The keys used within the JSON file are CASE-SENSITIVE.
Below table contains the description about the parameters which are needed:
| Property/Key Name | Description |
|---|---|
| dbms | The dbms name. The current version of the component supports oracle, mysql, postgres and sqlserver. Find below the valid values which this field accepts. |
| databaseName | The actual database name to connect. Not required when connecting to oracle database. |
| daatabaseServerIP | The IP address or hostname of your database server |
| databaseServerPort | The port for your database server |
| username | The username you want dbCONNECT to use to connect your database server |
| password | The password for the user you want dbCONNECT to use to connect your database server |
| isPasswordEncrypted | If the Password you entered above is encrypted using the dbCONNECT Password encryption tool included in the dbCONNECT folder, then set the value of this property to 'true'. Otherwise, set its value to 'false'. |
| isWindowsAuthenticationMode | This property is specific to Microsoft SQL Server. For other databases, leave this property blank. There are 2 authentication modes in SQL Server. 1) SQL Server Authentication and 2) Windows Authentication. If you need Windows Authentication, set the value of this property to 'true'. Note: For Windows authentication, we need to copy auth.dll file( distributed along with the JDBC driver download from Microsoft) to lib directory created in the dbCONNECT folder and add a system specific property(-Djava.library.path=lib) to Start_Worksoft SaaSDBConnect.bat Changes to: Start_Worksoft SaaSDBConnect.bat java -Djava.library.path=lib com.eureqa.dbconnect.framework.DBConnectController -name demoTunnel -db db.json If you need SQL Server Authentication set the value of this property to 'false'. |
| jdbcDriverClass | The class name of the Type 4 JDBC driver you have downloaded for your database server. For the JDBC Driver, class names refer to the next table below. |
| jdbcConnectionURL | This optional property, when used takes precedence over other properties. This property holds in one string the values for all other connection related properties and is the address that points to your database. For the JDBC Driver, class names refer to the next table below. |
| initialConnectionPoolSize | The initial size of the database connection pool. |
| enableDBProxyMode | Set to 'true' if you want the proxy mode enabled for this particular database. If set to true, then instead of connecting to the real database, dbCONNECT will use CSV files. |
| proxyDBDefaultFolderPath | This parameter has significance only if the 'enableDBProxyMode' is set to 'true'. This parameter stores the folder path where the CSV files (representing your database tables) reside. |
Below are the JDBC Driver Class Name and the Database URL format that are to be used for the popular databases. All the bolded parts in URL format are not static and you need to change the bolded parts as per your database setup.
| Database | JDBC Driver Class | JDBC Driver URL |
|---|---|---|
| Oracle | org.jdbc.OracleDriver | jdbc:oracle:thin:@hostname:port Number:databaseName |
| Microsoft SQL Server | com.microsoft.sqlserver.jdbc.SQLServerDriver | jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
|
| PostgreSQL | org.postgresql.Driver | This takes one of the following forms: jdbc:postgresql:database jdbc:postgresql://host/database jdbc:postgresql://host:port/database |
| MySQL | com.mysql.jdbc.Driver | jdbc:mysql://host:port/ databaseName |
7. Verifying Operability of Worksoft SaaS dbCONNECT
7.1 Command Line Options for Worksoft SaaS dbCONNECT
Worksoft SaaS dbCONNECT component has many command line options which are helpful for some additional configurations at that component level. Below table contains the information about all the options.
| Option Name | Description | Mandatory |
|---|---|---|
| Mandatory Options | ||
| -name | -name option is mandatory which needs to be passed to during component start-up. To this option we need to pass a unique name which is referred to as tunnelName. This tunnel name will be unique with in the domain. This tunnel name will be successfully registered with Worksoft SaaS SAAS application and can be used within runs. | true |
| -db | -db option will hold the json file (db.json) which contains the database specific configuration. If passed only the filename the component will look for the file in the current working directory. | true |
| Optional Options | ||
| -version | -version option which prints the component version and exit. | false |
| -help | -help option let print all the options present with the component. | false |
| Logging Options | ||
| The below options helps to control the component logging.The log files are generated in logs folder. This folder will be created automatically in the same folder where the component is running.The log files will be rotated on daily basis. | ||
| -quiet | Turns off all output except errors and warnings. Equivalent to -verbosenesslevel=0 | false |
| -normal | Quiet output plus a modest amount of informational and progress output. Equivalent to -verbosenenesslevel=1 | false |
| -progress | Normal output plus output about progress through the phases of activity and the queries being processed. Equivalent to - verbosenenesslevel =2. This is the default option used by the component. | false |
| -verbose | Progress output plus a verbose amount of output about the phases of activity and the queries being processed. Equivalent to -verbosenenesslevel=3 | false |
| -debug | Output useful only for detailed debugging (includes the verbose output). Equivalent to -verbosenenesslevel=4 | false |
| -verbosenesslevel | Valid values will be between 0 to 4.Additional option instead of log options | false |
7.2 Starting Worksoft SaaS dbCONNECT Component/BatchJob
Java (JDK or JRE) must be installed in order to run the component. The PATH environment variable has to be set before starting the component.
To check the java is installed and the PATH environment variable is set to use the below command in command prompt in case of windows or terminal in case of linux. It should print the java version which is installed in the machine.
D:\..\Worksoft SaaS dbCONNECT>java –version
Worksoft SaaS dbCONNECT component can be started using the Start_Worksoft SaaS dbCONNECT.bat for windows environment and Start_Worksoft SaaS dbCONNECT.sh for linux environment. We need to edit the startup scripts to specify the tunnel name and pass the Database Configuration (db.json).
Note: Before starting the component we need to set the classpath (environment variable) to all the jars present in the lib folder. The .bat files or .sh files can be used to run the component without setting the classpath as the startup scripts contain the required commands to set the classpath.
In case the location of the libraries folder i.e (lib) is not present in the same directory (Worksoft SaaS dbCONNECT), edit the location of the libraries folder in the startup script.
Setting classpath from Command Prompt in Windows: (already present in the Startup script)
D:\..\Worksoft SaaS dbCONNECT> SET CLASSPATH = %CLASSPATH%; Worksoft SaaS dbCONNECT.jar;<location>/lib/*; (* indicates all the jar files present in the lib directory)
Setting classpath from terminal in Linux: (already present in the Startup script)
[dev@..Worksoft SaaS dbCONNECT] export CLASSPATH=$CLASSPATH:Worksoft SaaS dbCONNECT.jar: <location>lib/*
After setting the classpath below command triggers the component execution common for both windows and linux.
D:\..\Worksoft SaaS dbCONNECT> java com.eureqa.dbconnect.framework.DBConnectController -name < Tunnel Name> -db <db.json filename or filename with filepath>
Note: Path should be mentioned with either single forward slash (/) or two backward slashes (\\)
7.3 Shutdown Worksoft SaaS dbCONNECT Component/BatchJob
7.4 Shutdown of Worksoft SaaS dbCONNECT component De-registers the tunnel with the Worksoft SaaS SAAS application.
A demon thread will create a socket on a port mentioned in config.properties file, this thread will wait for the shutdown signal to send a deregister request to the Worksoft SaaS SAAS application.
7.5 Below is the command line option which allows us to trigger the shutdown process.
D:\..\Worksoft SaaS dbCONNECT> java com.eureqa.mtf.framework.ShutdownController -p 5656 -s s
The above command is configured in ShutdownScripts.
8. Installing Worksoft SaaS dbCONNECT Reference Schema(s)
To know more about the purpose and value of dbCONNECT Reference Schema, please click here.
If you are interested, in using dbCONNECT Reference Schema and want to install it, please follow instructions from here.
As mentioned previously in this article, installing Worksoft SaaS dbCONNECT Reference Schema is not mandatory for you to use and reap the benefits of dbCONNECT.