1

I wrote a stored procedure in MySQL that uses cursors. Everything worked fine when calling that spoc from mysql and mysql workbench. However, as soon as I try to call the spoc from within Excel VBA, I always get an error: "Commands out of sync; you can't run this command now". The line 'OPEN cursorName;' leads to the error message.

The strange thing is: as soon as my spoc does not use a cursor OR it uses a cursor but does no "SELECT" statement, everything works from Excel too. Even multiple SELECT statements work, but of course not with a cursor.

The connection settings are definitely ok. What I found in addition when using mysql-proxy was that the logged output is a bit strange, as there is no call to the stored procedure (but I get results without using a cursor) but a couple of other calls (I guess from the ODBC driver itself?).

I'm looking forward for any answer! Thank you all in advance.

grange

A minimal example:

MySQL

DELIMITER //

DROP PROCEDURE IF EXISTS buildTree // 
CREATE PROCEDURE buildTree(IN rootId INT)
BEGIN


DECLARE bla CURSOR FOR ( SELECT 1 );
OPEN bla; 
SELECT 1;

END //

Excel VBA (not complete but runnable. Closing cursor etc is missing here)

Public Function QueryMySQL()
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 5.3 Unicode Driver}; SERVER=localhost; PORT=4040;  
DATABASE=mydbname; UID=username; OPTION=3"
conn.CursorLocation = adUseClient
conn.Open

Set spCommand = New ADODB.Command
spCommand.CommandText = "buildTree"
spCommand.CommandType = adCmdStoredProc
spCommand.CommandTimeout = 30
Set spCommand.ActiveConnection = conn

Set param1 = spCommand.CreateParameter("rootId", adInteger, adParamInput)
param1.Value = 1551
spCommand.Parameters.Append param1

Set rs = spCommand.Execute
End Function

MySQL-Proxy log

Logged query: SET NAMES utf8
Logged query: SET character_set_results = NULL
Logged query: SET SQL_AUTO_IS_NULL = 0
Logged query: select database()
Logged query: select database()
Logged query: SELECT @@tx_isolation
Logged query: set @@sql_select_limit=DEFAULT
grange
  • 582
  • 5
  • 17
  • Did you already had a look into http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now Some answers indicate that this is a limitation with MySQL. – MP24 Aug 13 '14 at 20:22
  • Thank you for your answer (and sorry for the late response, was on holidays last week). I already read about the "fetch imaginary other resultsets" workaround but I cannot see any possibility to do it in my case, as I already get the error in the line that executes the command (CALL spoc-name). Do you have any suggestions what I could further try? thanks a lot! – grange Aug 26 '14 at 07:04

1 Answers1

0

After trying around a whole week, I found a workaround. Instead of using the ADODB.Command (which is designed for exactly this, if I understood correctly), I used a regular query. Be aware that it does not work (in my case) if I end the statement with ";", which is somehow a bug I think.

to make a long story short: this is working for me: 

Set mysqlConAsync = New ADODB.Connection
With mysqlConAsync
.ConnectionString = "DRIVER={MySQL ODBC 5.3 Unicode Driver}; SERVER=localhost; PORT=3306; DATABASE=dbname; UID=root; OPTION=67108867"
.CursorLocation = adUseClient
End With
mysqlConAsync.Open
Set rst = New ADODB.Recordset
With rst
qry = "CALL buildTree()"
.CursorLocation = adUseClient
.Open query, mysqlConAsync, , adLockReadOnly, adAsyncFetch
End With
grange
  • 582
  • 5
  • 17