0

On Jmeter, I need to execute a stored procedure via a JDBC request, for which some of the parameters to be passed are tables (table valued parameters).

My current approach is to get these table parameters generated dynamically (by running SQL queries, each as a separate JDBC request), and pass to the stored procedure as parameters.

Questions: How do I pass table valued parameters in a JDBC request? What would be the parameter type for it?

Learner4life
  • 3
  • 2

2 Answers2

0

I don't think that JDBC Request sampler is flexible enough to cover your use case, you will have to go for DelegatingPreparedStatement from the JSR223 Sampler

You can get a connection from the JDBC Connection Configuration as follows:

def connection = org.apache.jmeter.protocol.jdbc.config.DataSourceElement.getConnection('your variable name of pool here')

more information:

Dmitri T
  • 159,985
  • 5
  • 83
  • 133
  • Thank you, I'm going to try this solution. Would I still be able to use the ResultSets from multiple other JDBC requests and pass them as parameters (programmatically) to the SQL stored procedure? – Learner4life Aug 11 '20 at 02:36
0

Got it working! The stored procedure of interest needs parameters of varieties of types - UUID, Boolean, string and Table-valued parameters.

For each of the table-valued parameters, a new SQLServerDataTable was created, and data fed from a JDBC request's "Result Variable Name".

    String ConnStr = "jdbc:sqlserver://<servername>;databaseName=<DBname>;integratedSecurity=true;"
    Connection connection = DriverManager.getConnection(ConnStr);
    
    String sql = "EXEC dbo.prInsertUpdateEntity ?,?,?,?,?,?";
    
    UUID VCID = UUID.fromString("842a50d9-4091-4c8f-9f04-a853356c7b29");
    
    PreparedStatement stmt = connection.prepareStatement(sql);
    stmt.setObject(1,VCID);
    stmt.setBoolean(2,true);
    stmt.setString(3,"Username");
    ((SQLServerPreparedStatement) stmt).setStructured(4,"dbo.Termtype",TT);
    ((SQLServerPreparedStatement) stmt).setStructured(5,"dbo.Descriptortype",Desc);
    ((SQLServerPreparedStatement) stmt).setStructured(6,"dbo.SemanticType",ST);
    ResultSet rs = stmt.executeQuery();
Learner4life
  • 3
  • 2