MySQL Bugs: #71431: ODBC 5.2 Driver - can't call Stored Procedure

Bug #71431	ODBC 5.2 Driver - can't call Stored Procedure
Submitted:	20 Jan 2014 17:15	Modified:	1 Apr 2014 10:47
Reporter:	Tina Basinger	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Any
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
When I run the following code with the MySQL ODBC 5.1 Driver, it works.  When I upgrade my driver to version 5.2, it fails with the error: "[ODBC 5.2(a) Driver] Commands out of sync; you can't run this command now".  This error occurs on the ExecuteReader line. As you can see, this connection has not done anything else, there are no other results that need processed, as is the typical response to this error.  This error did NOT occur in 5.1, and does occur in 5.2, so something within the ODBC driver has changed and is not working correctly.

            OdbcConnection objConnection = null;
            OdbcDataReader dataReader = null;

            try
            {
                objConnection = new OdbcConnection(connectionString);
                objConnection.Open();

                OdbcCommand objCommand = new OdbcCommand();
                objCommand.Connection = objConnection;
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.CommandText = "{ CALL GetActualData(?, ?, ?) }";
                objCommand.Parameters.AddWithValue("clientIDValue", clientID);
                objCommand.Parameters.AddWithValue("equipIDValue", equipID);
                objCommand.Parameters.AddWithValue("dataPointNameValue", dpNam);
                dataReader = objCommand.ExecuteReader();
            }

How to repeat:
Create a stored procedure in mySQL.  
Call the stored procedure using the ODBC 5.1 drivers and .NET.  It will work.  
Upgrade to the ODBC 5.2 drivers, and it will fail.

Hi Tina,

Thank you for your interest in MySQL software.
Unfortunately, I was not able to repeat the problem. Please check the screenshot (rest_result_screenshot.png) from my test application showing the results returned by the stored procedure.

Which version of Connector/ODBC driver 5.2 you are using? I recommend the most recent GA release version 5.2.6.

Test program dialog screenshot

Attachment: test_result_screenshot.png (image/png, text), 11.73 KiB.

C# test project

Attachment: bug71431.zip (application/zip, text), 9.78 KiB.

Also, uploading the test C# project for Visual Studio.
Perhaps you could give it a try and tell me whether the error is still showing up?
Thanks.

Thank you for your timely feedback!  I gave your project a try, and it worked for me as well.  My stored procedure was a bit more advanced though, and after much more debugging and drilling into it, I determined that I needed a begin/end wrapped around the action occurring inside a cursor loop.  I did not need this extra Begin/End in 5.1, but I do need it in 5.2.

Again, thank you for looking into this, and helping focus my efforts onto the stored procedure itself.
-Tina

delimiter $$

CREATE PROCEDURE `GetActualData`(IN clientIDValue INT, IN equipIDValue VARCHAR(45), IN dataPointNameValue VARCHAR(45))
BEGIN

DECLARE dpName VARCHAR(45);
DECLARE equipID INT;

DECLARE cur1 select x, y from greensleevesdata.actualdata WHERE z = clientIDValue 
AND equipmentID LIKE equipIDValue
AND dataPointName LIKE dataPointNameValue
order by equipmentID, dataPointName;

OPEN cur1; 

read_loop:  LOOP
	FETCH cur1 INTO equipID, dpName;
		BEGIN
			SELECT * FROM tableX WHERE equipmentID = equipID AND dataPointName = dpName;
		END;
END LOOP;

END$$

Thanks for your reply, Tina.

I might need to look deeper into the particular issue with your stored procedure.
At the first glance the problem might be caused by multiple result-sets as SELECT is spinning inside a LOOP. 

Can you please tell me if there might be more than one result returned by the cursor, so the LOOP with SELECT spins more than once producing several result sets?

Yes, that is correct.  I loop through my cursor and do another query based on the info in the cursor's current location.  Is there a better way to collect data like this?

Verified with the following table and stored procedure:
--------------------------------------------------------------
create table tab (x int);
insert into tab values (22), (33), (44);
--------------------------------------------------------------
CREATE PROCEDURE `GetActualData`(a int, b int, c varchar(255))
begin
 declare z INT;
 declare cur1 cursor for select x from tab;

 open cur1;

 read_loop: LOOP 
    FETCH cur1 INTO z;
    select concat ('DATA1 = ', z, ':', a, '-', b, '-', c) as actualdata1;
  END LOOP;
end;

Community user ran into this issue here:
http://stackoverflow.com/questions/29208354/commands-out-of-sync-using-mysql-odbc-connecto...