LoadRunner: Performance testing Oracle via JDBC Connection

This Post will give you brief introduction to design a method for testing Oracle via JDBC connection to simulate application interaction with the database.

Understanding JDBC

The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases—SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access.

The JDBC Thin Driver used in this method is a JDBC Type 4 driver that uses Java to connect directly to Oracle via the TNS protocol. This driver is written completely in Java and is hence platform independent and provides the best performance of all JDBC driver types.

Connection String Examples

The following URL connects user "ray" with password "password" to a database with service orcl (Important: see more on services) through port 1521 of host myhost, using the Thin driver.

jdbc:oracle:thin:ray/password@//performance.net:1521/orcl

This URL connects to the same database using the the OCI driver and the SID inst1 without specifying the username or password.

jdbc:oracle:oci:@performance.net:1521:inst1

LoadRunner Oracle via JDBC (Java VUser)

As JDBC is widely used and documented in the Java world, we can use the Java Vuser to use the Java JDBC examples directly in our Vuser script.

Requirements

In order to script an Oracle JDBC connection you require

1. JDBC Connection String
2. JDK
3. Oracle JDBC Drivers

JDK Path

As a Java VUser, the JDK location must be set. This is required to be the same for all load generators as this is a run-time setting for the script rather than the generator itself. In the example (See Figure 1) the JDK path is set to “C:\JDK\”. Note that this JDK directory must contain a “bin” directory that contains the javac.exe.

Script Type

The script type used for Java is the “Java Record Replay” or whatever you have a license for.

Database Drivers

To establish a connection to the database, we are obviously using JDBC. In this example we will be using the “Thin” drivers as they are considered to be smaller and faster than the OCI drivers, and doesn’t require a pre-installed version of the JDBC drivers. This is particularly important for managing the Oracle JDBC drivers across multiple machines. With the Thin driver, we can include the Oracle thing JDBC driver JAR files directly in our script to make them portable and not have to worry about installing them on each and every load generator.

Scripting

Java vs. C

The JDBC connection script uses a Java VUser as opposed to the regular C script. As much as it would be simpler to stick with C for familiarity, as JDBC is a Java API, it made sense. There are a few small changes with Java script when compared to a C script

1. vuser_init and vuser_end are contained in the action script
2. Every function is different than in C (lr_start_transaction(); becomes lr.start_transaction();)
3. You now have to worry about JDK and Class path run-time settings

It is important to consider these elements, especially point 3. The JDK run-time setting must specify the JDK location path. This path will have to be the same on ALL load generation machines.

Connecting to the Database

There are two steps in a Java to connect to an Oracle database via a JDBC connection. The first step is to dynamically load the Oracle JDBC driver class.

// Load Oracle JDBC Driver
Class.forName("oracle.jdbc.driver.OracleDriver");

The second step is to make the connection to the database using a JDBC connection string, username and password. The following example is connecting to the performance.com host on port 1521 and the database SID of LOAD.

// Specify the JDBC Connection String
String url = "jdbc:oracle:thin:@performance.com:1521:LOAD";

// Connect to URL using USERNAME and PASSWORD
connection = DriverManager.getConnection(url,"Perf",lr.decrypt("perfload"));

Of course being Java, we have to do all the work and specify what we would like to happen if any of the steps fail. The following is an example of an init function that is designed to connect to the database and gracefully abort the Vuser if the database connection cannot be made.

// Create global connection variable
private Connection connection;

// VUser Init
public int init() throws ClassNotFoundException, SQLException {

  try {
    // Load Oracle JDBC Driver
    Class.forName("oracle.jdbc.driver.OracleDriver");
  } catch (Exception ex) {
    // If driver load is unsuccessful
    lr.log_message("Database Driver not found");
    lr.abort();
  }
  try {
    // Specify the JDBC Connection String (jdbc:oracle:thin:@HOST:PORT:SID)
    String url = "jdbc:oracle:thin:@performance.com:1521:LOAD";
    // Connect to URL using USERNAME and PASSWORD
    connection = DriverManager.getConnection(url,"perf",lr.decrypt("perfload"));
    lr.log_message("JDBC Connection Successful");
  } catch (SQLException e) {
    // If Connection Failed
    lr.log_message("Database Connection Failed, Please check your connection string");
    lr.abort();
  }
  return 0;
} //end of init

Database Query Function

In an effort to make scripts as easy to read and understand as possible, I have written a database query function that will return a pass or fail for the currently open transaction. This function performs a Database Query with the parsed in query. The Java code to execute a query against out JDBC connection is as follows.

String SQL_QUERY = “SELECT * FROM TABLE”;
Statement stmt = null;
ResultSet rset = null;

connection.setAutoCommit(false);
stmt = connection.createStatement();
rset = stmt.executeQuery(SQL_QUERY);
rset.close();

The results of the query can be printed using the following code. Note that logging / printing of results should be avoided in a performance test script as takes additional time and processing power to log something that we should not need to look at. This is provided for debugging purposes only.

while (rset.next()) {
  lr.log_message(rset.getString(1));
}

Being Java of course we have to provide a very verbose bit of code to get our errors handled. The following is the entire database_query function.

public int database_query(String SQL_QUERY) {
  Statement stmt = null;
  ResultSet rset = null;

  try {
    connection.setAutoCommit(false);
    stmt = connection.createStatement();
    rset = stmt.executeQuery(SQL_QUERY);
    // The transaction can be set to a Passed Status
    lr.set_transaction_status(lr.PASS);

    // Print the results (For Debugging Only)
    /*
      while (rset.next()) {
        lr.log_message(rset.getString(1));
      }
      lr.log_message("SQL Query Executed Successfully");
    */
    rset.close();
  catch (SQLException e) {
    // SQL Query has failed
    lr.log_message("Caught Exception: " + e.getMessage());
    // The transaction must be set to a Failed Status
    lr.set_transaction_status(lr.FAIL);
    return 1;
  }
  return 0;
}

Scripting a Scenario

Through the use of functions such as database_query, our actual script can be kept very clean. An example that performs three SQL queries on the database with a 5 second think time is below. Note that the example shows SELECT statements, however any SQL command could be used.

public int action() throws ClassNotFoundException, SQLException {

  // Database Query Example 1
  lr.start_transaction("Database_Query_1");
  database_query("select BANNER from VERSION");
  lr.end_transaction("Database_Query_1", lr.AUTO);

  lr.think_time(5);

  // Database Query Example 2
  lr.start_transaction("Database_Query_2");
  database_query("SELECT * from POST_DETAILS");
  lr.end_transaction("Database_Query_2", lr.AUTO);

  lr.think_time(5);

  // Database Query Example 3
  lr.start_transaction("Database_Query_3");
  database_query("select * from users");
  lr.end_transaction("Database_Query_3", lr.AUTO);

  lr.think_time(5);

  return 0;
} //end of action

Verifying the Response

In database performance testing, it is not commonplace to verify the SQL results other than to ensure that the query has been successfully executed by the server. The Java executeQuery function utilised by thedatabase_query function will return an exception if the query is not successfully executed. The db_query function will return the first 10 records from a result set. To get the full SQL results parsed across the network, you will need to process them with the “getString” function of the resultset. In the case where keyword type checks would want to be performed on the SQL results, the same code to print the response could be used to check the results one by one with some form of validation.

Example Script

Here is the full example script for you to try. Please feel free to submit corrections or improvements.

/*
 * LoadRunner Java script.
 * Description: Oracle Database Testing via JDBC
 */

import lrapi.lr;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.*;

public class Actions
{
    // Create global connection variable
    private Connection connection;

    // VUser Init
    public int init() throws ClassNotFoundException, SQLException {
        // Initialize DB connection
        //connection = null;
        try {
     // Load Oracle JDBC Driver
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (Exception ex) {
     // If driver load is unsuccessful
     lr.log_message("Database Driver not found");
     lr.abort();
 }
 try {
     // Specify the JDBC Connection String (jdbc:oracle:thin:@HOST:PORT:SID)
     String url = "jdbc:oracle:thin:@performance.com:1521:SID";
     // Connect to URL using USERNAME and PASSWORD
     connection = DriverManager.getConnection(url,"USER_NAME",lr.decrypt("password"));
     lr.log_message("JDBC Connection Successful");
        } catch (SQLException e) {
     // If Connection Failed
     lr.log_message("Database Connection Failed, Please check your connection string");
     lr.abort();
 }
     return 0;
    } //end of init

    public int action() throws ClassNotFoundException, SQLException {

 // Database Query Example 1
 lr.start_transaction("Database_Query_1");
 database_query("select BANNER from TABLE_1");
 lr.end_transaction("Database_Query_1", lr.AUTO);

 // Database Query Example 2
 lr.start_transaction("Database_Query_2");
 database_query("SELECT * from TABLE_2");
 lr.end_transaction("Database_Query_2", lr.AUTO);

 // Database Query Example 3
 lr.start_transaction("Database_Query_3");
 database_query("select * from TABLE_3");
 lr.end_transaction("Database_Query_3", lr.AUTO);

 return 0;
    } //end of action

    public int end() throws Throwable {
 connection = null;
        return 0;
    } //end of end

    // Function: database_query
    // Argument: SQL Query String
    // Performs an SQL Query String, and returns pass or fail for the current transaction
    //
    public int database_query(String SQL_QUERY) {
       Statement stmt = null;
       ResultSet rset = null;

       try {
    connection.setAutoCommit(false);
    stmt = connection.createStatement();
    rset = stmt.executeQuery(SQL_QUERY);
    lr.set_transaction_status(lr.PASS);
    // while (rset.next()) {    // Print the results of the query
    //     lr.log_message(rset.getString(1));    // Note: This should be used for debugging only,
    // }       // as it slows down execution time
    //lr.log_message("SQL Query Executed Successfully");
    rset.close();
 } catch (SQLException e) {
     // SQL Query has failed
     lr.log_message("Caught Exception: " + e.getMessage());
     lr.set_transaction_status(lr.FAIL);
     return 1;
 }
 return 0;
    }
}