Bug 57266 - using executeQuery() method for CallableStatements in JDBC Sample
Summary: using executeQuery() method for CallableStatements in JDBC Sample
Status: NEEDINFO
Alias: None
Product: JMeter
Classification: Unclassified
Component: Main (show other bugs)
Version: 2.11
Hardware: All All
: P2 enhancement (vote)
Target Milestone: ---
Assignee: JMeter issues mailing list
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-11-26 11:46 UTC by Andrea Coloru
Modified: 2014-12-01 08:10 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Andrea Coloru 2014-11-26 11:46:56 UTC
Hi 
I have to test some procedures  for a particulare Database whose jdbc driver only supports the executeQuery() method  for CallableStatement object.

I saw the source code of the class org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler  and it uses the execute() method like below
            } else if (CALLABLE.equals(_queryType)) {
                CallableStatement cstmt = getCallableStatement(conn);
                int out[]=setArguments(cstmt);
                // A CallableStatement can return more than 1 ResultSets
                // plus a number of update counts.
                boolean hasResultSet = cstmt.execute();
Is it possible to improve the callable in a way that the user can choose which of the 2 methods to use?
Thanks 

Andrea
Comment 1 Sebb 2014-11-26 19:56:38 UTC
Actually there are 3 execute methods:

boolean java.sql.PreparedStatement.execute()
ResultSet java.sql.PreparedStatement.executeQuery()
int java.sql.PreparedStatement.executeUpdate()

It's not trivial to determine whether an SQL statement is a Query or an Update.
[Nor should JMeter have to make this determination.]
So the user will need to specify whether to use executeQuery() or executeUpdate().

Having said that, JMeter already supports PREPARED_SELECT and PREPARED_UPDATE.

Have you tried using PREPARED_QUERY?
Comment 2 Andrea Coloru 2014-11-27 09:30:44 UTC
Thanks for the quick response.
Unfortunatly  if I use Prepared statement in place of CallableStatement  I obtain a ClassCastException :ScPreparedStatement cannot be cast to java.sql.CallableStatement.
The reason of this Exception I think probably resides in the fact that PreaparedStatement doesn't support INOUT and OUT parameters  and all the stored procedures I have to test have at least an Output Parameter.
For this reason I proposed To you to insert into the dropDown list of the "Query Type:" the choice of the Callable by execute and Callable by executeQuery.
I understand that mine is a  particular request but i decided to submit it  as this could make the  sampler JDBC Sampler more flessibile.

Thanks in advance.
Comment 3 Sebb 2014-11-27 15:56:08 UTC
(In reply to Sebb from comment #1)
<snip/>
> Having said that, JMeter already supports PREPARED_SELECT and
> PREPARED_UPDATE.
> 
> Have you tried using PREPARED_QUERY?

Oops - that should be PREPARED_SELECT
Comment 4 Sebb 2014-11-27 16:31:12 UTC
I see now.
The CallableStatement interface is needed in order to register (IN)OUT parameters, but the JMeter code does not create a CallableStatement for PREPARED_(SELECT|UPDATE) sample types.

As you suggest, one possible solution is to add CALLABLE_SELECT and CALLABLE_UPDATE Query Types.

Given that output parameters cannot be registered for a PreparedStatement, I wonder if JMeter should try and create a CallableStatement if any (IN)OUT types have been specified. This would be another way to solve your issue.

At present - as you have found - this causes a class cast error.
It seems very unlikely that any test plans would rely on this behaviour, so it should not cause an incompatible change.



By the way, which JDBC driver does not support execute()?
Comment 5 Andrea Coloru 2014-11-28 10:05:36 UTC
Hi 
The driver is proprietary for the system.
Unfortunately I cannot tell you more info because of my firm security policies.

what can i do is to show you a sample code that we use to call stored procedures with the driver:

CallableStatement statement = null;
statement = connection.prepareCall("{call procedure_name(?,?,?)}");
statement.setString(1, (String) "paramin");
statement.setString(2, (String) "paramin2");
statement.registerOutParameter(3, Types.VARCHAR);
Resultset resultSet = statement.executeQuery();

As You can see there is nothing special  except the executeQuery in place of execute.

Regards
Comment 6 Sebb 2014-11-29 00:06:22 UTC
OK, thanks.

It may be worth filing a bug report with the JDBC supplier.
AFAIK, the execute() method is not optional.

What does the driver do when you call execute() ?
Does it throw an Exception?


Does it support executeUpdate()?
Comment 7 Andrea Coloru 2014-12-01 08:10:18 UTC
When I tried to use the execute() in place of execute Query I obtained thi sqlException (taken direclty from jmeter output): Response message: java.sql.SQLException: Not supported for anything other than Aggregate Message Service Class. Use executeQuery().

I didn't understand the mening of the First message, however I have to test with executeQuery()  because it is the "official" method used by developers to call our backend .

many thanks