| 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: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 5.1 | OS: | Windows (Windows XP) |
| Assigned to: | Jess Balint | CPU Architecture: | Any |
[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 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.
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