Bug #24163 Error when retrieving records to VBA/VB6 app: service returned an E_FAIL status
Submitted: 10 Nov 2006 2:52 Modified: 23 Aug 2007 14:10
Reporter: Erica Moss Email Updates:
Status: Duplicate Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 OS:Windows (XP sp2)
Assigned to: CPU Architecture:Any
Tags: ODBC5-RC

[10 Nov 2006 2:52] Erica Moss
Receiving the following error message for these two ADO RecordSet properties when certain records are read:
PageCount : <Data provider or other service returned an E_FAIL status.> : RecordCount: same as above

Attached is a VB application, and two mysqldump files containing different data sets that are failing in a similar manner.

The read block is simply:

    Do Until oRs.EOF
        For Each oField In oRs.Fields
            objStream.Write oField.Value & ","
        objStream.Write vbCrLf

It always fails on the 4th record of the 1st attached data set, and the 10th record of the the 2nd data set

How to repeat:
Source the two dump files into a test database
Run the VB application and enter the name of the test database created
There are four different SQL statements that can be tried in the source which will cause the same failure on the two different data sets.
[10 Nov 2006 2:53] Erica Moss
first data set

Attachment: test.out (application/octet-stream, text), 3.80 KiB.

[10 Nov 2006 2:54] Erica Moss
2nd data set

Attachment: test2.out (application/octet-stream, text), 17.85 KiB.

[10 Nov 2006 2:54] Erica Moss
vb form

Attachment: Form1.frm (application/octet-stream, text), 4.85 KiB.

[10 Nov 2006 2:55] Erica Moss
vb code

Attachment: Insert-update-vb-test.vbp (application/octet-stream, text), 895 bytes.

[10 Nov 2006 20:50] Erica Moss
I spent a bit more time on this issue, and it appears that the records in question fail because a date column is set to a default value of 0000-00-00.  For some reason when a record is fetched with a date field set like this then both BOF, and EOF are set TRUE.  When I changed the value of the cert_issue_date as follows, then the record could be successfully fetched:

UPDATE eric.test_table SET cert_issue_date = '2000-12-11' WHERE name = 'EAA AVIATION FOUNDATION INC' AND tail_num = 'N1AC';

This appears to be the same issue that was raised back in defect #1680, but it was closed as not reproducible at that time.
[1 Feb 2007 9:22] Jess Balint
Based on ODBC spec, I have fixed ODBC v5 to return an error 22007:

Error #1(22007)error=0: [MySQL][MyODBC 5.00.12] Invalid datetime format

This fix is committed in rev#785.
[12 Mar 2007 18:37] Erica Moss
Should 3.51 also be changed?
[22 Aug 2007 19:32] Claudio Morinico
It also happens when subqueries return a null value. It happens on recordcount and also if you try MoveNext when the next record has the NULL value. It happens on the two versions (3.51.19 and 5).

Please check it. This bug is making it very complex to migrate from Access to MySQL.
[23 Aug 2007 14:10] Tonci Grgin
Eric, in fact, this is a duplicate of Bug#9410 (problem fixed with patch for Bug#13766) and the rest of it is noted in Bug#30539. Agree? Did you try with .18 or .19 with options described in Bug#13766?
[30 Aug 2007 17:58] Tonci Grgin
Claudio, working on Bug#28016 I tested NULL and IFNULL behavior in regards of updating records like this:

Do While Not rs.EOF
    if IsNULL(rs(4)) Then 
        rs.Fields("Marker") = "Z1"
    end if

on a table with ~300000 records and found no problems. Can you be more specific and provide me with self contained test case, if possible, exhibiting the problem bothering you so I can check.