MySQL Bugs: #11525: Data provider or other service returned an E

Bug #11525	Data provider or other service returned an E_FAIL status
Submitted:	23 Jun 2005 10:04	Modified:	7 Nov 2007 10:05
Reporter:	Junias Sinaga	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1Alpha	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I was creating a search query in Visual Basic 6 with MyODBC 351. The MySQ ver 5 server was installed on a RedHat Linux machine.
I have 2 teksbox for search criteria. One is based on date field and the other on String field. When I tries a simple :

Select tgl,NoVoucher from jurnalhead where status = '1' . 

And then run RSCari.recordcount it return an error I mentione above. But when the user filled in one of the texfield, the query run well.

It's just weird.

How to repeat:
Table struckture :

  CREATE TABLE `jurnalhead` (                                
              `CreateUser` varchar(10) default NULL,                   
              `CreateStamp` datetime default NULL,                     
              `UpdateUser` varchar(10) default NULL,                   
              `UpdateStamp` datetime default NULL,                     
              `NoVoucher` varchar(10) NOT NULL default '',             
              `Tgl` datetime NOT NULL default '0000-00-00 00:00:00',   
              `Status` tinyint(1) default NULL,                        
              PRIMARY KEY  (`NoVoucher`,`Tgl`)                         
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC  
  

The VB code :

'the connection:
    Conn.Open "driver={MySQL ODBC 3.51 Driver};server=1.1.1.240;database=clara;uid=jsi;pwd=junias; "

Private Sub cmd_cari_Click()
    Dim Condition As String
    MyStr = "Select tgl,NoVoucher from jurnalhead  where Status = 1 "
    If opt_Cari.Value = True Then
        If ChkTgl.Value = 0 And ChkNo.Value = 0 Then
            MsgBox "Anda harus memilih salah satu pilihan pencarian", vbExclamation + vbOKOnly, Me.Caption
            Exit Sub
        End If
        If ChkTgl.Value = 1 Then
            Condition = "tgl >= '" & Format(DTPicker1.Value, "YY/mm/DD") & "' and tgl <= '" & Format(DTPicker2.Value, "YY/mm/DD") & "'"
        End If
        If ChkNo.Value = 1 Then
            Condition = IIf(txt_cariVoucher.Text <> "", Condition & IIf(Condition <> "", " " & CmbCondition.Text & " ", "") & "`novoucher` like '" & KillQuote(txt_cariVoucher.Text) & "%' ", Condition)
        End If
    End If
    MyStr = MyStr & IIf(Condition <> "", " and " & Condition, "")
    If RSCari.State = adStateOpen Then RSCari.Close
    RSCari.Open MyStr, Conn, adOpenDynamic, adLockOptimistic
    Flex_Cari.Clear
    Flex_Cari.Rows = 2
    If RSCari.RecordCount > 0 Then Set Flex_Cari.DataSource = RSCari   <===here the error accured
    ArrangaFlexCari
End Sub

Could you please write here full text of test case ? because I have problem with follow variables: opt_Cari, Flex_Cari and so on

The complete VB project

Attachment: Transaksi Jurnal- To be Send.zip (application/x-zip-compressed, text), 14.18 KiB.

I have uploaded the full project in the files section.
The problem happened when I thick the option button "Tampilkan Semua" on the searching tab and hit the "Cari" button.

After scouring around. I've got the problem.It's because I inserted a null value into the tgl fields which allow's no null. I use this script :

insert into jurnalhead(createuser,createstamp,updateuser,updatestamp,novoucher,status) values
('JSI',now(),'JSI',now(),'BB05060001',1)

Well, I guess this doesn't bother me anymore. But the error still accur though.

Really I was not able to convert and build your test case on VB (MSDN 2002). I would like to replace variables on constants. Could you please write here What constants must be instead of follow variables:

opt_Cari.Value
ChkTgl.Value 
ChkNo.Value 
txt_cariVoucher.Text
CmbCondition.Text
RSCari.State 

Thanks

Have you tried the file that I uploaded here ?
Even simpler. You could just create a simple VB 6 project that execute a query like :

Select * from jurnalhead 

to a recordset,and then bind the recordset to a datagrid control.
While in the table you do the insert statement I wrote above :

insert into
jurnalhead(createuser,createstamp,updateuser,updatestamp,novoucher,status)
values
('JSI',now(),'JSI',now(),'BB05060001',1)

Where field Tgl which is a primary key and not alow null, inserted a null value.

Thanks. I was able to reproduce null value of Tgl. I attach my test case and screen copy of the bug.
Tested on Win 2000 Sp4, Microsoft Visual Basic .NET, MyODBC 3.51.11, ADO 2.7

Test case

Attachment: test.zip (application/x-zip-compressed, text), 4.81 KiB.

screen copy

Attachment: Tgl_null.gif (image/gif, text), 5.83 KiB.

Welll... actually that's not what I have in mind.
I tried it in Visual Bsic 6 and the datagrid didn't actually show any result. It prompt the error message I mentioned in the subject instead, the recordset was never created.
Nevertheles, if what ever you might do later with this bug also solve the problem I encountered. I think it'll be just fine.
Thanks.

Could you please test with latest released version 3.51.19.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Data provider or other service returned an E_FAIL status.
REASON
This error occurs due to a Date field in database which contains Null Value. Try to store some Valid Date in Date fields.

This can be boiled down to a very simple case:

"If RSCari.State = adStateOpen Then RSCari.Close
RSCari.Open MyStr, Conn, adOpenDynamic, adLockOptimistic
If RSCari.RecordCount > 0 Then Set Flex_Cari.DataSource = RSCari <=here the error"

1) Use recordset
2) Close recordset
3) Reopen recordset
4) Use recordset

I will make a test case according to this schema.

Test case

Attachment: 11525.vbs (application/octet-stream, text), 2.98 KiB.

First thing, to test 3.51 driver, please change {MySQL ODBC 5.1 Driver} to {MySQL ODBC 3.51 Driver}

Hi all. I've tried to cover all aspects of discussion in my attached test case:
 1) Closing / Reopening RS
 2) Manipulating NULLs  in Date field
 3) Calling RecordCount

Environment:
 MySQL server 5.0.50-pb1046-log on WinXP Pro SP2 localhost
 MyODBC 3.51.21GA

I have found no problems, ie. script works as expected.

However, c/ODBC 5.1Alpha crashes.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Explanation: Running the same script on same environment but with MyODBC 5.01.01 rev.836 produces correct results. Marking the report as "Closed" now.