Description:
Thank you for the new MyODBC beta. However, I have a few bugs to possibly remprt and hope you can verify or correct me. I'm using VB6 w/SP6, ADO 2.8, MySQL 5.0.27, and MyODBC 3.51.13 on WinXP Pro SP2. I have to use beta build 13 because it's the only one that seems to work okay and supports Stored Procedures (I cannot get release build of MyODBC 3.5.12 to call stored procs.) I do have some occassional issues with MyODBC 3.51.13 (as it's beta) and this is why I wanted to try a newer driver.
So, I've tried the last 2 MyODBC 5 betas and neither of them work at all! Here's what I'm doing and the erros I get:
** Opening an ADO connection to MySQL like this
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL Connector/ODBC v5};SERVER=localhost;DATABASE=hlo;UID=root;PWD=;OPTION=3"
conn.CursorLocation = adUseClient
conn.Open
Then I try a simple select from a table
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM Client", conn
Do Until rs.EOF
With rs
Debug.Print rs!ClientID
rs.MoveNext
End With
Loop
rs.Close
That returns an empty resultset! I've tried using server side cursors to see if that made a difference and still get an empty recordset (no errors on this one though). I tried replacing the rs.open line with Set rs = conn.Execute("SELECT * FROM Client") and it still does the same thing.
Then I tried to call a stored procedure in MySQL like this:
Dim rs As ADODB.Recordset
Dim cm As ADODB.Command
Set cm = New ADODB.Command
Set rs = New ADODB.Recordset
cm.CommandText = "qry_ClientsAll()"
cm.CommandType = adCmdStoredProc
Set cm.ActiveConnection = conn
Set rs = cm.Execute
This ERRORS on the line Set rs = cm.Execute with the following error:
Run-time error '-2147467259 (80004005)':
[MySQL][MyODBC 5.00.08] mismatched brace near:
This works OK with MyODBC driver 3.51.13. There is nothing fancy about the stored proc. The fact is that it doesn't work with any stored proc even a simple one.
Lastly, I tried to call a stored procedure (which has a parameter) in a different way:
Dim rs As New ADODB.Recordset
Set rs = conn.Execute("call qry_Clients('A')")
Do Until rs.EOF
Debug.Print rs!SSN
rs.MoveNext
Loop
rs.close
This time I get the error:
Run-time error '-2147467259 (80004005)':
[MySQL][MyODBC 5.00.08][MySQL] 1312 PROCEDURE hlo.qry_Clients
can't return a result set in the given context
I have no idea what this means. Basically NOTHING WORKS! Is this driver only made to work with .NET or MySQL 5.1? Am I doing something wrong? Any help would be greatly appreciated.
Thank you very much,
Frank
How to repeat:
Create a VB project and include the Microsoft Access Data Objects 2.8 Library. Use the example code above.
Description: Thank you for the new MyODBC beta. However, I have a few bugs to possibly remprt and hope you can verify or correct me. I'm using VB6 w/SP6, ADO 2.8, MySQL 5.0.27, and MyODBC 3.51.13 on WinXP Pro SP2. I have to use beta build 13 because it's the only one that seems to work okay and supports Stored Procedures (I cannot get release build of MyODBC 3.5.12 to call stored procs.) I do have some occassional issues with MyODBC 3.51.13 (as it's beta) and this is why I wanted to try a newer driver. So, I've tried the last 2 MyODBC 5 betas and neither of them work at all! Here's what I'm doing and the erros I get: ** Opening an ADO connection to MySQL like this Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.ConnectionString = "DRIVER={MySQL Connector/ODBC v5};SERVER=localhost;DATABASE=hlo;UID=root;PWD=;OPTION=3" conn.CursorLocation = adUseClient conn.Open Then I try a simple select from a table Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open "SELECT * FROM Client", conn Do Until rs.EOF With rs Debug.Print rs!ClientID rs.MoveNext End With Loop rs.Close That returns an empty resultset! I've tried using server side cursors to see if that made a difference and still get an empty recordset (no errors on this one though). I tried replacing the rs.open line with Set rs = conn.Execute("SELECT * FROM Client") and it still does the same thing. Then I tried to call a stored procedure in MySQL like this: Dim rs As ADODB.Recordset Dim cm As ADODB.Command Set cm = New ADODB.Command Set rs = New ADODB.Recordset cm.CommandText = "qry_ClientsAll()" cm.CommandType = adCmdStoredProc Set cm.ActiveConnection = conn Set rs = cm.Execute This ERRORS on the line Set rs = cm.Execute with the following error: Run-time error '-2147467259 (80004005)': [MySQL][MyODBC 5.00.08] mismatched brace near: This works OK with MyODBC driver 3.51.13. There is nothing fancy about the stored proc. The fact is that it doesn't work with any stored proc even a simple one. Lastly, I tried to call a stored procedure (which has a parameter) in a different way: Dim rs As New ADODB.Recordset Set rs = conn.Execute("call qry_Clients('A')") Do Until rs.EOF Debug.Print rs!SSN rs.MoveNext Loop rs.close This time I get the error: Run-time error '-2147467259 (80004005)': [MySQL][MyODBC 5.00.08][MySQL] 1312 PROCEDURE hlo.qry_Clients can't return a result set in the given context I have no idea what this means. Basically NOTHING WORKS! Is this driver only made to work with .NET or MySQL 5.1? Am I doing something wrong? Any help would be greatly appreciated. Thank you very much, Frank How to repeat: Create a VB project and include the Microsoft Access Data Objects 2.8 Library. Use the example code above.