| Bug #31968 | RETURN WRONG FIELD TYPE | ||
|---|---|---|---|
| Submitted: | 30 Oct 2007 21:08 | Modified: | 28 Mar 2008 6:58 |
| Reporter: | Joseph Lui | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 3.51.21 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[31 Oct 2007 5:41]
Susanne Ebrecht
Hi Joseph, many thanks for reporting a bug. What exactly do you mean with currency. Can you give us an example? Also we need the trace. Kind Regards, Susanne
[31 Oct 2007 15:40]
Joseph Lui
In VFP there are integer field, numeric field and currency field. integer field is 4 bytes; numeric field is 8 bytes in memory - 1 to 20 bytes in table; it can be integer or decimal. currency field is 8 bytes use Monetary amounts. the following link explain VFP data type in more detail http://msdn2.microsoft.com/en-us/library/ww305zh2(VS.80).aspx ie: CREATE TABLE `table1` ( `custname` VARCHAR(40) NOT NULL DEFAULT '', `height` NUMERIC(6,3) NOT NULL DEFAULT 0 ) ENGINE = MYISAM; in VFP when it query from the MYSQL server using the 3.51.21 connector, the result change the height from numeric data field into currency data field type.
[31 Oct 2007 17:34]
Joseph Lui
I did more investigation on it. it start happening like this since 3.51.18 the older version return the correct numeric fields.
[7 Nov 2007 13:59]
Susanne Ebrecht
Many thanks. Please, can you add your trace file here? Susanne
[8 Dec 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[12 Mar 2008 9:03]
Adrian van der Merwe
I have the same problem. MySQL 5.0 ODBC 3.51.23 - Doesn't work ODBC 3.51.14 - works Using Foxpro 9.0 SP1 Unfortunately cannot return to using ODBC 3.51.14 as we need the character set support.
[21 Mar 2008 14:23]
Christian Ehlscheid
The cause of the problem is the "SQLColAttribute" function inside the driver. In the new driver versions the SQLColAttribute function returns SQL_TRUE when querying the "SQL_DESC_FIXED_PREC_SCALE (SQL_COLUMN_MONEY)" attribute of a DECIMAL column, in the old versions SQL_FALSE was returned. The problem with FoxPro is that it maps any column that returns SQL_TRUE for the SQL_DESC_FIXED_PREC_SCALE (aka SQL_COLUMN_MONEY) attribute to the "Currency (Y)" datatype. If don't know if this is actually a bug, but i don't know any other ODBC driver that returns SQL_TRUE for a column with the datatye NUMERIC/DECIMAL, so it's definitly uncommon behaviour.
[21 Mar 2008 14:36]
Christian Ehlscheid
For all those using Visual FoxPro:
There are currently two "workarounds" available:
1. Use the CursorAdapter class instead of SQLEXEC and specify the "CursorSchema" property to manually map the column to the correct type.
2. I've written an extended replacement function for the SQLEXEC function called SQLEXECEx inside my open source FLL @ www.vfp2c.com. It allows one to specify the column to datatype mapping too. The limitation of SQLEXECEx is that one cannot make the resulting cursor updatable with CURSORSETPROP("UpdatableFieldList", ...), so it's usable only for readonly datasets.
[26 Mar 2008 18:37]
Carlos Alloatti
Since I cannot reopen this bug report, I have reposted here: http://bugs.mysql.com/bug.php?id=35581
[28 Mar 2008 6:58]
Tonci Grgin
Yes Carlos, only original reporter can reopen the report. We will continue this discussion in Bug#35581. Closing this report now.
Description: I have MySql 5.0.18 server engine running and use the 3.51.12 connector for a year and it have no problem. my application is developed in VFP 8.0. everything works great untill one of my client update the connector to 3.51.21, the program start give weird issues. the new version connector return currency for all decimal fields. is there a way I can make it stay as I declare in the table? How to repeat: mysql 5.x myodbc 3.51.12 during developing. foxpro 8.0 use the odbc to connect to the server db1 -> table1 have fields that are in decimal. myconn = Sqlstringconnect("DRIVER={MySQL ODBC 3.51 Driver};SERVER=&loSite;PORT=3306;DATABASE=&connMyDB;USER=&loUser;PASSWORD=&loPswd;OPTION=3;") = SQLEXEC(myconn, "call store_procedure1('db1', 'select * from table1')", "tmpoutput") works --> fine now upgrage the myodbc to 3.51.21 the decimal fields return as currency. Suggested fix: please help.