Connect API Tests to PostgreSQL in UFT One

Micro Focus Expert
Micro Focus Expert
2 0 1,725

Written by Yuanhai Xu (Solever) from the UFT One R&D team.

API testing in Micro Focus UFT One offers a few standard database activities, which enable you to test your application's ability to connect and communicate with a database. This article focuses on using standard database activities with PostgreSQL.

Overview

There are two common connection types available in the API database activities: OLEDB and ODBC. You can connect to various data sources by using any configured OLEDB or ODBC drivers.

An official PostgreSQL ODBC driver is released by the PostgreSQL team which is called psqlODBC and is released under the Library General Public License, or LGPL. With this ODBC driver, you can connect to a PostgreSQL database in API tests.

Install PostgreSQL ODBC Driver

The PostgreSQL team delivers psqlODBC in several formats. The Windows Installer Package (MSI) is the most commonly used to install the PostgreSQL ODBC driver on Windows. You can download the appropriate psqlODBC MSI package on the official download site: https://www.postgresql.org/ftp/odbc/versions/msi/.

The psqlODBC MSI packages are released in various versions corresponding to the database versions. For example, the MSI package psqlodbc_09_06_0500-x86.zip is the latest PostgreSQL ODBC driver that is used to connect to PostgreSQL version 9.6.

Since UFT One is installed as a 32-bit application on Windows, it is important to download and install the x86 MSI package of psqlODBC.

Once downloaded, unzip the file and run the MSI package psqlodbc_x86.msi. Follow the instructions on the Setup Wizard to install the PostgreSQL ODBC driver on Windows.

⚠️ Note: You may need administrator privileges in order to install the psqlODBC driver.

Connect to PostgreSQL in API Tests

Follow the steps below to connect to a PostgreSQL database in a new API test. The database information used in this tutorial is as follows:

uft-api-testing-postgreSQL-1.png

Step 1: Create an API Test with Database Activities

The first step is to create an API test with several database activities.

1. Launch UFT One, click the New Test… toolbar button, select API Test and click Create. A new API test is created.

uft-api-testing-postgreSQL-2.png

2. Then open the toolbox panel on the left, drag the Open Connection activity under the Database group and drop it in the Test Flow canvas on the right. A new Open Connection database activity is created.

3. Add more database activities as needed. In this tutorial, four database activities are populated: Open Connection, Execute Command, Select Data and Close Connection.

uft-api-testing-postgreSQL-3.png

 

Step 2: Open a Connection to a PostgreSQL Database

1. Right-click the Open Connection activity in the Test Flow canvas and select Properties.

2. In the Properties panel on the right, click the […] button on the Connection string row.

uft-api-testing-postgreSQL-4.png

3. In the Connection Builder dialog box that opens, select ODBC connection type and click the button (shown below).

uft-api-testing-postgreSQL-5.png

4. In the Select Data Source Name dialog box that opens, click Manage ODBC Data Sources button.

uft-api-testing-postgreSQL-6.png

5. In the ODBC Data Source Administrator (32-bit) dialog box that opens, navigate to the User DNS tab and click Add…. The Create New Data Source dialog is opened.

6. Find and select the PostgreSQL Unicode driver in the list and click Finish.

⚠️ If the PostgreSQL drivers are not presented in the list, the psqlODBC driver may not be properly installed, or the installed driver may be meant for an unsupported database platform. Only psqlODBC driver is supported in UFT One. Please follow the instructions in the Install PostgreSQL ODBC Driver section to install a proper psqlODBC driver.

uft-api-testing-postgreSQL-7.png

7. In the PostgreSQL Unicode ODBC Driver (psqlODBC) Setup dialog box that opens, enter the information for the database to connect and click the Test to test the database connection.

uft-api-testing-postgreSQL-8.png

8. Click Save or OK a bunch of times until you get to the Select Data Source Name dialog. In this dialog, select the configured data source from the User DSN drop-down list and click Test Connection. You will receive a message when the connection succeeds. Click OK to save the settings.

uft-api-testing-postgreSQL-9.png

9. In the Connection Builder dialog, confirm the settings and optionally click Test Connection to test the database connection. Click OK to save the settings.

Step 3: Execute INSERT INTO Command

1. Right-click the Execute Command activity in the Test Flow canvas and select Properties. In the Properties panel on the right, click the link button on the Connection row.

uft-api-testing-postgreSQL-12.png

2. In the Select link Source for ‘Connection’ dialog box that opens, link the Connection output property to the Open Connection activity. Click OK to save the settings.

uft-api-testing-postgreSQL-13.png

3. Enter your clause on the Command row. In this tutorial, the INSERT INTO clause is:

 

 

INSERT INTO public.api1(name, "desc", "time") VALUES ('name', 'desc', current_timestamp);

 

 

Step 4: Select Data

1. Right-click the Select Data activity in the Test Flow canvas and select Properties.

2. In the Properties panel, click the link button on the Connection row.

uft-api-testing-postgreSQL-15.png

3. In the Select link Source for ‘Connection’ dialog box that opens, link the Connection output property to the Open Connection activity. Click OK to save the settings.

uft-api-testing-postgreSQL-16.png

4. Enter your on the Query string row.

uft-api-testing-postgreSQL-17.png

In this tutorial, the SELECT clause is:

 

 

SELECT name, "desc", "time" FROM public.api1;

 

 

⚠️ You can also use the Query Builder dialog to build the SELECT clause automatically by automatically retrieving the database table schema. To launch the Query Builder dialog, click the […] button on the Query string row. This will populate a general SELECT clause by retrieving the database table schema which may not fit the actual database. You may still have to modify the SELECT clause manually. For example, PostgreSQL requires you to enclose the column name with double quotes, but the generic Query Builder always encloses the column name with brackets.

Step 5: Close PostgreSQL Database Connection

1. Right-click the Close Connection activity in the Test Flow canvas and select Properties.

2. In the Properties panel on the right, click the link button on the Connection row.

uft-api-testing-postgreSQL-18.png

3. In the Select link Source for ‘Connection’ dialog box that opens, link the Connection output property to the Open Connection activity. Click OK to save the settings.

Step 6: Run API Test

Run the API test and check the run results report.

uft-api-testing-postgresql-20.png

Wrap up

While this blog covers a subset of our functionality, more details about UFT One can be found on the Online Help Center.

Download UFT One today from the following locations:

To learn more about UFT One, visit us at:

microfocus.com | Twitter | LinkedIn Group

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.