DB Explorer Tool

Functionize DB Explorer Tool Overview

The Functionize Database Explorer tool allows a user to query multiple types of databases in order to verify database contents as part of a test flow.

The DB Explorer tool supports query requests to multiple databases, however only MYSQL has native support in the tool. Other databases can also be supported via configuration using database drivers Functionize provides.

Supported Databases:

  • MySQL
  • MySQL (JDBC)
  • MS SQL Server
  • DB2
  • PostgreSQL
  • Oracle
  • MariaDB
  • InfluxDB

If the system (machine/vm) running the Functionize DB Jar file is accessible via a public endpoint, then the configuration to test the database will only require the IP address and Port of the Functionize DB service system.

DBET1.png

However, in most cases, access to the internal database is restricted. In such scenarios, a Functionize Whitelisted or Tunnel Proxy configuration can be implemented.

DBET2.png

DBET3.png

 

Access the DB Explorer

  1. Go to any project
  2. Click the Create New Test button in the upper right corner of the page
  3. Select Architect
  4. The Create New Test window will open and enter this URL: functionizeapp.com/tools/dbexplorer


  5. Click Start to open the DB Explorer
  6. The Architect will launch along with the Functionize DB Explorer tool site


-OR-

 

  1. Go to any project
  2. Select the Create New Test button in the upper right corner of the page
  3. Select Architect
  4. From the Web App tab, enter the URL for the site under test, confirm I understand that no production credentials should be used in my test cases, enter any further information, then click Start


  5. Once Architect is launched and recording has begun, click the plus  icon
  6. Click DB Explorer under the Tools section


  7. Select Open in a new tab (if desired), then click Add


  8. The DB Explorer tool site will open along with the Architect

DB Explorer Request Form

  1. Server IP

  2. Server Port
  3. Server Type (drop-down selection)

    • MySQL
    • Postgres
    • Oracle
    • SQL Server
    • Mongo
    • DB2
    • MariaDB
    • MySQL (JDBC)
    • InfluxDB
    • AWS Dynamo DB
    • KDB+
      Note: Only MYSQL is supported natively within the tool. If you select another Server Type, the fields below will appear. See Advanced Database Integration below:
      • Functionize DB Service

      • Proxy IP

      • Proxy Port

  4. DB Name

  5. Credentials (Username and Password)

  6. Query

  7. Fill in all fields, then click Launch Request

Query Result

The Response Data will load and click the + icon to view the response details

mceclip1.png

The Response Data will display what is returned from the database request. In this example, we can see the following data has been returned and we could proceed to perform validations on it within the test case. You may verify or store the resulting data as you would with any other site.

  • order_id

  • firstname

  • lastname

  • date_added

  • total

mceclip2.png

Advanced Database Integration

If integrating with a JDBC interfacing database, such as DB2, you will need to host a basic Whitelisted Proxy or Functionize Tunnel Service to facilitate the communication between the Functionize Runtime environment and your database. This service will need to be set up in an environment where it can communicate directly with the database.

When using this configuration, the service that you install exposes a single endpoint to act as a passthrough to the database.

DB_Integration.png

Whitelist Proxy

For this configuration, Functionize will provide you with a Proxy that can be whitelisted in your DMZ to allow access to the Functionize DB service host that will in turn route the database queries to the appropriate database.

DBET8.png

Functionize Tunnel

For this configuration, we will provide you with a Proxy to the Functionize WSS Tunnel endpoint to allow access to the Functionize DB service host that will in turn route the database queries to the appropriate database. The Java JAR file to host the DB Service can be run on the same system where the Tunnel is running.

DBET9.png

Prerequisites

A minimum of Java 1.8 must be installed and Java needs to be added in system PATH

Installation

  1. Download the .jar file needed for all databases here
  2. Test connectivity from the server where you would like to host the service
    • The service can be ran temporarily from the command line
      java -jar functionize_databaseConnectivity.jar
    • You can use the curl command below to test connectivity
      Note: The curl command for Oracle must include the Site Identifier or SID parameter.
  3. Once connectivity is verified, install as a service. Instructions to install as a Linux service are available upon request.
Drivers Available:

For MSSQL: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
For Oracle: "oracle.jdbc.driver.OracleDriver"
For DB2 (JDBC4 specification): "com.ibm.db2.jcc.DB2Driver"
For PostgreSQL: "org.postgresql.Driver"
For mariaDB: "org.mariadb.jdbc.Driver:
For MySQL (JDBC): "com.mysql.cj.jdbc.Driver"
For InfluxDB: "com.dbschema.influxdb.InfluxJdbcDriver"

Connectivity Test Command:

curl -H "Content-Type: application/json" -X POST -d '{"sql":"DB_QUERY", "name":"DB_USER_NAME", "password":"DB_USER_PASSWORD", "dbname":"DB_NAME","server":"DB_SERVER_ADDRESS", "dbport": "DB_SERVER_PORT" , "driver":"DB_DRIVER" , "sid": "SITEID"}' http://ADDRESS_OF_SERVICE:2511/query