MySQL Bugs: #34330: MyODBC 3 and 5/ ADO Unable to write to a Bit(>1) field

Bug #34330	MyODBC 3 and 5/ ADO Unable to write to a Bit(>1) field
Submitted:	5 Feb 2008 19:46
Reporter:	Erica Moss	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.23	OS:	Windows (XP sp2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The code below reads a bit field from one record, and assigns that same value to the bit field of a different record and attempts to update the record.

In the case of a record set opened as adUseClient the error received on update is:

"Row cannot be located for updating. Some values may have been changed since it was last read.  IDispatch error #3128"

In the case of adUseServer the error received on update is:

"Query-based update failed because the row to update could not be found."

How to repeat:
SETUP
CREATE TABLE testtable (C1 INT PRIMARY KEY AUTO_INCREMENT, C2 BIT(28))

INSERT INTO testtable VALUES (1, b'11',0), (2,b'1000010010001101111010011')

Sub Test()
   Dim strSQL, oConn, oRs, fld, arrBin
   Dim testval, bPass, arrTest(2,3), b
   bPass = cBool(True)
   Set oConn = CreateObject("ADODB.Connection")
   Set oRS = CreateObject("ADODB.RecordSet")
   strSQL = "SELECT * FROM testtable"

   MakeArray 0,0,0,3,arrTest,0
   MakeArray 1,9,27,211,arrTest,1

   ' open connection
   oConn.ConnectionString = connstr
   oConn.CursorLocation = adUseClient
   oConn.Open

   ' open record set
   oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText

 ' read the bit field from the 2nd record, write it back to the 1st one
   ors.MoveNext
   arrBin = oRs.Fields("C2").Value

   oRs.Close
   oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText
   oRs.Fields("C2").Value = arrBin

   oRs.Update

   ' Verify value against original test values array
   oRs.Close
   oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText

   Set fld = oRs.Fields("C2")
   arrBin = fld.Value
   For b = 1 To LenB(arrBin)
      testval = AscB(MidB(arrBin, b, 1))
      If testval <> arrTest(1,b-1) Then
         bPass = cBool(False)
      End If
   Next
   Call Cleanup (oConn, oRs)
End Sub

Sub MakeArray(a, b, c, d, byref arr, i)
	arr(i,0)=a
	arr(i,1)=b
	arr(i,2)=c
	arr(i,3)=d
End Sub

In the case of 3.51.23 both client and server fail.  In the case of 5.1.1 server passes, client fails

Test case added to ado_conformance tests
/bugs/34330.vbs

attaching traces for both drivers

5.1.1 driver trace

Attachment: SQL_511_driver.LOG (application/octet-stream, text), 202.31 KiB.

3.51.23 driver trace, server side cursor test

Attachment: SQL_351_driver.LOG (application/octet-stream, text), 152.22 KiB.

Delayed pending fix of bug#40187

taking not assigned bugs. perhaps this bug is fixed in 5.1

Updating BIT field failed with MyODBC 5.1.8.
However, the problem seems to be different this time:

Microsoft Cursor Engine: Row cannot be located for updating. Some values may have been changed since it was last read

IMO this error is connected to bug #30349.