Apache OpenOffice (AOO) Bugzilla – Issue 6600
MySQL: inserting into numerical fields fails with german locale
Last modified: 2006-05-31 14:29:06 UTC
Hello I tried to setup an ODBC connection from OOo (German version!) to MySQL using the ODBC access on my SuSE 7.3 system. For this I installed the latest MyODBC (NOT the 3.51er version) and unixODBC drivers. I created a database connection in the ini-files and selected this connection in the data source area of OOo. The installation in general should be ok, as the access via ISQL (unixODBC's command line interface) works without problems. Connecting to the database from OOo and watching the records does also work fine without any problems. Adding and changing records does work either - as long as no numerical fields are involved - and that's the problem. When I try to insert a record there occures the error "Column count does not match value count at row 1". When I use OOo's direct SQL-window and put the raw INSERT sql-command in it does work!! To find out, what could be wrong there I switched on MySQL's log - and there was the mistake! Look at this log-file entry: 020723 23:38:18          3 Query    INSERT INTO `temp5` (`nr`,`te`) VALUES ( 8,77849999999999930e+00,'8ret') As you can see there is a (German) decimal comma used instead of the (English) decimal point needed in SQL-code. For this reason MySQL of course interprets the values as 3 instead of 2 and "Column count does not match value count". The log entries of my raw SQL-commands are correct, so that it seems to be OOo generating that wrong code. Changing the language and format (decimal point now) of the numerical fields didn't help either as it only affected the way numbers are displayed but not the one they are stored. I contacted the author of this (http://www.unixODBC.org/doc/OOoMySQL.pdf) script and asked him if he had that problem, too. But he didn't - and he used the English OOo-version. To find out more I tried the same installation under WIN98. But the problem didn't occure there. So it seems to be a bug of the German version of OOo for Linux. What can I do?
confirming. This issue is known, the last knowledge I have is that it's a driver problem. Ocke, please follow up on this. We should contact the driver guys, and find a solution for this - it happens way to often out there .... Frank
changing the title from "OpenOffice and MySQL via ODBC" to "MySQL: inserting into numerical fields fails with german locale" This may allow more people to find this issue.
I had exactly the same problem, now using the Dutch version (also StarOffice 6.0 has the same problem). The error message I get is somewhat different. As soon as I enter a number in the field, even without the ",", so as integer from the datasource explorer (F4-key). The error messeage is: [unixODBC][TCX][MyODBC]You have an error in your SQL syntax near '000000000000000e+00 WHERE 'ISBN' is '123'' at line 1 What is the workaround ?
Some additional information: I've switched the language setting to US-English, using YAST2 (I'm using a SuSE distro). Now it is OK! I don't really understand what is happening, as KDE is still talking Dutch to me, also in /etc/rc.config the variable DEFAULT_LANGUAGE="dutch".
Sorry, forgot to attach this information: I've the same problem in StarOffice 6.0 and OOo 1.0, note that StarOffice is an English version (there is no Dutch). Relating my last two inputs, I doubt StarOffice/OOo is to blame. Although I'm not sure here.
<< I've updated my Email address (globally), this is added to see, if I the email address is now correct; I might have missed Email on this issue >>
Luc, yes, we think this is an MySQL driver problem. Can't give you the details at the moment .... Ocke investigated this some time ago (before this Issuezilla bug existed). Ocke, can you please (for archiving purposes at the moment :) explain here what's the problem? Thanks. We surely have to follow up on this, even if it's a driver problem at the moment - I think the current status is really unsatisfying. Frank
Hi, I encountered that MySQL only ask for english locale and doesn't work if this locale is not available. As I remember they only ask for LC_ENGLISH or something like that. Sorry that I could say more about, I don't know if there exists already a bug by MySQL about this. From our side we cann't fix the bug. All parameters we submit to the driver are set via API calls. Our insert statement looks like this "insert into Table1 (?,?,?)" so that we supply the orignal value to the ODBC driver of MySQL. The conversion back into an ACSII string is done by the driver itself. So the best way to fix this bug, 1. submit a bug to MySQL, 2. install an englsih locale :-( Hope this helps. Best regards, Ocke
Closing.
After considering the information given in this and the duplicate bug entries for this problem, I thought up a very quick and easy hack to solve the problem until there is a better one: 0. Start OOo. 1. Change the locale in the "Tools->Options->Language Settings->Language" dialog for OOo. NOTE: At this point, OOo also changes the currency entry. This can be set back to whatever you like as it does not affect the course of the solution. 2. Close OOo and start it anew, but this time by prefixing "LC_ALL " Example: "LC_ALL=C OpenOffice.org/soffice &" in one line. 3. WORKS! Since so far I only tried to use Integers, I would not know what adjustments you would have to make in order to enter floating point values while using this hack. It seems clear that while now integers are passed correctly to MySQL through MyODBC (2.50) and unixODBC (2.23, I think), floating point values would still have to be entered with a dot as a decimal point delimiter. For manual entries, the hand can be trained. Lists can be changed with sed. I shall leave the other cases to more competent putterers.
*** Issue 18632 has been marked as a duplicate of this issue. ***
change subcomponent to 'none'