MySQL Bugs: #72535: Writing record with ADO incomplete / wrong result (ODBC driver 5.02.06 32 bit)

Bug #72535	Writing record with ADO incomplete / wrong result (ODBC driver 5.02.06 32 bit)
Submitted:	4 May 2014 21:35	Modified:	23 Sep 5:16
Reporter:	Rene Roessler	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.2.6	OS:	Windows (Microsoft Windows 7 Service Pack 1 build 7601, 64-bit)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ADO

Description:
Running the code creates a recordset as following:

Input fields

SUserName := Admin
SUserPassword := Byte Array (8 Bytes)
SUserRights := ByteArray (6 Bytes)

Stored in database

SUserName  "" (empty string)
SUserPassword  Null (no Bytes)
SUserRights  ByteArray 6 Bytes (as expected)

Please tell me the reason for this. Wrong option flags?

I have used 16427
The recommended value of 3 (ADO, VB) doesn't work either.

Thanks for ideas to let it work :)

Cheers Rene

How to repeat:
CREATE TABLE `sce_usernames` (
  `SUsername` varchar(50) NOT NULL DEFAULT '',
  `SUserPassword` blob,
  `SUserRights` blob,
  PRIMARY KEY (`SUsername`),
  UNIQUE KEY `SUsername_UNIQUE` (`SUsername`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Use this:

Typelib: Microsoft ActiveX Data Objects 6.1 (ADO 6.1)

    Dim Conn As ADODB.Connection, RS As ADODB.Recordset, BA() As Byte
    
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = "DRIVER={MySQL ODBC 5.2 Unicode Driver};SERVER=localhost;DATABASE=sce;UID=...;PWD=...;Option=16427"
    
    Conn.Open
    
    If Conn.State = 1 Then
    
        Set RS = New Recordset
        RS.Open "SCE_USERNAMES", Conn, adOpenDynamic, adLockOptimistic
                
        If RS.State <> 0 Then
            RS.AddNew
                    
            RS.Fields("SUserName").Value = "Admin"
            
            BA = StrConv("Password", vbFromUnicode)
            RS.Fields("SUserPassword").Value = BA
                    
            BA = StrConv("Rights", vbFromUnicode)
            RS.Fields("SUserRights").Value = BA
                    
            RS.Update
            RS.Close
        End If
    
        Conn.Close
    End If

Hi Rene,

Thanks for your interest in MySQL software.
I have verified the bug as described.

However, it cannot qualify as S1 bug because there are possible workarounds such as using VARCHAR column types instead of BLOB. This might be not convenient for your application logic, but still doable.

Therefore I am changing the severity to S2.

Hi, when do you think, the bug is fixed? One of our customers likes to use MySQL with our software.

Cheers Rene

Posted by developer:
 
With BLOB types ADO expects assignment of an array type.
Strings will not work.
Here is the relevant VB code:

Sub bug_18709944()
Dim con
    Set con = CreateObject("ADODB.Connection")
    con.Open "Driver={MySQL ODBC 9.0 Unicode Driver};" & _
            "Server=localhost;Port=3306;" & _
            "User=root;" & _
            "Database=test;"

con.Execute "DROP TABLE IF EXISTS sce_usernames"
con.Execute "CREATE TABLE `sce_usernames` ( " & _
  "`SUsername` varchar(50) NOT NULL DEFAULT ', " & _
  "`SUserPassword` varchar(50), " & _
  "`SUserRights` blob, " & _
  "PRIMARY KEY (`SUsername`), " & _
  "UNIQUE KEY `SUsername_UNIQUE` (`SUsername`) " & _
  ")"
  
    Dim rs
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SCE_USERNAMES", con, adOpenDynamic, adLockOptimistic
            
    If rs.State <> 0 Then
        rs.AddNew
                
        rs.Fields("SUserName").Value = "Admin"
    
        rs.Fields("SUserPassword").Value = "abc"
                
        Dim byteArr(10) As Byte
        byteArr(0) = CByte(Asc("A"))
        
        rs.Fields("SUserRights").Value = byteArr
                
        rs.Update
        rs.Close
    End If
    
    conn.Close

End Sub

The above code works.
Closing the bug as "Could not reproduce" since it is working with ODBC Driver 9.0/9.1