MySQL Bugs: #27125: Runtime error -2147467259 (80004005)

Bug #27125	Runtime error -2147467259 (80004005) - Unknown Error
Submitted:	14 Mar 2007 9:58	Modified:	2 Apr 2007 12:35
Reporter:	Angelo Mondati	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.14	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
Updating to MyODBC 3.51.14 this error occurs (previous version seems not be affected).
In a Visual Basic 6 application we have a global ADO recorset (ADODB.Recordset) in a search form.
When a button is pressed a query is built with the search parameters and then executed (the query uses the LIKE operator) :

Option Explicit
Dim rsProd As ADODB.Recordset

Private Sub FindButton_Click()
....
sSql = ' A query is built using the search parameters matching with the LIKE operator
If rsProd.State = adStateOpen Then rsProd.Close
rsProd.Open sSql
......
End Sub

The first time the search button is pressed the query is performed. If the query is executed again by pressing the search button (changing the search parameters or not) the error 80004005 raises in the "rsProd.Open sSql" instruction.

How to repeat:
1) Create a form in VB 6 with a button

2) Define a global recordset on the form
    Dim rsProd As ADODB.Recordset

3) in the button handler run a query:
    sSql = ' build a query string that use the LIKE operator
    If rsProd.State = adStateOpen Then rsProd.Close
    rsProd.Open sSql

Workaround:

If the recordset is created from scratch every time, it seems to work:

Option Explicit
Dim rsProd As ADODB.Recordset

Private Sub FindButton_Click()
....
sSql = ' A query is built using the search parameters matching with the LIKE
operator
If rsProd.State = adStateOpen Then rsProd.Close

' --- WORKAROUND ----
' Dispose the recordset and creates a new one
Set rsProd = Nothing
Set rsProd = New ADODB.Recordset
Set rsProd.ActiveConnection = gDBConn
rsProd.CursorLocation = adUseClient
rsProd.CursorType = adOpenStatic
rsProd.LockType = adLockReadOnly
' --- WORKAROUND ----

rsProd.Open sSql
......
End Sub

Hi Angelo and thanks for your report.

Please make a basic, self-sufficient test case demonstrating this problem and attach it to this report.

Hi,

we have the same problems with 3.51.14, Delphi 5 and ADO components. Up to < 3.51.14 everything worked well, but now we encounter the following problem when doing this (I tried with several different MySQL server versions and different client hosts):

 - use client-side cursors
 - create an ADO-query
 - perform a arbitrary SELECT command
 - close ADO-query
 - perform a different arbitrary SELECT command

==> error occurs

This happens only with client-side cursors and only with the new .14

Hi Carsten,

have you tried to create a new recordset object every time you run the query instead of close the recordset and open it with the new SQL query (see WORKAROUND above which for me works) ?

Hi Angelo,

I have not tried this, altough I think that it will work.
But this is no option for me, as I cannot modify all my applications in such way.

As I said: it worked with all prior MyODBC versions and I hope there will be a solution for the new .14!

cheers,
carsten

Building a sample test case I've noticed that the error arise only if three or more fields are specified into the SQL query.
Wanting to extract one/two fields, no problem occurs.

TEST CASE:

- Create a form
- Add a TextBox and assign the name txString
- Add a Button and assign the name btSearch
- Add a ListBox and assign the name lbResults
- Add a reference to Microsoft Activex Data Object

Source code:

Option Explicit

Dim gDbConn As ADODB.Connection
Dim rsProducts As ADODB.Recordset

Private Sub btSearch_Click()
    Dim sSql As String
    Dim sWHERE As String
    
    ' User have to insert a string to search
    If Len(txString) = 0 Then
        Exit Sub
    End If
    ' Some controls should me made on the txString content ...
    ' If the result recorset is open close before a new search is done
    If rsProducts.State = adStateOpen Then rsProducts.Close
    ' Build a simple query
    sWHERE = sWHERE & " WHERE MaDESCRIZI LIKE '%" & txString & "%'"
    sSql = "SELECT MaCODMAG,MaDESCRIZI,MaSTATO FROM Magaz "
    sSql = sSql & sWHERE & " ORDER BY MaDESCRIZI"
    ' Run the query
    ' HERE THE SECOND TIME THE QUERY IS EXECUTED THE UNKNOWN ERROR ARISE
    ' ONLY IF THREE OR MORE FIELDS ARE SPECIFIED IN THE SQL QUERY
    rsProducts.Open sSql
    ' Fill the list box with the result records
    lbResults.Clear
    Do While Not rsProducts.EOF
        lbResults.AddItem rsProducts.Fields(0) & " / " & rsProducts.Fields(1) & " / " & rsProducts.Fields(2)
        rsProducts.MoveNext
    Loop
End Sub

Private Sub Form_Load()
    Dim sConn As String
    
    ' New ADODB connection object
    Set gDbConn = New ADODB.Connection
    ' Build the connection string (put a valid userid and password here)
    sConn = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=" & Chr(34) & "Driver={MySQL ODBC 3.51 Driver};DESC=;DATABASE=esitrade;UID=USERID;PASSWORD=PASSWORD;Option=2083;STMT=;"
    sConn = sConn & "PORT=3306;SERVER=localhost;"
    gDbConn.ConnectionString = sConn & Chr(34)
    ' Try to establish the connection with MySql
    On Error GoTo ErrConn
    gDbConn.Open
    On Error GoTo 0
    ' Create a recordset object
    Set rsProducts = New ADODB.Recordset
    Set rsProducts.ActiveConnection = gDbConn
    rsProducts.CursorLocation = adUseClient
    rsProducts.CursorType = adOpenStatic
    rsProducts.LockType = adLockReadOnly
    Exit Sub
ErrConn:
    MsgBox gDbConn.Errors(0).Number & " " & gDbConn.Errors(0).Description
End Sub

Private Sub Form_Unload(Cancel As Integer)
    ' Close recordset
    If rsProducts.State = adStateOpen Then rsProducts.Close
    ' Close connection
    gDbConn.Close
End Sub

Test Case Source Code

Attachment: TestCase.zip (application/zip, text), 2.30 KiB.

Hi Angelo.

As I have VS2005 Pro so I had to rewrite your test case (attached) and found no problems running it, with "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=" and without that.

As can be seen on attached image, I clicked 3 times (notice the duplicates in sorted list box).

If you can provide me with more info, please reopen the report.

Test case VS2005

Attachment: Bug27125-TestCase.zip (application/x-zip-compressed, text), 8.06 KiB.

Executed 3 times in a row

Attachment: 27125.jpg (image/jpeg, text), 44.70 KiB.

From your test case I saw you open the connection and create the recordset each time the button is pressed.
Try to move the connection creation code and the recordset creation code from the button handler to the onload event (when the form is loaded) and the close of the connection and of the recordset in the unload event (where the form is closed), as i've done in my sample.

Angelo, I'm not sure what is it that you're trying to prove? It seems you want *me* to fix "If rsProd.State = adStateOpen Then rsProd.Close" behavior in VB. Can you please place a few break points and see what's happening in your code. If you find anything MySQL-code related, please reopen this report.

Thanks for your interest in MySQL!

Angelo, I may not know much about VB but here's what I've found (creation part relocated to separate procedure):

 - with code:
        If rsProducts.State <> 0 Then 'adStateClosed; it is ENUM so your condition is not good
            rsProducts.Close()
        End If

        rsProducts.Open(sSql)
        ' Fill the list box with the result records
        Do While Not rsProducts.EOF
            lbResults.Items.Add(rsProducts.Fields(0).Value.ToString & " / " & rsProducts.Fields(1).Value.ToString & " / " & rsProducts.Fields(2).Value.ToString)
            rsProducts.MoveNext()
        Loop
End Sub
some part of COM interface stays unreleased (I think the Fields we used to display data) causing NPE at .Open

 - using this code:
        'If rsProducts.State <> 0 Then 'adStateClosed
        '    rsProducts.Cancel()
        '    rsProducts.Close()
        'End If

        rsProducts.Open(sSql)
        ' Fill the list box with the result records
        Do While Not rsProducts.EOF
            lbResults.Items.Add(rsProducts.Fields(0).Value.ToString & " / " & rsProducts.Fields(1).Value.ToString & " / " & rsProducts.Fields(2).Value.ToString)
            rsProducts.MoveNext()
        Loop
        rsProducts.Close()
End Sub
works perfectly.

I realy don't think it's our fault...

Don't dismiss this as being non repeatable.

MyODBC 3.51.14 has many bugs which we all need to be aware of. We have just had to roll some fairly major websites back to MyODBC 3.51.12 because of this.

For example, (using MySQL 5.0.24 and Classic ASP under IIS 6.0)...

1) Resultset Persistance.
If you have say a 'description' column in a table which is a MySQL data type of TEXT then you cannot refer to it using (Recordset1.Fields.Item("description").Value) more than once in the same resultset.  Try making two references one after the other - only the first will show. Rolling back to 3.51.12 cures the problem. Of course you can work around by storing to a variable and reusing that - but you shouldn't have to.

2) Cursor problems:
A construct such as   
for (var i=0; !MM_rs.EOF && i < MM_offset; i++) 
{
    MM_rs.MoveNext();
}
fails under 3.51.14 - but works fine in 3.51.12

This is frustrating for everyone as there was some Stored Procedure support in MyODBC 3.51.14 which was the reason we made the change (MyODBC 3.51.12 offers almost none).

Best Regards
Nigel Sedgwick
www.e-cc.org

It is weird that closing the recordset after the listbox is filled or close the recordset before re-opening it has this effect.

Moreover a recordset cab be either adStateClosed or adStateOpen, according to the Microsoft deocumentation.

Nothing's got dismissed. I just feel that in this particular report and with test cases used there is no problem I can verify...

Angelo, yes, it's weird and it can have 5 states according to MS docs (and a combinations, thus ENUM):
ObjectStateEnum Values

Specifies whether an object is open or closed, connecting to a data source, executing a command, or retrieving data.
Constant 	Value 	Description
adStateClosed 	0 	The object is closed
adStateOpen 	1 	The object is open
adStateConnecting 	2 	The object is connecting
adStateExecuting 	4 	The object is executing a command
adStateFetching 	8 	The rows of the object are being retrieved

Nigel, please post your problems in a new bug report together with full test case and other relevant info so I can check.

Problems with rs.State checking can not be repeated with MyODBC 3.51.15