TroubleShooting: SQL Error – The Statement did not return a result set

Problem: When using a stored procedure to get data from MySQL data source in eFORMz, eFORMz returns the error, “Statement did not return a result set”

But you see a result set returned from MySQL using the stored procedure call.

Cause: MySQL returns the count of the number of rows affected by a SQL statement or stored procedure as part of the result set. The JDBC driver used in MySQL to get to the data source can become confused as to what it should put into the ResultSet, therefore throwing the error.

Resolution: The stored procedure must include SET NOCOUNT ON at the beginning of the procedure declaration to suppress the “n rows affected” counts to avoid the confusion. For example:

CREATE PROCEDURE p2
AS
 
SET NOCOUNT ON
 
DECLARE @t1 TABLE(
    REF VARCHAR(20)
)
...........