| Bug #35581 | Wrong column info in ODBC driver, DECIMAL is mapped to CURRENCY | ||
|---|---|---|---|
| Submitted: | 26 Mar 2008 18:23 | Modified: | 13 May 2008 14:04 |
| Reporter: | Carlos Alloatti | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.20 | OS: | Windows (XP SP2) |
| Assigned to: | Jess Balint | CPU Architecture: | Any |
| Tags: | 31968, column, CURRENCY, decimal, type, wrong | ||
[26 Mar 2008 18:23]
Carlos Alloatti
[26 Mar 2008 18:34]
Carlos Alloatti
Oh and by the way, in Visual FoxPro, a column of type CURRENCY is a column with a fixed number of decimal places (4). It is stored as 8 bytes, with a value range of - $922337203685477.5807 to $922337203685477.5807 MYSQL DECIMAL columns should translate to NUMERIC columns in Visual FoxPro, not to CURRENCY columns
[28 Mar 2008 6:56]
Tonci Grgin
> "MYSQL DECIMAL columns should translate to NUMERIC columns in VisualFoxPro, not to CURRENCY columns" Carlos, let me get one thing clear. You are trying to get us "fix" behavior of VisualFox Pro? I think MyODBC behaves correctly but VFP maps the answer according to it's internal rules (which I can not find in ODBC specs). How should we proceed? Add checkbox in configuration for every 3rd party ODBC client? Just out of curiosity, does MyODBC 5.1 work as expected?
[28 Mar 2008 6:58]
Tonci Grgin
Continued from Bug#31968.
[28 Mar 2008 13:07]
Carlos Alloatti
Tonci, let's forget about Visual FoxPro for now, and focus in the MySQL ODBC connector: "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." So here we have a change of behaviour in new versions of the driver. Also: "I 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." I'am quoting Christian Ehlscheid's comments in both cases. So here we have that the driver is returning TRUE for a column type query when asked about the SQL_COLUMN_MONEY attribute. Now, MySQL does not have a MONEY or SMALLMONEY column type, so it should not return TRUE to SQL_DESC_FIXED_PREC_SCALE (SQL_COLUMN_MONEY) unless you are doing this to fix some other odd behaviour. Maybe this is not reported more often because most ODBC clients used with MySQL don't even handle MONEY or SMALLMONEY column types and just ignore this setting? (just a wild guess) Visual FoxPro has a CURRENCY column type, that maps without problems to MONEY and SMALLMONEY columns from another popular SQL server product. DECIMAL columns from that other SQL server product map to NUMERIC columns. I'am not an expert, but common logic says to me that the ODBC driver should never say "yes this is a MONEY type column" when MySQL does not even have a MONEY type for columns. So I don´t think this is an issue of handling some quirk in FoxPro, but just reverting to the older driver versions behaviour, unless this was changed for a some other reason. I dont how 5.1 works, never used ADO.Net driver in FoxPro. I will ask around and report back.
[28 Mar 2008 15:59]
Jess Balint
Index: driver/results.c
===================================================================
--- driver/results.c (revision 1082)
+++ driver/results.c (working copy)
@@ -686,11 +686,8 @@
break;
case SQL_DESC_FIXED_PREC_SCALE:
- if (field->type == MYSQL_TYPE_DECIMAL ||
- field->type == MYSQL_TYPE_NEWDECIMAL)
- *(SQLINTEGER *)NumericAttributePtr= SQL_TRUE;
- else
- *(SQLINTEGER *)NumericAttributePtr= SQL_FALSE;
+ /* We don't support any fixed-precision type, such as MONEY */
+ *(SQLINTEGER *)NumericAttributePtr= SQL_FALSE;
break;
case SQL_DESC_LENGTH:
[28 Mar 2008 16:54]
Lawrenty Novitsky
patch approved
[29 Mar 2008 14:10]
Christian Ehlscheid
Hello folks, nice that see that a fix is already done. I can assure that this is correct behaviour since i just tested it on these DB Systems - Microsoft SQL Server, Oracle, Firebird, PostgreSQL and all return SQL_FALSE for a DECIMAL/NUMERIC column. Regards Christian
[31 Mar 2008 6:48]
Tonci Grgin
Christian, we are not paid by ruling on report and we always strive to do our best understanding the problem our users report. However that might not be possible in all situations. Further more, the one exposed to users in Bugs-db can always consult colleagues with more experience in certain areas thus improving quality of ruling. This is a complex process for us as we are matched against people using various tools/SW on daily basis while we might have never seen such tool/SW. So it's rather important to step away from actual environment producing the error and try to describe problem on more general basis like Carlos did. Thanks for your interest in MySQL.
[1 Apr 2008 5:50]
Carlos Alloatti
Tonci: >Just out of curiosity, does MyODBC 5.1 work as expected? I just tested the ODBC connector version 5.1.3 and it has the same problem. I guess that was expected seeing that a patch was approved. I'am just reporting this back because I said I would. By the way if you have available a patched ODBC binary for Windows I can test it for you. Carlos
[4 Apr 2008 6:27]
Tonci Grgin
Carlos, you can either wait for new release or build the driver yourself from sources (without GUI part) as described in manual and elsewhere. I am confident this patch will suit you as Jess always knows what he's doing.
[9 Apr 2008 4:56]
Jess Balint
Patch committed as rev 1089 and will be released in 3.51.25.
[18 Apr 2008 3:34]
Carlos Alloatti
Feedback: Both ODBC 5.1.4 and 3.51.25 versions work correctly in Visual FoxPro now, numeric columns are correctly maped. Thank you very much!
[13 May 2008 14:04]
Paul DuBois
Noted in 3.51.25, 5.1.4 changelogs. The SQLColAttribute() function returned SQL_TRUE when querying the SQL_DESC_FIXED_PREC_SCALE (SQL_COLUMN_MONEY) attribute of a DECIMAL column. Previously, the correct value of SQL_FALSE was returned; this is now again the case.
