MySQL Bugs: #9111: MyODBC returns wrong character field widths in Visual Foxpro

Bug #9111	MyODBC returns wrong character field widths in Visual Foxpro
Submitted:	10 Mar 2005 23:18	Modified:	24 Mar 2005 2:22
Reporter:	Tom Gadue	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	MyODBC 3.51.11	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
In Visual Foxpro when you use ODBC to connect to a MySQL database the table structure that is returned does not match what is on the server.  For example, if you have a field "firstname" that is VARCHAR(20) and you issue a SELECT via MyODBC the results set will be put into a temporary Foxpro table (cursor) and the structure of that table will have "firstname" width of the longest length value returned (e.g.. if "FRED" was the only value returned the width of Foxpro firstname field would be 4 when it should be 20 and hence you can't enter any data longer than 4 characters).  When using MS SQL Server with the same data, the correct width is returned.

How to repeat:
I have provided a Visual Foxpro 8 project and all that you need to do to reproduce is the run the supplied SQL script (table_structure_bug.sql) which sets up the test database and one table with two fields and one record inserted.  Then modify the Click event of the button in the form (frmodbctest.scx) to include your connection parameters to MySQL.  I was using MyODBC 3.51.11.  Build an exe and then run it, click the button and a text file will be output (tblstructbug.txt) displaying the structure of the table which will not match that of MySQL.  You should get output similiar to this (note that the width is only 4 and not 20 as specified in the sql script):

Structure for table:    C:\DOCUME~1\USER\LOCALS~1\TEMP\8P1S0007.TMP
Number of data records: 1       
Date of last update:    11/10/1617
Code Page: 1252    
Field  Field Name      Type          Width    
1  FIRSTNAME       Character          4    
2  AGE                 Integer             4 
** Total **                                 9

Visual Foxpro 8 files and SQL script

Attachment: odbcbug2.zip (application/x-zip-compressed, text), 5.18 KiB.

This has been tested with Oracle as well as MS SQL Server.  Using their ODBC drivers this problem does not exist, i.e. the correct width of 20 is returned even if the longest field value is less than 20.  Because of this I am lead to believe that this is a problem with MyODBC 3.51.11

This Bug Report can be closed, there is a MyODBC option that can be set to fix this problem.

Thank you for the update.