How to set up parameters of a stored procedure in JMeter using a JDBC Request

Question

I am planning to load the database using a stored procedure callable statement. The test plan I am creating in JMeter looks like below:

- Test plan
  - Thread Group
     - JDBC Connection Configuration
     - JDBC Request
     - View results tree
     - Summary Report

JDBC Connection Configuration is based on tests that already work. The question is with my JDBC Request:
Variable name: is the database name same as in JDBC Connection configuration
Query Type: Callable Statement
Query: {call schema.dbpk_utilities.get_user_id(?,?,?,?,?,?,?)}
Parameter values: S12345, HR, OUT, NULL,NULL,NULL, NULL
Parameter Types: VARCHAR, VARCHAR, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR
Variable names: username,hr, id, four, five, six, seven

The error I get is:

Response message: java.lang.NumberFormatException: For input string:
"OUT"

Can anyone tell me why the response message? Is it possible to call Stored procedures in JMeter? I am struggling to OK this request!

Aketza Ayo Aketza Ayo · Accepted Answer · 2016-06-16T09:55:09

Following the 'no space rule' from the previous post I found out why the request was responding with java.lang.NumberFormatException: For input string: "OUT" message. See below

Variable Name: Oracle

SQL Query: Callable Statament

Query: {call nspishr.dbpk_user_utilities.get_user_details(?,?,?,?,?,?,?)}

Parameter Values: S12345,DMS,OUT,NULL,NULL,NULL,NULL

Par Types: VARCHAR,VARCHAR,OUT INTEGER,VARCHAR,VARCHAR,VARCHAR,VARCHAR

Variable names: username,dms,id,four,five,six,seven

Notice how the parameters are registered in this call. In my procedure param 1 and param 2 are IN parameters. These are made IN parameters implicitly by giving them a parameter value S12345 and HR in my case.

From param 3 all the way to param 7 are OUT parameters. Param 4, 5, 6 and 7 have NULL values. Therefore no need to register them as OUT parameters. Param 3 (the id), however, is the OUT parameter and it has to be registered as such by explicitly saying OUT in the parameter value and then saying OUT INTEGER in the parameter types section. The thing to remember is that you need to specify OUT in both parameter value and parameter type. Not to forget the datatype of the parameter in the parameter types field. ie OUT INTEGER, OUT VARCHAR ...

Hope this helps

How to set up parameters of a stored procedure in JMeter using a JDBC Request

2 Answers