Search code examples
jdbcjmeterbeanshell

Benshell Sampler to update Oracle Request throwing error

I am trying to run a loop to create records in oracle data base through a bean shell sampler and it is throwing error,

Response code:500
Response message:org.apache.jorphan.util.JMeterException: Error invoking bsh method: eval   Sourced file: inline evaluation of: ``import java.sql.*; import oracle.jdbc.*; import org.apache.jmeter.protocol.jdbc. . . . '' : Error in method invocation: Method setString( int, java.lang.String ) not found in class'org.apache.commons.dbcp2.DelegatingStatement'

Script for request:

import java.sql.*;
import oracle.jdbc.*;
import org.apache.jmeter.protocol.jdbc.config.DataSourceElement;

ResultSet rs = null;
ResultSetMetaData rsmd = null;
PreparedStatement pstmt = null;

Connection conn = DataSourceElement.getConnection("IMPACTConnectionPrerequisitsII");
String Query = "DECLARE \n" 
           + "j number(4) \n"
           + "BEGIN \n"
           + "FOR j in 11 .. 12 LOOP \n"
           + "INSERT INTO FWA_STAGE_TU \n"
           + "(ID,EXT_ID,BATCH_NO,OPERATION,BLOCK_ROLL_UP_LAST_RECORD,LAST_UPDATED_DATE,ERROR_FLAG,TRIAL_NO,TRIAL_ALIAS_CODE, \n"
            + "COUNTRY_CODE,DISPLAY_UNIT_NO,GROUP_NO,TRIAL_UNIT_REFERENCE,PRIMARY_INVESTIGATOR,PRIMARY_CENTRE,OCATION_NO, \n"
            + "PURPOSE_CODE,MANAGING_MED_UNIT_CODE,UPDATING_MED_UNIT_CODE,FINANCE_MED_UNIT_CODE,PATIENTS_ALL_SET_UP_FLAG, \n"
            + "PATIENT_MONITORING_FLAG,COMMENTS,CONFIRMED_FLAG,CONFIRMED_BY,CONFIRMED_DATE,ROLLUP_ROLLDOWN_PLANNED_FIGS, \n"
            + "DOC_COLLECTION_INDICATOR,VALIDATED_PAT_CAP,MAXIMUM_PAT_CAP,PREFERRED_LANGUAGE_CODE,NEW_DISPLAY_UNIT_NO, \n"
            + "TOTAL_OPEN_DCF_ISSUES_NO,ORDER_INTERVAL,ORDER_INTERVAL_UNITS,DATA_SOURCE_CODE,RANK_SEQ) \n"
            + "VALUES (j, 1, 10, 'I', NULL, SYSDATE, 'N', 102922, 'TESTTRIAL4', 'BEL', \n"
           + "j, NULL, 'BELTU'||j, 130262, 124236, NULL, 'PATTR', 'FP', 'FP', 'FP', 'N', 'Y', 'TESTTU'||j, \n"
           + "'Y', 999999, SYSDATE, NULL, 'P', 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL) \n"
           + "END LOOP \n"
           + "END";

pstmt = conn.prepareStatement(Query);
Statement stmt = conn.createStatement();
try {
    stmt.setString(1, "123456");
    stmt.registerOutParameter(2, OracleTypes.CURSOR);
    stmt.executeUpdate();
}
catch(Throwable ex) {
    log.error("Error message: ", ex);
    throw ex;
}
finally {
    if (rs != null) {
        rs.close();
    }
    if (stmt != null) {
        stmt.close();
    }
    if (conn != null) {
        conn.close();
        log.info("!!!! Connection closed to database !!!!");
    }
}

JDBC configuration: enter image description here

This same query worked fine on the SQL Developer Tool

Solution

  • Agreed with the comment, you try, catch and finally block should be looking like this, rest it seems fine.

    try {
    pstmt = conn.prepareStatement(Query);
    pstmt.executeUpdate();
}
catch(Throwable ex) {
    log.error("Error message: ", ex);
    throw ex;
}
finally {
    if (rs != null) {
        rs.close();
    }
    if (pstmt != null) {
        pstmt.close();
    }
    if (conn != null) {
        conn.close();
        log.info("!!!! Connection closed to database !!!!");
    }