MySQL Bugs: #21578: Error with TEXT fields through ODBC and DELPHI

Bug #21578	Error with TEXT fields through ODBC and DELPHI
Submitted:	11 Aug 2006 6:39	Modified:	31 Aug 2006 6:31
Reporter:	Albert Molina	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.12	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
There is a bug (reference 5809) that looks like this, but there is no solution.
If we have a table with a text field (TINYTEXT, TEXT, MEDIUMTEXT or LONGTEXT) in a Delphi 5 application connecting through ADO and "MySQL ODBC 3.51 Driver" it doesn't works like we need.
Problems:
- When table has no values then Delphi detects the TEXT field like a TMemoField (that's ok)
- When table has a value, or Delphi application wants to save values, then it detects the Text field like a TStringField (so that is an error) and only can save first 85 characters
- The solutions explained in #5809 don't works (put OPTION=1 or OPTION=526596 or OPTION=526597 ... in connection string)
- I think that perhaps that's a problem of MySQL ODBC 3.51 driver but I can't assure this

This is tested with Driver 3.51.12 and MySQL server 4.1.14

How to repeat:
(* that's a code in Delphi 5 with ado 

   To test this code Open Delphi 5 (with ADO components)
   and create an Application, include this unit and
   call to procedure DoShowBug.
 
   First is necessary to change CONST values:
   - Database name
   - Server IP
   - Put table name of table with Text field
   - Put the field name of Text field
   - You must test this with an empty Table and Table with values to see differences

*)

unit ShowBug;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, ADODB, StdCtrls;

CONST
  (* change this constants with valid values to test *)
  DATABASE_NAME = 'Put_your_DataBaseName';
  SERVER_IP     = 'Put_your_Server_IP';
  TABLE_NAME    = 'Put_TableName_With_Text_Field';
  TEXT_FIELDNAME= 'Put_Text_FieldName';

procedure DoShowBug;

implementation

procedure DoShowBug;
Var ADOConnection : TADOConnection;
   ADODataSet : TADOQuery;
begin
  ADOConnection := TADOConnection.Create(Nil);
  try
    ADOConnection.ConnectionString :=
       'Provider=MSDASQL.1;'+
       'Extended Properties="'+
         'DATABASE='+DATABASE_NAME+';'+ 
         'DRIVER={MySQL ODBC 3.51 Driver};'+
         'OPTION=526597;'+ (* Can put 1 or 526596 too *)
         'PORT=3306;'+
         'SERVER='+SERVER_IP+'"'; 
    (* Delphi will prompt for user name and password *)
    ADOConnection.Connected := True;
    (* Here Delphi connects (if all is ok)
      Now, we will open a simple SELECT query*)
    ADODataSet := TADOQuery.Create(Nil);
    try
      ADODataSet.Connection := ADOConnection;
      ADODataSet.SQL.Text := 'SELECT * FROM '+TABLE_NAME;
      ADODataSet.open;
        (* If table has no values, then it shows: TMemoField, but if there is
           some value, it shows TStringField *)
      ShowMessage('ClassName of TEXT field: '+ ADODataSet.fieldByName(TEXT_FIELDNAME).ClassName);
    finally
      ADODataSet.free;
    end;
  finally
    ADOConnection.Free;
  end;
end;

Hi Albert and thanks for your problem report.
Can you please do the following:
  * Attach full Delphi project showing the error to this report
  * Attach SQL script with tables and data needed to reproduce
Did you try using SourceForge OpenODBC as a Delphi/MyODBC bridge?

Dear Tonci;

Where can I find SourceForge OpenODBC ?
In Sourceforge.net there is nothing that matches "OpenODBC"... perhaps DBExpres?
Thanks.

Hi Albert.
Home page:
  http://open-dbexpress.sourceforge.net/
Project Home Page:
  https://sourceforge.net/projects/open-dbexpress/
What about my requests? I'd like to see all the files needed attached to this bug report.

Delphi 6 source

Attachment: Bug21578-D.zip (application/x-zip-compressed, text), 1.45 KiB.

Test with empty table

Attachment: B21578-2.jpg (image/jpeg, text), 6.97 KiB.

Test with full table

Attachment: B21578-3.jpg (image/jpeg, text), 7.44 KiB.

Hi Albert. I can't repeat your problem. Test case is attached to report.
MySQL 4.1.18 max-nt on Win XP SP2 (NET framework 2.0) with Delphi6 fully patched.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\myodbc1
    Driver	REG_SZ	C:\WINDOWS\system32\myodbc3.dll (3.51.12.0)
    DATABASE	REG_SZ	dbtest
    DESCRIPTION	REG_SZ	test
    OPTION	REG_SZ	264
    SERVER	REG_SZ	localhost
    UID	REG_SZ	root

[SetupAPI Log]
OS Version = 5.1.2600 Service Pack 2
Platform ID = 2 (NT)
Service Pack = 2.0
Suite = 0x0100
Product Type = 1
Architecture = x86

Module[  1] [C:\PROGRAM FILES\COMMON FILES\SYSTEM\OLE DB\MSDADC.DLL]
  Company Name:      Microsoft Corporation
  File Description:  Microsoft Data Access - OLE DB Data Conversion Stub
  Product Version:   (2.81:1117.0)
  File Version:      (2.81:1117.0)
  File Size (bytes): 4096
  File Date:         Wed Aug 04 09:56:42 2004
  Module has internal symbols.

Module[  2] [C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\MSADO15.DLL]
  Company Name:      Microsoft Corporation
  File Description:  Microsoft Data Access - ActiveX Data Objects
  Product Version:   (2.81:1117.0)
  File Version:      (2.81:1117.0)
  File Size (bytes): 536576
  File Date:         Wed Aug 04 09:56:42 2004
  Module has internal symbols.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.18-nt-max

mysql> use test;
Database changed
mysql> create table b21578e (ID Int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> TFld MEDIUMTEXT);
Query OK, 0 rows affected (0.03 sec)

mysql> create table b21578f (ID Int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> TFld MEDIUMTEXT);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into b21578f VALUES (NULL,"01234567890123456789ABCDEFGHIJKLMNOPRST
UVZabcdefghijklmnoprstuvz0123456789012345678901234567890123456789ABCDEFGHIJKLMNO
PRSTUVZabcdefghijklmnoprstuvz0123456789");
Query OK, 1 row affected (0.00 sec)

mysql> select * from b21578f\G
*************************** 1. row ***************************
  ID: 1
TFld: 01234567890123456789ABCDEFGHIJKLMNOPRSTUVZabcdefghijklmnoprstuvz0123456789
012345678901234567890123456789ABCDEFGHIJKLMNOPRSTUVZabcdefghijklmnoprstuvz012345
6789
1 row in set (0.00 sec)

mysql> select LENGTH(TFld) from b21578f;
+--------------+
| LENGTH(TFld) |
+--------------+
|          158 |
+--------------+
1 row in set (0.00 sec)

mysql> quit
Bye

Dear Toncy;

Now I can't test it with Delphi 6, I'm using Delphi 5
Can you test this with Delphi 5 ?
Thanks.

Albert, it is impossible for me to test on Delphi 5... You can try upgrading your OS and/or MySQL server and see if problem goes away. If not, you will have to upgrade Delphi.

Dear Toncy;

I have tested this example with Delphi 7 and it allready works bad, so i think that Delphi is not the problem.

I use the same ODBC client driver to conect: myodbc3.dll (3.51.12.0)

I have seen you used a MySQL server with version 4.1.18 and my MySQL server is version 4.1.14, so I will try to use a newer version...

I will say you something... and I hope it will be good news!

Albert

Albert, according to our SOP I am obliged to use latest server. Anyway, I can't change my ruling on this since I made test case and checked. Hope you'll find what's wrong but I'm sure it isn't MyODBC.
Thanks for your interest in MySQL.