Performance Testing Oracle via JDBC with LoadRunner – Basics

Pre-requisites

1.JDK 1.6 or above should be installed in your machine to run the script.

2.Required OJDBC.jar (5 or 6 & 14 )  Drivers are required.

/*
 * 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.*;

//char *request;// variable decleration
//
//    char *openReq;//variable decleration

public class Actions
{
    // Create global connection variable
    private Connection connection;
    public int SequnceId ;
    // 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:@host:SID";
          
       
        // Connect to URL using USERNAME and PASSWORD
        connection = DriverManager.getConnection(url,"",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");
  
//    int SeqId=("Insert into STAGPHUB1.STG_ENROLLMENT_CNTRL (BAN,MSISDN,PROCESSED_FLG,ENROLL_TYPE,COMMIT_TIMESTAMP,PROGRAM,ENROLL_DATE,DEENROLL,DE_ENROLL_REASON,ROW_ID,EQUIPMENT_ID,SOC_PROVISION_FLG,REDEEMED_FLAG,REDEMPTION_DATE,OVERRIDE_FLAG,OVERRIDE_DATE,OVERRIDE_REF_NO,MANUAL_PUBLISH_FLG,RETRY_COUNTER,LAST_UPD_SOURCE,LAST_UPD) 

  
    //int SeqId = database_query("update wholesale.wholesale_transaction set results='SUCCESS', status='COMPLETE' where status ='BILLING'and CREATED_DATE > sysdate-1");
//    
//    int SeqId1 = database_query("update wholesale.wholesale_transaction set  status='COMPLETE' where status ='NEW'and CREATED_DATE > sysdate-1");
//    
//    int SeqID2 = database_query("update wholesale.wholesale_transaction set  status='COMPLETE' where status ='SWITCH-COMPLETE'and CREATED_DATE > sysdate-1");
  
  
    int seqID = database_query("Enter your Query ");
  
    //seqId.execute();
  
    //int seqid1= database_query("commit");
  
    lr.end_transaction("Database_Query_1", lr.AUTO);
      
    return 0;
    }

    public int end() throws Throwable {
    connection = null;
        return 0;
    }
    public int database_query(String SQL_QUERY) {
       Statement stmt = null;
       ResultSet rset = null;

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