Bug #42385 Lose text value after the first read
Submitted: 27 Jan 2009 17:49 Modified: 7 Oct 2010 4:38
Reporter: Stefano Zilli Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.1.5 OS:Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: lose value text read

[27 Jan 2009 17:49] Stefano Zilli
Description:
I noticed a strnage behavior when reading text field with this version of ODBC.
If I make a select query with a text field as one of its results, when I read that field I get the value of it only the first time, all the next attempt on read it will result on a blank/null string.
If I use an old version of odbc (like 3.5.12) the same code is ok and I can access that field several time.

The only workaround is to save that value on a temporary variable as the first access on it.

How to repeat:
Create a test database like this :

CREATE DATABASE `test_bug` ;

CREATE TABLE `table1` (
  `ID` int(11) NOT NULL auto_increment,
  `fdText` text,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO `test_bug`.`table1` (`ID` ,`fdText`) VALUES 
(NULL , 'This is a text string'), 
(NULL , 'This is another text string');

Create an asp page like this :

<%
  strDB="DRIVER=MySQL ODBC 5.1 Driver;option=268435456;database=test_bug;UID=root;PWD="
  Set Conn_DB = Server.CreateObject("ADODB.Connection")
  Conn_DB.Open strDB
  set rs=conn_db.Execute("select fdText from table1 order by id desc")
  response.write "First read : " & rs(0) & "<br>"
  response.write "Second read : " & rs(0)
%>
[28 Jan 2009 7:23] Tonci Grgin
Stefano, this is, in fact, duplicate of Bug#16866 & Bug#24132 and many more but Bogdan decided to give it a one more try.
[28 Jan 2009 8:21] Stefano Zilli
Thank you for the answer Tonci.

On the other "bugs" I read that this is a behavior compliant to the ODBC standard for the Text fields.
It could be usefull to add an option on the ODBC driver to take in the buffer these fields value, just for backward compatibility with older ODBC driver version. Otherwise when you do an upgrade of driver you will get "strange" behavior on your apps...
[28 Jan 2009 8:32] Tonci Grgin
Stefano, problem with non-compliant behavior is in that it *will* sneak up on you and break driver sooner or later. And then you have no means of justifying such error in code... Anyway, Bogdan is checking if this behavior can be implemented in ODBC-compliant way as feature is interesting.
As for breaking "old" behavior, I'm all for it if new code complies to standard better.
[28 Jan 2009 8:42] Stefano Zilli
You are right Tonci :)
And it's always better to stay as close to the standard as possible. But if for a long time the driver as a behavior and a lot of program are based on it it could be usefull to have a deprecated option on the new one for backward compatibility and give the programmer the time to change the code while stay up with the version :)
Maybe it could be a nice thing to add this different behavior on the FAQ of the ODBC...
[29 Jan 2009 22:05] Jess Balint
Stefano, This seems to be working ok for me with both a client and server cursor. Can you try one of our snapshot builds?

http://downloads.mysql.com/snapshots.php

The only thing that I can think that would have fixed this is bug#26950.

Look forward to hearing your results.
Jess
[29 Jan 2009 22:29] Stefano Zilli
Hi Jess,

what snapshot version have you tried ?

Stefano
[29 Jan 2009 23:00] Jess Balint
The ODBC snapshot from: http://downloads.mysql.com/snapshots.php
[29 Jan 2009 23:06] Stefano Zilli
I will make a try tomorrow...
[2 Feb 2009 10:16] Stefano Zilli
Hi Jess,

I tryed this build:
mysql-connector-odbc-noinstall-5.1.6r823_20090115_2050-win32.zip
with MySQL Server v5.0.22 but the "problem" still persist.
Can you tell me the connection string you used ?

Stefano
[2 Feb 2009 10:21] Tonci Grgin
Stefano, please check "Limit column size to 32bit range" as MS can't handle anything bigger. Also look for 1+2 and "Treat BIGINT as int" (same reason).
[2 Feb 2009 10:34] Stefano Zilli
Hi Tonci,

for the bigint problem, for exemple when you make a "select count(*) from ..", I use the workaround of the asp type cast ( cLng() ).
Anyway I tryed with this option :

  Option_ = 0
  Option_ = Option_ + 1 ' FLAG_FIELD_LENGTH
  Option_ = Option_ + 2 ' FLAG_FOUND_ROWS
  Option_ = Option_ + 16384 ' FLAG_NO_BIGINT
  Option_ = Option_ + 134217728 ' FLAG_COLUMN_SIZE_S32 
  Option_ = Option_ + 268435456 ' FLAG_NO_BINARY_RESULT  

and no change in the results. After the first read of the text field its value is resetted to null.

Stefano
[18 May 2009 18:44] Jess Balint
Current workaround (for ASP only, works in ADO/VB) is to use a client side cursor:

Change:
  set rs=conn_db.Execute("select fdText from table1 order by id desc")

To:
  set rs = Server.CreateObject("ADODB.recordset")
  rs.cursorlocation = 3 'client
  rs.open "select fdText from t_bug42385 order by id desc", Conn_DB
[18 May 2009 20:34] Jess Balint
Bug#44831 marked as a duplicate.
[24 Feb 2010 14:03] Tonci Grgin
Bogdan, this sure appears to be a duplicate of Bug#16866. Please close if you see fit.
[7 Oct 2010 4:38] Bogdan Degtyariov
Closed as duplicate of http://bugs.mysql.com/bug.php?id=16866