Bug #26950 MyODBC / ADO adUseServer cursor is lost after updating adLongVarWChar field
Submitted: 8 Mar 2007 6:42 Modified: 21 Nov 2008 15:20
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1 OS:Microsoft Windows (Windows XP)
Assigned to: Jess Balint CPU Architecture:Any
Triage: D1 (Critical)

[8 Mar 2007 6:42] Erica Moss
Description:
When the recordSet.Update function is called to update an adLongVarChar field, the field is updated but the recordset is immediately lost.  This is happening with driver cursors, regardless of whether the cursor is opened optimistic or pessimistic.

When the next update is called the below test function will exit with the following error:
-2147467259:Query-based update failed because the row to update could not be found.

This is also occuring the 5.0 driver and is entered as a seperate bug for tracking purposes

How to repeat:
mysql> CREATE TABLE foo (C1 INT PRIMARY KEY, C2 TEXT, C3 INT);
mysql> INSERT INTO foo VALUES (1, 'FOO', 55), (2, 'BAR', 66);

Sub LONGTEXTBUG()
    On Error GoTo EH
    Dim strSQL As String
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field
    Dim fldName As String
    Dim lngType As Long

    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset

    strSQL = "SELECT * FROM foo"

    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=35;"

'   strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";UID=root;PWD=mypass;OPTION=3;"

    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseServer
    connection1.Open
   ' open recordset
    recset1.Open strSQL, connection1, adOpenForwardOnly, _
             adLockOptimistic, adCmdText
             
    If Not recset1.EOF Then
            recset1.MoveFirst
    End If
    
    If recset1.Supports(adUpdate) Then
            While Not recset1.EOF
                For Each fld In recset1.Fields
                    lngType = CLng(fld.Type)
                    fldName = fld.Name
                    
                    If lngType = adLongVarChar Then
                        fld.Value = "test"
                        recset1.Update
                    End If

                    If lngType = adInteger And fldName <> "C1" Then
                        fld.Value = 99
                        recset1.Update    ' <----- fails here!
                    End If
                Next
                recset1.MoveNext
            Wend
    End If
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    msgbox Err.Number & ":" & Err.Description
    MsgBox "failed on column " & fldName
End Sub
[12 Jun 2007 9:21] Tonci Grgin
General query log:
070612 11:18:04	      4 Connect     root@localhost on test
		      4 Query       SET SQL_AUTO_IS_NULL=0
070612 11:18:05	      4 Query       select database()
070612 11:18:06	      4 Query       SELECT @@tx_isolation
		      4 Query       USE test
070612 11:18:07	      4 Query       DROP TABLE IF EXISTS bug26950
		      4 Query       CREATE TABLE bug26950 (C1 INT PRIMARY KEY, C2 TEXT, C3 INT)
070612 11:18:08	      4 Query       INSERT INTO bug26950 VALUES (1, 'FOO', 55), (2, 'BAR', 66)
		      4 Query       SELECT * FROM bug26950
070612 11:18:10	      4 Query       SELECT * FROM bug26950
070612 11:18:13	      4 Query       UPDATE bug26950 SET C2='test' WHERE (C1=1 AND C2='FOO' AND C3=55 )
070612 11:18:14	      4 Query       UPDATE bug26950 SET C3=99 WHERE (C1=1 AND C2='' AND C3=55 )
070612 11:18:19	      4 Quit       
C2='' should have been C2='FOO'.
[12 Jun 2007 9:22] Tonci Grgin
ODBC trace

Attachment: SQL-log.zip (application/x-zip-compressed, text), 4.94 KiB.

[12 Jun 2007 9:24] Tonci Grgin
Slightly modified test case that was used to produce logs

Attachment: Bug26950VB.txt (message/rfc822, text), 2.46 KiB.

[12 Jun 2007 9:28] Tonci Grgin
I would expect this to fail due to ss cursor. Changing:
        'connection1.CursorLocation = CursorLocationEnum.adUseServer
        connection1.CursorLocation = CursorLocationEnum.adUseClient
and
        'recset1.Open(strSQL, connection1, CursorTypeEnum.adOpenForwardOnly, _
        recset1.Open(strSQL, connection1, CursorTypeEnum.adOpenKeyset, _
                 LockTypeEnum.adLockOptimistic, 1)

yields expected result.
[12 Jun 2007 9:29] Tonci Grgin
And the general query log for successful test:
      5 Query       UPDATE `test`.`bug26950` SET `C2`='test' WHERE `C1`=1
      5 Query       UPDATE `test`.`bug26950` SET `C3`=99 WHERE `C1`=1 AND `C3`=55
      5 Query       UPDATE `test`.`bug26950` SET `C2`='test' WHERE `C1`=2
      5 Query       UPDATE `test`.`bug26950` SET `C3`=99 WHERE `C1`=2 AND `C3`=66
[12 Jun 2007 9:32] Tonci Grgin
Eric, this is not a bug due to the known limitations of server-side cursor.

Environment:
 - MySQL server 5.0.44BK on WinXP Pro SP2 localhost
 - MyODBC 3.51.15GA
[12 Jun 2007 23:33] Jim Winstead
I don't understand why this was closed as 'Not a Bug'. Which known limitation of server-side cursors is this? Where is it documented?

This looks sort of like a duplicate of Bug #19065.
[13 Jun 2007 14:01] Tonci Grgin
Jim, http://dev.mysql.com/doc/refman/5.0/en/cursor-restrictions.html: "Cursors are read-only; you cannot use a cursor to update rows."
[13 Jun 2007 21:35] Erica Moss
Here is another VB test function which covers the binary data case:

SETUP:
CREATE TABLE bintest (C1 INT PRIMARY KEY, C2 BLOB);
INSERT INTO bintest(C1) VALUES (1);
copy test file to c:\

VB CODE:
Private Sub binTest()
    On Error GoTo EH
    Dim strSQL As String
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field

    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset
    Dim fso, FILE, fileSize, offSet, blockSize, bytesReadTotal As Integer
    Dim fileName As String
	Dim binArr() As Byte
	
    blockSize = 500
    bytesReadTotal = 0
    offSet = 1
    ReDim binArr(blockSize)

    ' Find out the size of the binary test file
    Set fso = CreateObject("Scripting.FileSystemObject")
    fileName = "C:\E.jpg"
    Set FILE = fso.GetFile(fileName)
    fileSize = FILE.size
    Set FILE = Nothing
    ' ******************************************
    
    strSQL = "SELECT c1, c2 FROM bintest"
    
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"

    ' open the connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseClient
    connection1.Open
    
    ' open record set
    recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText
    recset1.MoveFirst

	' get a reference to the binary field
    Set fld = recset1.Fields("c2")
    
	' open the test file
    Open fileName For Binary Access Read As #1
	
    While bytesReadTotal <> fileSize
        ' Read a block from the test File
        If fileSize - bytesReadTotal < blockSize Then
            blockSize = fileSize - bytesReadTotal
            ReDim binArr(blockSize - 1) ' size the array for the final read
        End If
        Get #1, offSet, binArr
        
        ' Append that to the field.Value
        fld.AppendChunk (binArr)

        bytesReadTotal = bytesReadTotal + blockSize
        offSet = bytesReadTotal + 1
    Wend
    recset1.Update
    
    ' cleanup
    Close ' close the file
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
	
EH:
    Debug.Print Err.Number & ":" & Err.Description
End Sub
[13 Jun 2007 21:35] Erica Moss
binary test file

Attachment: E.jpg (image/jpeg, text), 2.51 KiB.

[13 Jun 2007 22:03] Erica Moss
Whoops, I had changed this to adUseClient / adOpenStatic just to make sure it worked that way.  Change it back to adUseServer / adOpenForwardOnly to run it against "server side" cursor
[19 Jun 2007 5:15] Jim Winstead
Just a note of clarification: the page that Tonci cites is about real server-side cursors, which the driver does not use. Cursors are emulated in the driver, and should support updates when possible.
[19 Jun 2007 6:45] Tonci Grgin
Switching back to "Verified" as I was wrong. Thanks Jim, Eric.
[22 Oct 2007 12:27] Susanne Ebrecht
Bug #26951 is a duplicate of this bug here
[12 Nov 2007 23:32] Erica Moss
Added test case to ADO conformance test suite 
ado-compliance/trunk/bugs/26950.vbs

Attached new ODBC trace
[12 Nov 2007 23:34] Erica Moss
ODBC trace of ADO conformance testcase run against 3.51.21

Attachment: SQL.LOG (application/octet-stream, text), 119.10 KiB.

[10 Jun 2008 5:17] Jess Balint
we need the same fix for bug#19065 to work with unicode

Index: driver/utility.c
===================================================================
--- driver/utility.c    (revision 1113)
+++ driver/utility.c    (working copy)
@@ -229,6 +229,7 @@
     {
       case SQL_LONGVARBINARY:
       case SQL_LONGVARCHAR:
+      case SQL_WLONGVARCHAR:
         irrec->searchable= SQL_PRED_CHAR;
         break;
       default:
[13 Jun 2008 20:37] Jess Balint
Patch committed as rev 1117, will be released in 5.1.5.
[19 Jun 2008 17:44] Erica Moss
This still appears to have a problem.  Unfortunately the original test case for this bug used a mysql TEXT column which currently passes, MEDIUMTEXT also passes, however LONGTEXT fails although all of them are seen by ADO as adLongVarWChar.  I've updated and committed the test cases for this bug in the ado test suite to reproduce this problem.

Tested against 5.1.5r1122_20080617_0347

ODBC trace attached.
[19 Jun 2008 17:45] Erica Moss
new odbc trace with LONGTEXT failure

Attachment: SQL.LOG (application/octet-stream, text), 136.02 KiB.

[21 Oct 2008 19:08] Jess Balint
ADO is multiplying the size returned from SQLDescribeCol() by 2 (for Unicode). I think we can overload the FLAG_COLUMN_SIZE_S32 and return the correct value when it's SQL_WLONGVARCHAR:

=== modified file 'driver/utility.c'
--- driver/utility.c    2008-08-22 22:12:25 +0000
+++ driver/utility.c    2008-10-21 19:06:08 +0000
@@ -115,6 +115,9 @@
     }
     irrec->type_name= (SQLCHAR *) irrec->row.type_name;
     irrec->length= get_column_size(stmt, field);
+    if (capint32 && irrec->length == INT_MAX32 &&
+        irrec->concise_type == SQL_WLONGVARCHAR)
+      irrec->length /= sizeof(SQL_WCHAR);
     irrec->octet_length= get_transfer_octet_length(stmt, field);
     irrec->display_size= get_display_size(stmt, field);
     /* prevent overflowing */
[5 Nov 2008 17:17] Jim Winstead
Please add a comment before the new code that explains why this little hack is there.
[5 Nov 2008 18:26] Jess Balint
/* Hack for ADO - prevent ADO multiplying the length by sizeof(SQLWCHAR) from overflowing the result */
[14 Nov 2008 17:35] Jess Balint
Pushed as rev 813. Will be released in 5.1.6.
[21 Nov 2008 15:20] Tony Bedford
An entry was added to the 5.1.6 changelog:

When the recordSet.Update function was called to update an adLongVarChar field, the field was updated but the recordset was immediately lost. This happened with driver cursors, whether the cursor was opened in optimistic or pessimistic mode.

When the next update was called the test code would exit with the following error:

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