Home
>
WinDev Forum
>
Topic
MySQL 5.0 Problem
Posted by Tom Thorward
|
Tom Thorward
MySQL 5.0 Problem April 05, 2008 05:52AM |
I recently completed a financial reporting application for a major client (the U.S. Army) using Windev 11 and MySQL 5.0. Part of the process involves updating several large databases using data from external text files. The SQL command "LOAD DATA INFILE 'Data.txt' REPLACE INTO TABLE actualcurrent..." is used to update the MySQL tables.
The process worked fine in MySQL 4.1 running on a Windows 2003 server. It also works fine when using a SUSE Enterprise 10 server and MySQL 5.0. However, the combination of Windev, MySQL 5.0 and Windows 2003, XP, whatever... does not work. The datasets are dropped and recreated as required, but the data fails to load into the file. Strangely, when the SQL "LOAD" command is captured verbatim from the program, and run independently using SQLyog or other utilities, it works flawlessly. Somehow Windev is not allowing the "LOAD" command to work. I am running the query using "hQueryWithoutCorrection" to keep the SQL code intact. I also have the latest MySQL connector and the libmysql.dll from the 5.0 version. I have checked the user rights in the MySQL data directory and they seem OK. The problem occurs on several different machines. What could be the cause of this problem? Unfortunately, the client has one mission critical location where a Linux server is not an option and also they will not revert to MySQL 4.1 for security reasons. Other than that the deployment has gone quite well.
The process worked fine in MySQL 4.1 running on a Windows 2003 server. It also works fine when using a SUSE Enterprise 10 server and MySQL 5.0. However, the combination of Windev, MySQL 5.0 and Windows 2003, XP, whatever... does not work. The datasets are dropped and recreated as required, but the data fails to load into the file. Strangely, when the SQL "LOAD" command is captured verbatim from the program, and run independently using SQLyog or other utilities, it works flawlessly. Somehow Windev is not allowing the "LOAD" command to work. I am running the query using "hQueryWithoutCorrection" to keep the SQL code intact. I also have the latest MySQL connector and the libmysql.dll from the 5.0 version. I have checked the user rights in the MySQL data directory and they seem OK. The problem occurs on several different machines. What could be the cause of this problem? Unfortunately, the client has one mission critical location where a Linux server is not an option and also they will not revert to MySQL 4.1 for security reasons. Other than that the deployment has gone quite well.
|
steve erts
Re: MySQL 5.0 Problem April 06, 2008 04:39AM |
|
Tom Thorward
Re: MySQL 5.0 Problem April 07, 2008 03:30PM |
|
Tom Thorward
Re: MySQL 5.0 Problem April 10, 2008 04:20PM |
Just for the record, MySQL version 5.0.50sp1a-enterprise-gpl-nt inserts the following into the my.ini configuration file for Windows systems:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
The problem is the "STRICT_TRANS_TABLES" clause. This caused the transactions to fail in Windev, but not in, for example SQLyog. The solution was to eliminate this clause from the configuration, and then Windev performed as it should.
The "local-infile=1" statement also needs to be added to the my.ini file to enable the "LOCAL" parameter of the "LOAD DATA" SQL command, if data is to be transferred from a Windev client application.
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
The problem is the "STRICT_TRANS_TABLES" clause. This caused the transactions to fail in Windev, but not in, for example SQLyog. The solution was to eliminate this clause from the configuration, and then Windev performed as it should.
The "local-infile=1" statement also needs to be added to the my.ini file to enable the "LOCAL" parameter of the "LOAD DATA" SQL command, if data is to be transferred from a Windev client application.