Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use this query for loading sql data from my pc to localhost mysql database.(version 5.0.10 working witj XAMPP)

The query is as below:

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' IGNORE INTO TABLE " + tblname + " CHARACTER SET UTF8", conn);

And I tried this also (enclosed tblname)

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' INTO TABLE `" + tblname + "` CHARACTER SET UTF8", conn);

And one line of my sql data (loaded without problem by phpmyadmin) :

INSERT INTO `atelier` VALUES(1,'Chateau Carbonnieux -1','26.12.2013 00:00:00',23,10,0,'4 macarons differents','mamie','15.12.2013 11:09:14','sabrina','18.12.2013 05:29:26');

The variable filename is : "E:\Macdata\20131228\atelier.sql" (with double backslah)

And the tblname is the table name read directly form database is: "atelier".

When the program reaches the line cmd.ExecuteNonQuery() 30 seconds after i get "Error 0 fatal error ecountered attempting to read the resultset."

This data file is loaded without problem with phpmyadmin.

What can be the reason ? MySql version ? Any help ?

As I'am a newbie in mysql, i used this link for creating my query command. Often they send this link for answer. But in this link there is no remarq to enclose the filename with ' otherwise it does not work it gives an error. http://dev.mysql.com/doc/refman/5.1/en/load-data.html

share|improve this question
1  
Sorry I edited my question after your remarq it's in c# –  Ismail Gunes Dec 28 '13 at 20:40
    
This is an example LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; –  Mihai Dec 28 '13 at 20:41
    
I know but those are said as optional if I have some different separators. I use same structure of data that gives phpmyadmin within my program. As I told phpmyadmin has no problem for loading this data –  Ismail Gunes Dec 28 '13 at 20:44
    

1 Answer 1

up vote 2 down vote accepted

You don't use LOAD DATA INFILE for what you are trying to do.

LOAD DATA INFILE loads a delimited text file (such as CSV) full of raw data, that is sitting on the server, into a table.

What you have is a dump file, which is full of queries -- not raw data.

You have three basic options:

  • use phpmyadmin to apply the file
  • use the mysql command line client to load the file
  • open the file yourself in your code (however you'd open a normal text file for reading), read each line in, and execute each line as a query from your code.

A dump file contains the queries to recreate data structures, so its contents are simply executed as queries. That's what phymyadmin is doing when you "load" it that way.

If the file contains DDL that drops and creates tables, you'll have to do a little more work, because you'll need to buffer what you read from the file, and only execute what you've collected so far each time you find a line ending with the ; delimiter -- or any other delimiter that may be specified in the dump file (a line beginning with DELIMITER changes the expected delimiter for subsequent statements until a subsequent DELIMITER statement is found).

But using the mysql.exe command line client on your local machine would probably be the easiest.

C:\>mysql [options] < "E:\Macdata\20131228\atelier.sql"

With a file structured like this, you can't specify what table it goes into. If the file contains USE statements to change to the appropriate database, that will happen when those statements are encountered. If it doesn't, you'll need to add the name of your schema right before the < in the command line.

share|improve this answer
    
The data line in my exemple is created by my program not a copy of phpmyadmin. So you mean if I create a file as csv file format I can do it ? If yes I will try –  Ismail Gunes Dec 29 '13 at 15:45
    
Yes, if it's csv you can LOAD DATA INFILE but the file needs to be on the server. LOAD DATA LOCAL INFILE loads data from a file on your local machine, but your client library has to support that, which many do not. If you are creating the file yourself... why would you need to use a file? You already are connecting to the databasr, so you could just execute the queries... it would seem... –  Michael - sqlbot Dec 29 '13 at 15:55
    
It's only for getting backup/restore the data. The program will be used by a friend very far away from me, who has no knowledge about mysql or phpmyadmin. So I want that the program takes the backup and restore it if he has a problem. Thanks for the new ideas. Sorry my reputation doesn't let me +1. –  Ismail Gunes Dec 29 '13 at 16:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.