MySql Load data local infile error

[life2122]

Hi,

    I'm have a problem with load txt file into mysql
   

Code: [Select]


SQLTransaction1.DataBase:= MySQL56Connection1;
MySQL56Connection1.DatabaseName:= 'test';
MySQL56Connection1.HostName:= '192.168.0.205';
MySQL56Connection1.UserName:= 'testusr';
MySQL56Connection1.Password:= '123456789';
MySQL56Connection1.Port:= 3306;
MySQL56Connection1.Transaction:= SQLTransaction1;
MySQL56Connection1.Connected:= true;
SQLTransaction1.Commit;
SQLQuery1.DataBase:= MySQL56Connection1;
SQLQuery1.Close;
SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('load data local infile ''C:/Users/Administrator/Desktop/ep/test.txt'' into table TCM42785 fields terminated by '' '' (IDA,IDB,IDC,IDE)');
SQLQuery1.ExecSQL;
SQLTransaction1.EndTransaction;

it's can't run , how can i do

[rvk]

That server, on IP 192.168.0.205, is that the same machine on which the "C:/Users/Administrator/Desktop/ep/test.txt" is located?
(If not... then it's obvious the server can't read the file... it's not accessible. So put it somewhere the server/192.168.0.205 van access it.)

If it is... does the MySQL-server instance run under administrator privileges?

[life2122]

i use HeidiSQL tools can run this sql statement, the local file means use local files, not remote file, i want to upload local txt file on server

[rvk]

I don't know how HeidiSQL works. Maybe it detects you want to read a local-file and it uploads it automatically. But for standard SQL you need to make sure the file is accessible to the server on that path. If the file is not in C:/Users/Administrator/Desktop/ep/ on the server the database engine can't read it.

(You can check this easily. Put the test.txt in C:\TEMP on the server and issue the same command with "C:\TEMP\test.txt" and see if it works.)

[life2122]

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

see this url, for load  data file Syntax, that's not need put file on server

[rvk]

If you're talking about this:

You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server. The --local option causes mysqlimport to read data files from the client host. You can specify the --compress option to get better performance over slow networks if the client and server support the compressed protocol. See Section 4.5.5, “mysqlimport — A Data Import Program”.

You're correct. The mysqlimport utility automatically uploads the local files to the remote before issuing the import.
(You can't, and didn't, specify a --local option with your command, did you )

But... you're not working with the mysqlimport utility. You're working with a direct command which you send to the server. If you don't upload the file yourself the server can't do anything with that command. So you have to upload it yourself or use the mysqlimport utility (which does this for you).

[life2122]

I think it not must be need use mysqlimport, it's another method you also can use it, use zeos in delphi, the same statement run ok, but zeos for lazarus it's not run.

[rvk]

You are correct. Zeos should be able to upload it with "load data local infile"

Code: [Select]

TMYSQL_CLIENT_OPTIONS =
//...
  CLIENT_LOCAL_FILES   , { = 128;   Can use LOAD DATA LOCAL }

Are you sure that CLIENT_LOCAL_FILES is set as client-flag? (I'm not familiar with Zeos so I don't know where you should set this).

My guess is you need to set it in your connection-parameters:

Code: [Select]

CLIENT_LOCAL_FILES=TRUE
(I also guess you do need to use the Zeos-client components)

[life2122]

I changed the txt format,  and use  ','  replace space, then change 'fields terminated by '','' ', it's run success, but why can't use space ' '?

[andrej.silva]

Sorry, the English, google translator. So try , for direct use and always worked.


SQLTransaction1.DataBase:= MySQL56Connection1;
MySQL56Connection1.DatabaseName:= 'test';
MySQL56Connection1.HostName:= '192.168.0.205';
MySQL56Connection1.UserName:= 'testusr';
MySQL56Connection1.Password:= '123456789';
MySQL56Connection1.Port:= 3306;
MySQL56Connection1.Transaction:= SQLTransaction1;
MySQL56Connection1.Connected:= true;
SQLTransaction1.Commit;
SQLQuery1.DataBase:= MySQL56Connection1;
SQLQuery1.Close;
SQLQuery1.SQL.Clear;
{   SQLQuery1.SQL.Add('load data local infile ''C:/Users/Administrator/Desktop/ep/test.txt'' into table    TCM42785 fields terminated by '' '' (IDA,IDB,IDC,IDE)'); }


TBlobField(SQLQuery1.FieldbyName(Your blob field)).LoadFromFile('C:/Users/Administrator/Desktop/ep/test.txt'');

SQLQuery1.ExecSQL;

[rvk]

You could try:

Code: [Select]

fields terminated by ''\s''
(not sure if that will work)

TBlobField(SQLQuery1.FieldbyName(Your blob field)).LoadFromFile('C:/Users/Administrator/Desktop/ep/test.txt'');
SQLQuery1.ExecSQL;

"load data local infile" has nothing to do with a "blob"-field. This is a method to import a text-file into a table with columns directly on the server.

[andrej.silva]

Oh I'm sorry, really thought it was to upload a file to a field.