MySQL Bugs: #5809: Varchar detect as Memo if NULL or Empty

Bug #5809	Varchar detect as Memo if NULL or Empty
Submitted:	29 Sep 2004 19:08	Modified:	15 Dec 2005 16:04
Reporter:	Mathieu Tremblay	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	4.1.5 gamma-max	OS:	Windows (windows XP)
Assigned to:	Sergey Vlasenko	CPU Architecture:	Any

Description:
I'm using Delphi 7 with MyODBC 3.51 and MySQL 4.1.5 gamma-max

When I query MySQL, if the the column type in the query is a varchar and the value is null or empty string.  The delphi TEdit component detect the field as a Memo type.

example

CREATE Table Client (
Name varchar(30) default NULL
)ENGINE=InnoDb DEFAULT CHARSET=latin1;

SELECT Name FROM Client;

if Name is empty string or null then the return type is a Memo. 

If I use the 4.1.4 gamma-max there is no problem with this kind of column type.

How to repeat:
Use MySQL 4.1.5 gamma-max

Just put the options: don't optimize columns width in MyODBC source configuration

Thats works...

Why ??? don'y know

I verified this with another Client interfaces, like Access which looks
the Varchar as Text file. I will test again when Connector/ODBC
will be released compiled against the 4.1.XX client library.

Regarding your question, below the explanation from Readme.win:

The Option argument is used to tell MyODBC that the client isn't 100% ODBC
  compliant. The following options are listed in the same order as they
  appear in the MyODBC connect screen:

  1   The client can't handle that MyODBC returns the real width of a column.

I also encountered this problem in Delphi & VB with MyODBC
3.51.11-2, MDAC 2.8, and MySQL 4.1.12a. I found out that the problem is in the data type returned from MyODBC which is 201 (adLongVarChar) instead of 200 (adVarChar) when the column is null/empty. In Delphi, field data type 201 (adLongVarChar) will be declared as TMemoField (a sub-class of TBlobField). So when I first create a table w/ no rows, Delphi defined these as TMemoField and it compiles w/ no problems but when the tables have already rows/values then Delphi raises an exception. 

Any updated fix in this bug? For now, I can't use MySQL in my Delphi programs using dbGo (ODBC).

Thanks...

I just ran across a similar problem with a tinytext column. 
I am connecting to a v4.0.18 myisam table on a Win2000 server which has a 
not null, tinytext column.  I connect to the table using MyODBC 3.51.11.0 and 
am viewing using Crystal reports 11.0.0.895.  The tinytext column is being 
identified as type Memo and so can't be used in joins.

I tried the "Don't optimize column width" workaround mentioned above, but that 
has no effect.

Some code changed in this area. This needs to be reverified using c/odbc v3.51.12. Assigned to sergey to reverify and, in case where it is still a problem, to fix.

VB test case

Attachment: 05809.zip (application/x-zip-compressed, text), 1.75 KiB.

As test case shows the problem is not reproduced with MyODBC 3.51.12. 
Was tested with 4.1.17 &  4.0.27

I have a similar issue with text field that have Nulls being converted into Memo fields. Am using MySQL Server 5 / 3.51 ODBC driver / Crystal Reports XI.
Any help will be greatly appreciated.