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:Microsoft Windows (XP sp2)
Assigned to: Assigned Account CPU Architecture:Any

[5 Feb 2008 19:46] Erica Moss
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
[5 Feb 2008 19:54] Erica Moss
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 Feb 2008 19:57] Erica Moss
5.1.1 driver trace

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

[5 Feb 2008 20:01] Erica Moss
3.51.23 driver trace, server side cursor test

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

[20 Oct 2008 19:55] Jess Balint
Delayed pending fix of bug#40187
[10 Oct 2010 14:12] Lawrenty Novitsky
taking not assigned bugs. perhaps this bug is fixed in 5.1
[3 Dec 2010 8:19] Bogdan Degtyariov
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.