Bug #11276 ODBC Connector returns VarChar as LongVarChar type
Submitted: 13 Jun 2005 4:24 Modified: 13 Jun 2005 6:18
Reporter: Edrick Duero Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.11-2 OS:Windows (Windows XP-SP2)
Assigned to: CPU Architecture:Any

[13 Jun 2005 4:24] Edrick Duero
Description:
I'm using Delphi 2005 and dbGo (ODBC Connectivity) with MyODBC 3.51.11-2, MDAC 2.8, and MySQL 4.1.12a

When I access a table with VarChar column, MyODBC returns a DataTypeEnum of 201(adLongVarChar) instead of type 200(adVarChar). This makes Delphi map this field as TMemoField (a decendant of TBlobField) instead of TStringField.

I also tried this using VB but still MyODBC return a data type of 201(adLongVarChar) instead of data type 200 (adVarChar).

How to repeat:
Create a Delphi VCL Acpplication.

In Form1, drop a TADOConnection and TADODataSet

Build the connection string of ADOConnection1
Set ADOConnection1.Active = True in property inspector

Assign the ADODataSet1.Connection to ADOConnection1
Build the ADODataSet1.ConnectionText = 'select * from mytable'
Set ADODataSet1.Active = True
Right click the ADODataSet1 and select Fields Editor
In Fields Editor, right click and select Add All fields

Check the declared field type for varchar columns in MySQL table, it is incorrectly detected as TMemoField instead of TStringField.

Suggested fix:
Please return a data type 200 (adVarChar) for MySQL varchar columns not data type 201 (adLongVarChar)
[13 Jun 2005 6:18] Vasily Kishkin
Sorry, I was not able to reproduce this bug. I attached screen copy where field "c" (varchar) is showed as TStringField. The definition of table is follow:

mysql> describe t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c     | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
[13 Jun 2005 6:19] Vasily Kishkin
screen copy

Attachment: screen.gif (image/gif, text), 20.35 KiB.

[14 Jun 2005 5:56] Edrick Duero
Vasily, I tried to find the cause and I found out that this problem occures in null  columns.

Here is my test script:
---------------------------------------------
mysql> create table t1(
    ->   c1 varchar(50),
    ->   c2 varchar(50)
    -> ) type=innodb;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> insert into t1(c1) values('test');
Query OK, 1 row affected (0.00 sec)

mysql>
------------------------------------------------

The 'c1' column return data type 200 (adVarChar) coz it has a value but the 'c2' column return 201 (adLongVarChar) and not 200 (adVarChar) this makes delphi to declare this a TMemoField instead of TStringField.

I also test this in VB (Recordset.Fields[Index].Type) and still returns the same data type value

Thanks...
[15 Jul 2005 15:25] Edrick Duero
Just an update for this report.

It occurs only when you set the connection option = 0. But when you set the option = 3 (don't optimize columns width) it works fine.

sample connection string:
'Provider=MSDASQL.1;User Name=root;Password=password;Extended Properties="driver={MySQL ODBC 3.51};DATABASE=test;OPTION=0;PORT=0;SERVER=localhost"'