MySQL Bugs: #4341: Recordset cache not updated after updating when using text field

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

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.

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".

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.