Bug #4341 Recordset cache not updated after updating when using text field
Submitted: 30 Jun 2004 12:11 Modified: 21 Jul 2004 23:27
Reporter: Jan Bouwhuis Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[30 Jun 2004 12:11] Jan Bouwhuis
Description:
When a ADODB.recordset `text` field is updated, and after updating the field is re-read, the previous value is returned instead of the updated value when a ServerSide cursor type is used. The data on the server is updated correctly. When the recordset is re-opened the correct data is returned.
The problem is solved when using a ClientSite CursorType (objSQL.CursorLocation = 3).
I found this problem only in combination with text fields, varchar fields didn't give any problem.

MySQL version: mysqld-4.1.2-alpha.
Using Visual Studion .NET 2002 with .NET Framework 1.0

Table def:
# Host: localhost
# Database: dbname
# Table: 'tblaanhef'
# 
CREATE TABLE `tbltest` (
  `KeyField` int(11) NOT NULL auto_increment,
  `NameField` text NOT NULL default '',
  PRIMARY KEY  (`KeyField`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
INSERT INTO `tbltest` (NameField) VALUES ('InitValue');

How to repeat:
Dim objSQL As ADODB.Connection = New ADODB.Connection()
objSQL.ConnectionString = "driver={MySQL ODBC 3.51 Driver};server=localhost;database=dbname;UID=root;pwd=****;PORT=3306;OPTION=3;STMT=;old_passwords=1;"
objSQL.Open()
'First set an initial value
objSQL.Execute("UPDATE tblTest SET NameField = 'Old Value' WHERE KeyField = 1")
'Now update using ADODB (The key field must be included see Bug #647
'in http://bugs.mysql.com/bug.php?id=647)
Dim MyRs As ADODB.RecordSet = New ADODB.RecordSet()
Dim sValue As String
Dim sqlstr As String = _
  "SELECT KeyField, NameField FROM  tblTest " & _
  "WHERE KeyField = 1"
MyRs.Open(sqlstr, objSQL, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockPessimistic)
If Not MyRs.EOF Then
  MyRs("NameField").Value = "New Value"
  MyRs.Update()
  sValue = MyRs("NameField").Value
  'Problem: sValue holds the old value! though the server is updated.
End If
MyRs.Close
objSQL.Close()

Suggested fix:
When using Server side cursors 'text-field' should be fetched properly,
also after an update.

A work around is using client side cursors
objSQL.CursorLocation = 3.
Closing the record set and reopening it also works.
[21 Jul 2004 23:25] Timothy Smith
Thanks for your bug report.  Version 3.51 of MyODBC was not written with server-side cursor support fully in mind, and it won't be updated to fully support server-side cursors or other MySQL 4.1 features.

Instead, work is progressing on version 3.53 of Connector/ODBC, which will comply fully with the ODBC spec and will support all features of MySQL 4.1 and above.  It is in development now, and will be available "Real Soon Now".
[21 Jul 2004 23:27] Timothy Smith
By the way, full support for server-side cursors is not available until version 5.0 of MySQL.  Server-side prepared statements is available in MySQL 4.1.  Version 5.0 of MySQL is not even in alpha yet (a pre-release is available on www.mysql.com).  We are hoping to have a beta release of MySQL 5.0 in the first half of 2005.