MySQL Bugs: #15207: Flawed conversion for 5.0 decimal data type

Bug #15207	Flawed conversion for 5.0 decimal data type
Submitted:	23 Nov 2005 22:47	Modified:	5 Feb 2007 13:23
Reporter:	Tom Price	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.10	OS:	Windows (Win2K)
Assigned to:		CPU Architecture:	Any

Description:
I'm using Visual Foxpro 7 against a MySQL back end with MySQL Connector/ODBC. The database has columns defined as decimal(8,3). I'm using MyISAM tables.

I had initially expected a conversion to "numeric" which can have up to 19 digit of precision

In MySQL 4.x, decimal columns are returned as Foxpro type "currency". The currency data type conversion with 4.x had caused minor precision issues since Foxpro's currency data type only allows for a max of 4 positions to the right of decimal. .

With MySQL 5.0.15, these return as type "character". As expected, this creates big problems when using these values in calculations.

More information on VFP 7 type conversions here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7help/html/dgcondataty...

FWIW, I have connection option "change bigint columns to int" enabled.

Here is a portion of the ODBC trace using 5.0.15. Notice the ODBC_C_CHAR being returned...

vfp7            7a8-1b8	ENTER SQLFetch 
		HSTMT               02201FD0

vfp7            7a8-1b8	EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
		HSTMT               02201FD0

vfp7            7a8-1b8	ENTER SQLGetData 
		HSTMT               02201FD0
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x011F0059 
		SQLLEN                    11
		SQLLEN *            0x0012F684

vfp7            7a8-1b8	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               02201FD0
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x011F0059 [       5] "0.001"
		SQLLEN                    11
		SQLLEN *            0x0012F684 (5)

How to repeat:
1. Create a table containing at least one decimal field.
   CREATE TABLE vct (x decimal(8,3) NOT NULL default '0.000') TYPE=MyISAM;
2. Add a record to the table.
3. Connect to the database from Visual Foxpro.
    nConn=SQLConnect() or nConn=SQLSTRINGCONNECT(....)
4. Query the table using Visual Foxpro.
    rc=SQLEXEC(nConn,"select * from mytable",")
5. Display the type of the result.
    MESSAGEBOX(TYPE("sqlresult.x")) C=character,Y=currency,N=numeric

Suggested fix:
I have some concern that this issue might be the database rather than ODBC because of the difference in behavior between 4.x and 5.0. 

It might make more sense for this to come back as either SQL_FLOAT or SQL_DOUBLE.

Thanks for your bug report.  I was able to reproduce this using 3.51.12

For what it is worth,  if you use sum() and avg() functions on a decimal type column, in foxpro the values are returned as character values - in 5.0.15 while 4.1.15 the same is returned as currency.

It would be better to return them as SQL_FLOAT values with whatever level of precision that the user has defined.

Hi ,

I am using VFP 9 with MySql 4.1 and ODBC 3.51... The currency return value is big problem for me also. It is a very big problem! 
I suggest build free data type conversion possibility into the new ODBC connector version.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/