MySQL Bugs: #11970: MyODBC fails to issue correct UPDATE statement

Bug #11970	MyODBC fails to issue correct UPDATE statement
Submitted:	15 Jul 2005 15:05	Modified:	3 Jan 2008 3:48
Reporter:	Edrick Duero	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.0.11.0	OS:	Windows (Windows XP)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	RC-ODBC5

Description:
A float column with a long decimal digit value that where inserted from MySQL Query Browser or MySQL Command Line, cannot be updated/modified using VB6 and ADO 2.7/2.8.

I'm using:
MySQL 4.1.11-nt-max
MySQL Client 5.0.0
MyODBC 3.51.11-2
MDAC 2.8/2.7
VB6
Windows XP SP2

Error Message: Row cannot be located for updating. Some values may have been changed since it was last read.

How to repeat:
Create a table with float not null column

mysql> create table t1(
    ->   id integer unsigned not null auto_increment,
    ->   f float not null,
    ->   primary key(id)
    -> ) type=innodb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t1(f) values(144.977477477477);
Query OK, 1 row affected (0.02 sec)

** use the same value: 144.977477477477

Create a project in VB6 w/ a Form & Command button:

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.CursorLocation = adUseClient
    cn.ConnectionString = "Provider=MSDASQL.1;User Name=root;Password=password;Extended Properties=""driver={MySQL ODBC 3.51};DATABASE=test;OPTION=3;PORT=0;SERVER=localhost"""
    cn.Open

    with rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        Call .Open("select * from t1 where id=1", cn, adOpenStatic, adLockOptimistic)
        .Fields("f").Value = 123.123
        .Update ' //error here: Row cannot be located for updating. Same values...
    End With
End Sub

Suggested fix:
no idea.

Verified with Access 2003 and Server 4.1.13.

I am getting the same behavior with this configuration: MS Access 2000, MyODBC 3.51.11-2, mysql 5.0.15 and Windows XP SP2.

Also applies to DOUBLE columns.

I am able to avoid this issue by using ADO version 2.8 instead of v2.5! That said, I think this was a bug with MS ADO rather than with MyODBC.

Hello

I am using VB6, SQL server 2000, and ADO 2.5, and saw this problem recently.  But it does not happen all the time.  My VB6 program had been working ok for months.  For some reason, this problem started appearing.  In our case, the columns are INT and VARCHARs.  Ideas?

Thanks

Joe

I must disagree as this is not a bug in MyODBC!

On page http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1 you will find all the ways ADO uses to update dataset:

   adCriteriaKey = 0      Uses only the primary key
   adCriteriaAllCols = 1  Uses all columns in the recordset
   adCriteriaUpdCols = 2  (Default) Uses only the columns in the recordset that have been modified
   adCriteriaTimeStamp= 3 Uses the timestamp column (if available) in the recordset

From what I see in logs, and by common sense, I can say neither 1 nor 2 (Default)  will work as you can't match FLOATs with "=":
UPDATE `test`.`testbug11970` SET `f`=1.23123001098632810e+002 WHERE `id`=1 AND `f`=1.44977005004882810e+002

Changing update criteria to 0 (PK only) corrects the situation:
UPDATE `test`.`testbug11970` SET `f`=1.23123001098632810e+002 WHERE `id`=1

Consider following, and working, example:
    Private Sub bnBug11970_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnBug11970.Click
        Dim p_conn As New ADODB.Connection
        p_conn.Open("Provider=MSDASQL.1;Extended Properties=;SERVER=localhost;DRIVER={MySQL ODBC 3.51 Driver};OPTION=1+2+32;PWD=;DATABASE=test;UID=root")
        'p_conn.Open("DSN=myodbc1")
        'p_conn.Open("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;OPTION=3;PWD=;DATABASE=test;UID=root") '16387, 1+2+32

        p_conn.Execute("drop table if exists testbug11970")
        p_conn.Execute("create table testbug11970(id integer unsigned not null auto_increment primary key, f float not null) type=innodb")
        p_conn.Execute("insert into testbug11970(f) values(144.977477477477)")

        Dim p_rs As New ADODB.Recordset

        p_rs.CursorLocation = CursorLocationEnum.adUseClient
        p_rs.Properties("Update Criteria").Value = 0 'adCriteriaKey

        p_rs.Open("SELECT * FROM testbug11970 where Id = 1", p_conn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic)

        p_rs.MoveFirst()

        p_rs.Update(1, 123.123)

        Dim oField As ADODB.Field
        Dim oStr As String
        oStr = ""
        Do Until p_rs.EOF
            For Each oField In p_rs.Fields
                oStr = oStr & (oField.Value) & ","
            Next
            MsgBox(oStr)
            oStr = ""
            p_rs.MoveNext()
        Loop
        p_rs.Close()
        p_rs = Nothing
        p_conn.Close()
        p_conn = Nothing
    End Sub

However, in MyODBC 5, UPDATE statement is completely wrong (but we are aware of this problem as only SELECT is currently fixed):
UPDATE def.test.testbug11970 SET f= WHERE id=1 AND f=

So, I'm changing synopsis and the version affected accordingly.

Tonci's evaluation is correct. Updating a row matching all columns will not work with a floating point type. You must set the update criteria to adCriteriaKey:

rs.Properties("Update Criteria").Value = ADODB.ADCPROP_UPDATECRITERIA_ENUM.adCriteriaKey

or

rs.Properties("Update Criteria").Value = 0