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

Bug #9111	MyODBC returns wrong character field widths in Visual Foxpro
Submitted:	11 Mar 2005 0:18	Modified:	24 Mar 2005 3:22
Reporter:	Tom Gadue
Status:	Closed
Category:	Connector/ODBC	Severity:	S3 (Non-critical)
Version:	MyODBC 3.51.11	OS:	Microsoft Windows (XP)
Assigned to:		Target Version:

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.