Bug #24408 MyODBC 5.00x not working from VB6
Submitted: 18 Nov 2006 1:27 Modified: 20 Nov 2006 11:15
Reporter: Frank Gump Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.008 beta4 OS:Microsoft Windows (WinXP Pro)
Assigned to: CPU Architecture:Any
Tags: ODBC5-RC

[18 Nov 2006 1:27] Frank Gump
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.
[20 Nov 2006 11:15] Tonci Grgin
Hi Frank and thanks for your report. We are aware of this problem and working on solution.

Thanks for your interest in MySQL.

Explanation: Although reported against most recent version, this report is a duplicate of Bug#17604 and Bug#24054. It is also connected to Bug#24163.