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:
DELIMITER //
DROP PROCEDURE IF EXISTS buildTree //
CREATE PROCEDURE buildTree(IN rootId INT)
BEGIN
DECLARE bla CURSOR FOR ( SELECT 1 );
OPEN bla;
SELECT 1;
END //
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
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
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