"Load data local infile" problem

[pece]

Hi,

I am trying to use:

LOAD DATA LOCAL INFILE "c:/temp_data.txt" INTO TABLE security2

and I get "The command is not allowed in this version of MySql.

I can execute that sql from other software that I use for mysql administration, but not from mine that it's using zeos.
it probably has something to do with local-infile client option, but I can't figure it out where to set that on.

Please advice.

[zippo]

Try to skip the LOCAL word. Probably you're using MySQL 3.23.x, that still hasn't this feature implemented.

[pece]

The server si version 4.
In fact same query works from a few other GUI applications against the same server.
You can get that error either from a server setting or a client setting. While I do not get the error from any other software I'm executing it from, I guess it's a flag zeos or maybe libmysql40.dll that I'm using.

[mdaems]

Hi, in ZDbcMysql.pas you should try to add _CLIENT_LOCAL_FILES to _CLIENT_CONNECT_WITH_DB in the following code.

if FPlainDriver.RealConnect(FHandle, PChar(HostName), PChar(User),
PChar(Password), PChar(Database), Port, nil,
_CLIENT_CONNECT_WITH_DB) = nil then

Don't shoot me if it doesn't work. I didn't get the time to try it myself yet.

Mark

[mdaems]

Hi,

I have some bad news. I did try to do this load data local infile on mysql 5.1 and it worked without any problems, I didn't have to change the code.

Did you try the change? Just in case the default behaviour of the mysql library changed. Also, did you try to use the libmysql from your server distribution instead of the ZEOS version? Just rename it to libmysql40.dll and put it next to your executable.

So, I hope it will be sufficient for you , but for me it al works.
Mark

[onaikul]

Hi just a question i'm tryng to do the same but my question es so simple with wich component do you execute the load data sentence ?.....i'm tryng with zQuery but i got a "SQL Error: Row 1 doesn't contain data for all columns"

SET NAMES latin1;
LOAD DATA LOCAL INFILE "c:\\Documents and Settings\\Administrador\\Escritorio\\especialidad.txt" REPLACE
INTO TABLE especialidad
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
SET da_timestamp = CURRENT_TIMESTAMP;


is this a problem of the component i am using or is the sentence?...

[mdaems]

This looks like a problem with your datafile. Mysql is telling you it has more columns in the table than you provided in the input file. Did you try to execute this exact same statement from the mysql command line client?
If yes : this might be a problem with the ';' separator that is also used as a statement delimiter by zeoslib. To make sure it's passed to the dll correctly you could add a TZSQLMonitor component to record the exact statement sent by zeoslib.

Mark

[onaikul]

it was than i dont set all fields in my txt file, but the rare thing is that executing that sentence directly from the mysql console works correctly, i tried with the monitor and the sentences is fine, also try with other field separator ( '|' ) and the same thing, but no problem i completed the fields in the txt and it works fine....

Thanks you very much